1 --创建登录账户:用户默认为public角色  2 USE [master]  3 GO  4 CREATE LOGIN [登录名] WITH PASSWORD=N‘密码‘, DEFAULT_DATABASE=[test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF  5 GO  6   7 --删除登录账户(登录名不用引号，‘[]‘号为必须，防止用户为单纯的数字)  8 USE [master]   9 go 10 EXEC sys.sp_revokedbaccess  @name_in_db = [登录名] 11 DROP USER [登录名] 12 DROP LOGIN [登录名] 13  14 --账户角色授权 15 /* 16 @rolename枚举值(角色权限)： 17                 bulkadmin            --可以运行BULK INSERT语句 18                 dbcreator            --可以创建、修改数据库     19                 diskadmin            --用户管理磁盘文件 20                 processadmin        --可以终止SQL SERVER实例中的进程 21                 public                --默认且不可修改     22                 securityadmin        --管理和审核登录账户 23                 serveradmin            --可以更改服务器范围的配置选项和关闭服务器 24                 setupadmin            --配置复制和链接服务器 25                 sysadmin            --执行任何活动 26 */ 27 USE [master]  28 go 29 EXEC master..sp_addsrvrolemember @loginame = N‘登录名‘, @rolename = N‘sysadmin‘ 30 EXEC master..sp_addsrvrolemember @loginame = N‘登录名‘, @rolename = N‘bulkadmin‘ 31 EXEC master..sp_addsrvrolemember @loginame = N‘登录名‘, @rolename = N‘dbcreator‘ 32 EXEC master..sp_addsrvrolemember @loginame = N‘登录名‘, @rolename = N‘diskadmin‘ 33 EXEC master..sp_addsrvrolemember @loginame = N‘登录名‘, @rolename = N‘processadmin‘ 34 EXEC master..sp_addsrvrolemember @loginame = N‘登录名‘, @rolename = N‘securityadmin‘ 35 EXEC master..sp_addsrvrolemember @loginame = N‘登录名‘, @rolename = N‘serveradmin‘ 36 EXEC master..sp_addsrvrolemember @loginame = N‘登录名‘, @rolename = N‘setupadmin‘ 37 EXEC master..sp_addsrvrolemember @loginame = N‘登录名‘, @rolename = N‘sysadmin‘ 38 go 39  40 --删除账户角色 41 USE [master]  42 go 43 EXEC master..sp_dropsrvrolemember @loginame = N‘登录名‘, @rolename = N‘sysadmin‘ 44 EXEC master..sp_dropsrvrolemember @loginame = N‘登录名‘, @rolename = N‘bulkadmin‘ 45 EXEC master..sp_dropsrvrolemember @loginame = N‘登录名‘, @rolename = N‘dbcreator‘ 46 EXEC master..sp_dropsrvrolemember @loginame = N‘登录名‘, @rolename = N‘diskadmin‘ 47 EXEC master..sp_dropsrvrolemember @loginame = N‘登录名‘, @rolename = N‘processadmin‘ 48 EXEC master..sp_dropsrvrolemember @loginame = N‘登录名‘, @rolename = N‘securityadmin‘ 49 EXEC master..sp_dropsrvrolemember @loginame = N‘登录名‘, @rolename = N‘serveradmin‘ 50 EXEC master..sp_dropsrvrolemember @loginame = N‘登录名‘, @rolename = N‘setupadmin‘ 51 EXEC master..sp_dropsrvrolemember @loginame = N‘登录名‘, @rolename = N‘sysadmin‘ 52 go 53  54 --用户数据库访问授权 55 --所有数据库都可以访问 56 USE [master]  57 go 58 EXEC master..sp_addsrvrolemember @loginame = N‘登录名‘, @rolename = N‘sysadmin‘ 59 go 60 --访问制定数据库(删除用户拥有的sysadmin角色，然后为登录用户创建数据库用户映射) 61 USE [master]  62 go 63 EXEC master..sp_dropsrvrolemember @loginame = N‘登录名‘, @rolename = N‘sysadmin‘ 64 go 65 USE databasename 66 go 67 create user [登录名] for login [登录名] with default_schema=dbo --此时还不可访问该数据库的对象如表、存储过程、视图等 68 go 69  70 --设置登录用户访问该数据库的所有对象 71 USE databasename 72 go 73 exec sp_addrolemember ‘db_owner‘, ‘登录名‘  74 go 75  76 --禁用登录用户访问该数据库的所有对象 77 USE test 78 go 79 exec sp_droprolemember ‘db_owner‘, ‘登录名‘  80 go 81  82 --授权登录用户访问指定的表\存储过程\视图等(先禁用数据库用户拥有的db_owner角色，然后再对制定的对象赋相应的权限) 83 /* 84 对象(表|存储过程|视图等)枚举值： 85             ALTER            --修改 86             CONTROL            --控制     87             EXECUTE            --执行 88             TAKE OWNERSHIP        --所有权限 89             VIEW DEFINITION                --查看定义 90 */ 91 USE test 92 go 93 exec sp_droprolemember ‘db_owner‘, ‘登录名‘  94 go 95 use test 96 go 97 GRANT ALTER ON [dbo].[表|存储过程|视图等名称] TO [登录名] 98 GRANT CONTROL ON [dbo].[表|存储过程|视图等名称] TO [登录名] 99 GRANT EXECUTE ON [dbo].[表|存储过程|视图等名称] TO [登录名]100 GRANT TAKE OWNERSHIP ON [dbo].[表|存储过程|视图等名称] TO [登录名]101 GRANT VIEW DEFINITION ON [dbo].[表|存储过程|视图等名称] TO [登录名]102 go103 104 --删除对登录用户访问指定的表\存储过程\视图等的授权105 use test106 go107 GRANT CONTROL ON [dbo].DRImportNew1 TO [登录名]108 REVOKE CONTROL ON [dbo].DRImportNew1 TO [登录名]109 REVOKE ALTER ON [dbo].[表|存储过程|视图等名称] TO [登录名]110 REVOKE CONTROL ON [dbo].[表|存储过程|视图等名称] TO [登录名]111 REVOKE EXECUTE ON [dbo].[表|存储过程|视图等名称] TO [登录名]112 REVOKE TAKE OWNERSHIP ON [dbo].[表|存储过程|视图等名称] TO [登录名]113 REVOKE VIEW DEFINITION ON [dbo].[表|存储过程|视图等名称] TO [登录名]114 go115 116 --授权登录用户访问表的指定列117 use test118 go119 GRANT SELECT ON dbo.表名(字段1,字段2...) TO [登录名]120 go121 122 --批量删除数据库所有表123 use databasename124 go125 DECLARE @DROP_STRING VARCHAR(8000)126 --删除外键约束127 DECLARE DROP_FK CURSOR FOR128 SELECT  ‘ALTER TABLE ‘+ OBJECT_NAME(PARENT_OBJ) + ‘ DROP CONSTRAINT ‘+NAME129 FROM    SYSOBJECTS130 WHERE   XTYPE = ‘F‘131 OPEN DROP_FK132 FETCH NEXT FROM DROP_FK INTO @DROP_STRING133 WHILE(@@FETCH_STATUS=0)134 BEGIN     EXEC(@DROP_STRING)     FETCH NEXT FROM DROP_FK INTO @DROP_STRING135 END136 CLOSE DROP_FK137 DEALLOCATE DROP_FK138 --删除表139 DECLARE DROP_TABLE CURSOR FOR140 SELECT  ‘DROP TABLE ‘+NAME141 FROM    SYSOBJECTS142 WHERE   XTYPE = ‘U‘143 OPEN DROP_TABLE144 FETCH NEXT FROM DROP_TABLE INTO @DROP_STRING145 WHILE(@@FETCH_STATUS=0)146 BEGIN     EXEC(@DROP_STRING)     FETCH NEXT FROM DROP_TABLE INTO @DROP_STRING147 END148 CLOSE DROP_TABLE149 DEALLOCATE DROP_TABLE150 GO