SQL语句创建删除权限管理用户

1、USE mydbGO-1. 新建测试用户-1.1 添加登录用户和密码 EXEC sp_addlogin Ntony,123 -1.2 使其成为当前数据库的合法用户 EXEC sp_grantdbaccess Ntony -2.设置操作授权-2.1 授予对自己数据库的所有权限 EXEC sp_addrolemember Ndb_owner, Ntony -2.2 以下是设置具体操作权限-授予tony对所有用户表的操作权限GRANT SELECT,INSERT,UPDATE,DELETE TO tony -授予tony SELECT,UPDATE到具体的表 GRANT SELECT,UPDATE

2、ON tb TO tony -授予tony SELECT,UPDATE到具体的表和列 GRANT SELECT,UPDATE ON tb(id,col) TO tony -禁止tony对所有用户表的操作权限DENY SELECT,INSERT,UPDATE,DELETE TO tony -禁止tony SELECT,UPDATE到具体的表 DENY SELECT,UPDATE ON tb TO tony -禁止tony SELECT,UPDATE到具体的表和列 DENY SELECT,UPDATE ON tb(id,col) TO tony -删除tony 对所有用户表的授权信息REVOKE

3、SELECT,INSERT,UPDATE,DELETE TO tony-授予tony对具有创建表、视图、存储过程等的操作权限GRANT CREATE TABLE,CREATE VIEW,CREATE PROC TO tony -禁止tony对具有创建表、视图、存储过程等的操作权限DENY CREATE TABLE,CREATE VIEW,CREATE PROC TO tony -删除tony对具有创建表、视图、存储过程等的授权信息REVOKE CREATE TABLE,CREATE VIEW,CREATE PROC TO tony GO-注：更多相关授权信息参考后面的附表中“数据库权限”列。-

4、3. 删除测试用户EXEC sp_revokedbaccess Ntony -移除用户对数据库的访问权限EXEC sp_droplogin Ntony -删除登录用户GO附表： 数据库权限 数据库权限隐含的权限 服务器权限隐含的权限 ALTERCONTROLALTER ANY DATABASEALTER ANY APPLICATION ROLEALTERCONTROL SERVERALTER ANY ASSEMBLYALTERCONTROL SERVERALTER ANY ASYMMETRIC KEYALTERCONTROL SERVERALTER ANY CERTIFICATEALTERCO

5、NTROL SERVERALTER ANY CONTRACTALTERCONTROL SERVERALTER ANY DATABASE DDL TRIGGERALTERCONTROL SERVERALTER ANY DATABASE EVENT NOTIFICATIONALTERALTER ANY EVENT NOTIFICATIONALTER ANY DATASPACEALTERCONTROL SERVERALTER ANY FULLTEXT CATALOGALTERCONTROL SERVERALTER ANY MESSAGE TYPEALTERCONTROL SERVERALTER AN

6、Y REMOTE SERVICE BINDINGALTERCONTROL SERVERALTER ANY ROLEALTERCONTROL SERVERALTER ANY ROUTEALTERCONTROL SERVERALTER ANY SCHEMAALTERCONTROL SERVERALTER ANY SERVICEALTERCONTROL SERVERALTER ANY SYMMETRIC KEYALTERCONTROL SERVERALTER ANY USERALTERCONTROL SERVERAUTHENTICATECONTROLAUTHENTICATE SERVERBACKUP

7、 DATABASECONTROLCONTROL SERVERBACKUP LOGCONTROLCONTROL SERVERCHECKPOINTCONTROLCONTROL SERVERCONNECTCONNECT REPLICATIONCONTROL SERVERCONNECT REPLICATIONCONTROLCONTROL SERVERCONTROLCONTROLCONTROL SERVERCREATE AGGREGATEALTERCONTROL SERVERCREATE ASSEMBLYALTER ANY ASSEMBLYCONTROL SERVERCREATE ASYMMETRIC

8、KEYALTER ANY ASYMMETRIC KEYCONTROL SERVERCREATE CERTIFICATEALTER ANY CERTIFICATECONTROL SERVERCREATE CONTRACTALTER ANY CONTRACTCONTROL SERVERCREATE DATABASECONTROLCREATE ANY DATABASECREATE DATABASE DDL EVENT NOTIFICATIONALTER ANY DATABASE EVENT NOTIFICATIONCREATE DDL EVENT NOTIFICATIONCREATE DEFAULT

9、ALTERCONTROL SERVERCREATE FULLTEXT CATALOGALTER ANY FULLTEXT CATALOGCONTROL SERVERCREATE FUNCTIONALTERCONTROL SERVERCREATE MESSAGE TYPEALTER ANY MESSAGE TYPECONTROL SERVERCREATE PROCEDUREALTERCONTROL SERVERCREATE QUEUEALTERCONTROL SERVERCREATE REMOTE SERVICE BINDINGALTER ANY REMOTE SERVICE BINDINGCO

10、NTROL SERVERCREATE ROLEALTER ANY ROLECONTROL SERVERCREATE ROUTEALTER ANY ROUTECONTROL SERVERCREATE RULEALTERCONTROL SERVERCREATE SCHEMAALTER ANY SCHEMACONTROL SERVERCREATE SERVICEALTER ANY SERVICECONTROL SERVERCREATE SYMMETRIC KEYALTER ANY SYMMETRIC KEYCONTROL SERVERCREATE SYNONYMALTERCONTROL SERVER

11、CREATE TABLEALTERCONTROL SERVERCREATE TYPEALTERCONTROL SERVERCREATE VIEWALTERCONTROL SERVERCREATE XML SCHEMA COLLECTIONALTERCONTROL SERVERDELETECONTROLCONTROL SERVEREXECUTECONTROLCONTROL SERVERINSERTCONTROLCONTROL SERVERREFERENCESCONTROLCONTROL SERVERSELECTCONTROLCONTROL SERVERSHOWPLANCONTROLALTER TRACESUBSCRIBE QUERY NOTIF