资源描述:
《sql语句创建删除权限管理用户》由会员上传分享,免费在线阅读,更多相关内容在学术论文-天天文库。
1、USEmydbGO--1.新建测试川户--1.1添加登录用户和密码EXECsp_addloginN'tony1,'123'--1.2使《成为当前数据库的合法用户EXECsp_grantdbaccessN'tony'--2.设置操作授权--2.1授予对己数椐库的所冇权限EXECsp_addrolememberN1db_owner1,N1tony'--2.2以下是设置具体操作权限--授予tony对所有用户表的操作权限GRANTSELECT,INSERT,UPDATE,DELETETOtony--授予tonySELECT
2、,UPDATE到具体的表GRANTSELECT,UPDATEONtbTOtony--授予tonySELECT,UPDATE到具体的表和列GRANTSELECT,UPDATEONtb(id,col)TOtony--禁止tony对所冇用户表的操作权限DENYSELECT,INSERT,UPDATE,DELETETOtony--禁止tonySELECT,UPDATE到只体的表DENYSELECT,UPDATEONtbTOtony--禁止tonySELECT,UPDATE到具体的衣和列DENYSELECT,UPDATEON
3、tb(id,col)TOtony--删除tony对所有用户表的授权信息REVOKESELECT,INSERT,UPDATE,DELETETOtony--授予tony对具有创建农、视阁、存储过程等的操作权限GRANTCREATETABLE,CREATEVIEW,CREATEPROCTOtony--禁止tony对具有创建表、视图、存储过程等的操作权限DENYCREATETABLE,CREATEVIEW,CREATEPROCTOtony--删除tony对具有创建表、视阁、存储过程等的授权倍息REVOKECREATETAB
4、LE,CREATEVIEW,CREATEPROCTOtonyGO--注:更多相关授权信息参考后面的附表中“数裾库权限”列。--3.删除测试用户EXECsp_revokedbaccessN'tony'--移除用户对数掘犀的访问权限EXECsp_droploginN'tony'--删除登录用户GO附表:数据库权限数据库权限隐含的权限服务器权限隐含的权限ALTERCONTROL[ALTERANYDATABASEALTERANYAPPLICATIONROLEalterControlserverALTERANYASSEMBL
5、YalterControlserverALTERANYASYMMETRICKEYalterControlserverALTERANYCERTIFICATEALTERCONTROLSERVERALTERANYCONTRACTALTERCONTROLSERVERALTERANYDATABASEDDLTRIGGERALTERCONTROLSERVERALTERANYDATABASEEVENTNOTIFICATIONALTERALTERANYEVENTNOTIFICATIONALTERANYDATASPACEalterCo
6、ntrolserverALTERANYFULLTEXTCATALOGalterControlserverALTERANYMESSAGETYPEalterControlserverALTERANYREMOTESERVICEBINDINGALTERCONTROLSERVERALTERANYROLEalterControlserverALTERANYROUTEalterControlserverALTERANYSCHEMAalterControlserverALTERANYSERVICEalterControlserve
7、rALTERANYSYMMETRICKEYALTERCONTROLSERVERALTERANYUSERalterControlserverAUTHENTICATECONTROLAUTHENTICATESERVERBACKUPDATABASECONTROL(CONTROLSERVERbackuplogControlControlservercheckpointControlControlserverCONNECTCONNECTREPLICATIONCONTROLSERVERCONNECTREPLICATIONCONT
8、ROLCONTROLSERVERCONTROLCONTROLCONTROLSERVERCREATEAGGREGATEALTERCONTROLSERVERCREATEASSEMBLYALTERANYASSEMBLYCONTROLSERVERCREATEASYMMETRICKEYALTERANYASYMMETRICKEYCONTROLSERVERCREATECE