SQL语句新建用户、对用户授权、删除用户实例

SQL语句新建用户、对用户授权、删除用户实例 收藏

 

USE mydb

GO

--1. 新建测试用户

--1.1 添加登录用户和密码  

EXEC sp_addlogin N'tony','123'

--1.2 使其成为当前数据库的合法用户   

EXEC sp_grantdbaccess N'tony'  

 

--2.设置操作授权

--2.1 授予对自己数据库的所有权限     

EXEC sp_addrolemember N'db_owner', N'tony'

 

--2.2 以下是设置具体操作权限

--授予tony对所有用户表的操作权限

GRANT SELECT,INSERT,UPDATE,DELETE TO tony 

--授予tony SELECT,UPDATE到具体的表  

GRANT SELECT,UPDATE 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 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

--注：更多相关授权信息参考后面的附表中“数据库权限”列。

 

--3. 删除测试用户

EXEC sp_revokedbaccess N'tony'   --移除用户对数据库的访问权限

EXEC sp_droplogin N'tony'        --删除登录用户

GO

 

 

 

 

附表：  数据库权限  数据库权限隐含的权限  服务器权限隐含的权限 
ALTER
 CONTROL
 ALTER ANY DATABASE
 
ALTER ANY APPLICATION ROLE
 ALTER
 CONTROL SERVER
 
ALTER ANY ASSEMBLY
 ALTER
 CONTROL SERVER
 
ALTER ANY ASYMMETRIC KEY
 ALTER
 CONTROL SERVER
 
ALTER ANY CERTIFICATE
 ALTER
 CONTROL SERVER
 
ALTER ANY CONTRACT
 ALTER
 CONTROL SERVER
 
ALTER ANY DATABASE DDL TRIGGER
 ALTER
 CONTROL SERVER
 
ALTER ANY DATABASE EVENT NOTIFICATION
 ALTER
 ALTER ANY EVENT NOTIFICATION
 
ALTER ANY DATASPACE
 ALTER
 CONTROL SERVER
 
ALTER ANY FULLTEXT CATALOG
 ALTER
 CONTROL SERVER
 
ALTER ANY MESSAGE TYPE
 ALTER
 CONTROL SERVER
 
ALTER ANY REMOTE SERVICE BINDING
 ALTER
 CONTROL SERVER
 
ALTER ANY ROLE
 ALTER
 CONTROL SERVER
 
ALTER ANY ROUTE
 ALTER
 CONTROL SERVER
 
ALTER ANY SCHEMA
 ALTER
 CONTROL SERVER
 
ALTER ANY SERVICE
 ALTER
 CONTROL SERVER
 
ALTER ANY SYMMETRIC KEY
 ALTER
 CONTROL SERVER
 
ALTER ANY USER
 ALTER
 CONTROL SERVER
 
AUTHENTICATE
 CONTROL
 AUTHENTICATE SERVER
 
BACKUP DATABASE
 CONTROL
 CONTROL SERVER
 
BACKUP LOG
 CONTROL
 CONTROL SERVER
 
CHECKPOINT
 CONTROL
 CONTROL SERVER
 
CONNECT
 CONNECT REPLICATION
 CONTROL SERVER
 
CONNECT REPLICATION
 CONTROL
 CONTROL SERVER
 
CONTROL
 CONTROL
 CONTROL SERVER
 
CREATE AGGREGATE
 ALTER
 CONTROL SERVER
 
CREATE ASSEMBLY
 ALTER ANY ASSEMBLY
 CONTROL SERVER
 
CREATE ASYMMETRIC KEY
 ALTER ANY ASYMMETRIC KEY
 CONTROL SERVER
 
CREATE CERTIFICATE
 ALTER ANY CERTIFICATE
 CONTROL SERVER
 
CREATE CONTRACT
 ALTER ANY CONTRACT
 CONTROL SERVER
 
CREATE DATABASE
 CONTROL
 CREATE ANY DATABASE
 
CREATE DATABASE DDL EVENT NOTIFICATION
 ALTER ANY DATABASE EVENT NOTIFICATION
 CREATE DDL EVENT NOTIFICATION
 
CREATE DEFAULT
 ALTER
 CONTROL SERVER
 
CREATE FULLTEXT CATALOG
 ALTER ANY FULLTEXT CATALOG
 CONTROL SERVER
 
CREATE FUNCTION
 ALTER
 CONTROL SERVER
 
CREATE MESSAGE TYPE
 ALTER ANY MESSAGE TYPE
 CONTROL SERVER
 
CREATE PROCEDURE
 ALTER
 CONTROL SERVER
 
CREATE QUEUE
 ALTER
 CONTROL SERVER
 
CREATE REMOTE SERVICE BINDING
 ALTER ANY REMOTE SERVICE BINDING
 CONTROL SERVER
 
CREATE ROLE
 ALTER ANY ROLE
 CONTROL SERVER
 
CREATE ROUTE
 ALTER ANY ROUTE
 CONTROL SERVER
 
CREATE RULE
 ALTER
 CONTROL SERVER
 
CREATE SCHEMA
 ALTER ANY SCHEMA
 CONTROL SERVER
 
CREATE SERVICE
 ALTER ANY SERVICE
 CONTROL SERVER
 
CREATE SYMMETRIC KEY
 ALTER ANY SYMMETRIC KEY
 CONTROL SERVER
 
CREATE SYNONYM
 ALTER
 CONTROL SERVER
 
CREATE TABLE
 ALTER
 CONTROL SERVER
 
CREATE TYPE
 ALTER
 CONTROL SERVER
 
CREATE VIEW
 ALTER
 CONTROL SERVER
 
CREATE XML SCHEMA COLLECTION
 ALTER
 CONTROL SERVER
 
DELETE
 CONTROL
 CONTROL SERVER
 
EXECUTE
 CONTROL
 CONTROL SERVER
 
INSERT
 CONTROL
 CONTROL SERVER
 
REFERENCES
 CONTROL
 CONTROL SERVER
 
SELECT
 CONTROL
 CONTROL SERVER
 
SHOWPLAN
 CONTROL
 ALTER TRACE
 
SUBSCRIBE QUERY NOTIFICATIONS
 CONTROL
 CONTROL SERVER
 
TAKE OWNERSHIP
 CONTROL
 CONTROL SERVER
 
UPDATE
 CONTROL
 CONTROL SERVER
 
VIEW DATABASE STATE
 CONTROL
 VIEW SERVER STATE
 
VIEW DEFINITION
 CONTROL
 VIEW ANY DEFINITION
 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/06/27/5696849.aspx