一、创建登录用户

1.登录数据库

#1.进入master库

USE [master]

#2.创建用户语法

CREATE LOGIN [登录名] WITH

password={ 'password' | hashed_password hashed }

[must_change][,]

[sid=0x14585E90117152449347750164BA00A7][,]

[default_database=database_name][,]

[default_language=language][,]

[check_expiration={ on | off }][,]

[check_policy={ on | off }][,]

[credential=credential_name]

2.语法块含义

#1.--login_name 指定创建的登录名。

有四种类型的登录：SQLServer登录、Windows登录、证书映射登录和非对称密钥映射登录。

--在创建从Windows域帐户映射的登录名时，必须以[<domainName>\<login_name>]格式使用Windows 2000之前的用户登录名。 

--不能使用login_name@DomainName格式的UPN。 

--身份验证登录的类型为sysname，它必须符合标识符规则，且不能包含“\”。 

--Windows登录名可以包含“\”。Active Directory用户的登录名需少于21个字符。

#2.--password 指定创建用户的密码

有两种密码类型：

--password='password*' 

--仅适用于SQL Server登录。指定正在创建的登录名的密码。应使用强密码。 

--有关详细信息，请参阅强密码和密码策略。从SQL Server 2012 (11.x)开始，存储的密码信息使用 SHA-512 加盐密码进行计算。

--密码是区分大小写的。密码应始终至少包含 8 个字符，并且不能超过128个字符。 

--密码可以包含 a-z、A-Z、0-9 和大多数非字母数字字符。 密码不能包含单引号或 login_name。

--password=hashed_password

--仅适用于hashed关键字。指定要创建的登录名的密码的哈希值。

--hashed仅适用于SQL Server登录。指定在password参数后输入的密码已经过哈希运算。 

--如果未选择此选项，则在将作为密码输入的字符串存储到数据库中之前，对其进行哈希运算。 

--此选项应仅用于在服务器之间迁移数据库。切勿使用hashed选项创建新的登录名。hashed选项不能用于SQL 7或更早版本创建的哈希。

#3.--must_change 首次登录设置新密码

--仅适用于SQL Server登录。如果包括此选项，则SQL Server将在首次使用新登录时提示用户输入新密码。

#4.--sid=sid 用于重新创建登录名

--用于重新创建登录名。仅适用于SQL Server身份验证登录，不适用于Windows身份验证登录。指定新SQL Server身份验证登录的sid。

--如果未使用此选项，SQL Server将自动分配sid。sid结构取决于SQL Server版本。 QL Server登录sid：基于GUID的16 字节(binary(16))文本值。 例如，sid 0x14585E90117152449347750164BA00A7。

#5.--default_database=database 用于指定默认数据库

--指定将指派给登录名的默认数据库。如果未包括此选项，则默认数据库将设置为master。

#6.--default_language=language 用于指定登录的语言

--指定将指派给登录名的默认语言。如果未包括此选项，则默认语言将设置为服务器的当前默认语言。即使将来服务器的默认语言发生更改，登录名的默认语言也仍保持不变。

#7.--check_expiration={ on | off } 用于指定是否强制实施密码过期策略

--仅适用于SQL Server登录。 指定是否应对此登录帐户强制实施密码过期策略。 默认值为off。

#8.--check_policy={ on | off } 用于指定实施的密码策略

--仅适用于SQL Server登录。 指定应对此登录强制实施运行SQL Server 计算机的 Windows 密码策略。 默认值为on。

--如果 Windows 策略要求强密码，密码必须至少包含以下四个特点中的三个：

--大写字符 (A-Z)。

--小写字符 (a-z)。

--数字 (0-9)。

--一个非字母数字字符，如空格、、@、*、^、%、!、$、# 或 &。

#9.--credential=credential_name 用于映射登录凭据

--将映射到新SQL Server登录的凭据名称。 该凭据必须已存在于服务器中。当前此选项只将凭据链接到登录名。凭据不能映射到系统管理员(sa)登录名。

3.常用示例

#1.最快速创建方式：

USE [master]

CREATE LOGIN [登录名] WITH PASSWORD=N'密码', DEFAULT_DATABASE=[test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

#2.普通创建方式：

--声明引用数据库

use tests;

--创建登录用户

create login '登录名'

with 

password='123456',

--must_change,

--sid=0x14585E90117152449347750164BA00A7,

default_database=master,

--default_language=language,

check_expiration=off,

check_policy=off

--credential=[sysadmin]

二、账户服务器角色授权

1.常用服务器角色类型

@rolename枚举值(角色权限)：

#1.bulkadmin            --可以运行BULK INSERT语句

#2.dbcreator            --可以创建、修改数据库    

#3.diskadmin            --用户管理磁盘文件

#4.processadmin         --可以终止SQL SERVER实例中的进程

#5.public               --默认且不可修改    

#6.securityadmin        --管理和审核登录账户

#7.serveradmin          --可以更改服务器范围的配置选项和关闭服务器

#8.setupadmin           --配置复制和链接服务器

#9.sysadmin             --执行任何活动

2.单独授权服务器角色

#1.声明引用数据库

USE [master] 

#2.创建登录名并授权服务器角色

EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'

EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'bulkadmin'

EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'dbcreator'

EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'diskadmin'

EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'processadmin'

EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'securityadmin'

EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'serveradmin'

EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'setupadmin'

EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'

3.删除服务器角色

#1.声明引用数据库

USE [master] 

#2.删除账户角色

EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'bulkadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'dbcreator'

EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'diskadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'processadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'securityadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'serveradmin'

EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'setupadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'

4.单用户添加多个服务器角色

#1.声明引用数据库

USE [master] 

#2.添加多个服务器角色，服务器角色用于向用户授权服务器范围内的安全特权

alter server role [bulkadmin] add member '登录名';

alter server role [dbcreator] add member '登录名';

alter server role [diskadmin] add member '登录名';

alter server role [processadmin] add member '登录名';

alter server role [securityadmin] add member '登录名';

alter server role [serveradmin] add member '登录名';

alter server role [setupadmin] add member '登录名';

alter server role [sysadmin] add member '登录名';

三、数据库授权

1.授权所有库

#1.声明引用数据库

USE [master] 

#2.授权所有库

EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'

2.访问特定数据库

#1.声明引用数据库

USE [master] 

#2.删除用户拥有的sysadmin角色

EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'

#3.为登录用户创建数据库用户映射

USE databasename

create user [登录名] for login [登录名] with default_schema=dbo 

ps：此时还不可访问该数据库的对象如表、存储过程、视图等

#4.示例;

为登陆账户创建数据库用户（create user）,在mydb数据库中的security中的user下可以找到新创建的dba

create user dba for login dba with default_schema=dbo

并指定数据库用户“dba” 的默认 schema 是“dbo”。这意味着 用户“dba” 在执行“select * from t”，实际上执行的是 “select * from dbo.t”。

3.访问该数据库的所有对象

#1.声明引用数据库

USE databasename

#2.设置登录用户访问该数据库的所有对象

exec sp_addrolemember 'db_owner', '登录名' 

4.禁用该数据库的所有对象

#1.声明引用数据库

USE databasename

#2.禁用登录用户访问该数据库的所有对象

exec sp_droprolemember 'db_owner', '登录名' 

四、数据表、视图等授权

1.数据表、视图等授权分类

授权登录用户访问指定的表\存储过程\视图等(先禁用数据库用户拥有的db_owner角色，然后再对制定的对象赋相应的权限)

对象(表|存储过程|视图等)枚举值：

#1.ALTER              --修改

#2.CONTROL            --控制    

#3.EXECUTE            --执行

#4.TAKE OWNERSHIP     --所有权限

#5.VIEW DEFINITION    --查看定义

2.禁用该数据库的所有对象

#1.声明引用数据库

USE databasename

#2.禁用登录用户访问该数据库的所有对象

exec sp_droprolemember 'db_owner', '登录名' 

3.授权登录用户访问指定的表\存储过程\视图等

#1.声明引用数据库

USE databasename

#授权登录用户访问指定的表、存储过程、视图等的授权

GRANT ALTER ON [dbo].[表|存储过程|视图等名称] TO [登录名]

GRANT CONTROL ON [dbo].[表|存储过程|视图等名称] TO [登录名]

GRANT EXECUTE ON [dbo].[表|存储过程|视图等名称] TO [登录名]

GRANT TAKE OWNERSHIP ON [dbo].[表|存储过程|视图等名称] TO [登录名]

GRANT VIEW DEFINITION ON [dbo].[表|存储过程|视图等名称] TO [登录名]

4.删除对登录用户访问指定的表\存储过程\视图等的授权

#1. 声明引用数据库

use databasename

#2.删除对登录用户访问指定的表\存储过程\视图等的授权

REVOKE ALTER ON [dbo].[表|存储过程|视图等名称] TO [登录名]

REVOKE CONTROL ON [dbo].[表|存储过程|视图等名称] TO [登录名]

REVOKE EXECUTE ON [dbo].[表|存储过程|视图等名称] TO [登录名]

REVOKE TAKE OWNERSHIP ON [dbo].[表|存储过程|视图等名称] TO [登录名]

REVOKE VIEW DEFINITION ON [dbo].[表|存储过程|视图等名称] TO [登录名]

5.授权登录用户访问表的指定列

#1. 声明引用数据库use databasename#2.授权登录用户访问表的指定列GRANT SELECT ON dbo.表名(字段1,字段2...) TO [登录名]

6.批量删除数据库所有表

#1. 声明引用数据库

use databasename

#2.批量删除数据库所有表

DECLARE @DROP_STRING VARCHAR(8000)

7.删除所有表的外键约束

#1.删除所有表的外键约束

DECLARE DROP_FK CURSOR FOR

SELECT  'ALTER TABLE '+ OBJECT_NAME(PARENT_OBJ) + ' DROP CONSTRAINT '+NAME

FROM    SYSOBJECTS

WHERE   XTYPE = 'F'

OPEN DROP_FK

FETCH NEXT FROM DROP_FK INTO @DROP_STRING

WHILE(@@FETCH_STATUS=0)

BEGIN     EXEC(@DROP_STRING)     FETCH NEXT FROM DROP_FK INTO @DROP_STRING

END

CLOSE DROP_FK

DEALLOCATE DROP_FK

8.删除表

DECLARE DROP_TABLE CURSOR FOR

SELECT  'DROP TABLE '+NAME

FROM    SYSOBJECTS

WHERE   XTYPE = 'U'

OPEN DROP_TABLE

FETCH NEXT FROM DROP_TABLE INTO @DROP_STRING

WHILE(@@FETCH_STATUS=0)

BEGIN     EXEC(@DROP_STRING)     FETCH NEXT FROM DROP_TABLE INTO @DROP_STRING

END

CLOSE DROP_TABLE

DEALLOCATE DROP_TABLE

GO

来自 <https://www.cnblogs.com/jhno1/p/15180471.html> 

一、创建用户映射,映射到此登录名的用户

--use [model]

--go

--create user '登录名' for login '登录名';

--go

--use [msdb]

--go

--create user '登录名' for login '登录名';

--go

--use [ReportServer]

--go

--create user '登录名' for login '登录名';

--go

--use [ReportServerTempDB]

--go

--create user '登录名' for login '登录名';

--go

--use [tempdb]

--go

--create user '登录名' for login '登录名';

--go

--use [testss]

--go

--create user '登录名' for login '登录名';

--go

BASH 复制 全屏

二、创建用户登录数据库

----创建用户登录数据库

--use [master]

--go

--create user ['登录名'] for login ['登录名'];

--go

----创建用户登录多个数据库

--use [testss]

--go 

--create user ['登录名'] for login ['登录名'];

--go

三、声明数据库应用

----声明数据库应用

--use [testss]

--go

四、授予不安全的程序集

----授予不安全的程序集

--grant unsafe assembly to '登录名';

--go

五、授予查看服务器状态

----授予查看服务器状态

--grant view server state to '登录名';

--go

六、授予查看任意定义

----授予查看任意定义

--grant view any definttion to '登录名';

--go

七、授予查看任意数据库

----授予查看任意数据库

--grant view any database to '登录名';

--go

八、授予创建DDL事件通知

----授予创建DDL事件通知

--grant create ddl event notification to '登录名';

--go

九、授予创建端点

----授予创建端点

--grant create endpoint to '登录名';

--go

十、授予创建服务器角色

----授予创建服务器角色

--grant create server role to '登录名';

--go

十一、授予创建跟踪事件通知

----授予创建跟踪事件通知

--grant create trace event notification to '登录名';

--go

十二、授予创建可用性组

----授予创建可用性组

--grant create availability group to '登录名';

--go

十三、授予创建任意数据库

----授予创建任意数据库

--grant create any database to '登录名';

--go

十四、授予更改服务器状态

----授予更改服务器状态

--grant alter server state to '登录名';

--go

十五、授予更改跟踪

----授予更改跟踪

--grant alter trace to '登录名';

--go

十六、授予更改任何服务器角色

----授予更改任何服务器角色

--grant alter any server role to '登录名';

--go

十七、授予更改任何可用性组

----授予更改任何可用性组

--grant alter any availability group to '登录名';

--go

十八、授予更改任意登录名

----授予更改任意登录名

--grant alter any login to '登录名';

--go

十九、授予更改任意端点

----授予更改任意端点

--grant alter any endpoint to '登录名';

--go

二十、授予更改任意服务器审核

----授予更改任意服务器审核

--grant alter any server audit to '登录名';

--go

二十一、授予更改任意权限

----授予更改任意权限

--grant alter any connection to '登录名';

--go

二十二、授予更改任意连接服务器

----授予更改任意连接服务器--grant alter any linked server to '登录名';--go

二十三、授予更改任意凭据

----授予更改任意凭据

--grant alter any credential to '登录名';

--go

二十四、授予更改任意事件会话

----授予更改任意事件会话

--grant alter any event session to '登录名';

--go

二十五、授予更改任意事件通知

----授予更改任意事件通知

--grant alter any event notification to '登录名';

--go

二十六、授予更改任意数据库

----授予更改任意数据库

--grant alter any database to '登录名';

--go

二十七、授予更改设置

----授予更改设置

--grant alter settings to '登录名';

--go

二十八、授予更改资源

----授予更改资源

--grant alter resources to '登录名';

--go

二十九、授予关闭

----授予关闭

--grant shutdown to '登录名';

--go

三十、授予管理大容量操作

----授予管理大容量操作

--grant administer bulk operations to '登录名';

--go

三十一、授予控制服务器

----授予控制服务器

--grant control server to '登录名';

--go

三十二、授予连接SQL

----授予连接SQL

--grant connect sql to '登录名';

--go

三十三、授予外部访问程序集

----授予外部访问程序集

--grant external access assembly to '登录名';

--go

三十四、授予验证服务器

----授予验证服务器

--grant authenticate server to '登录名';

--go

三十五、设置是否允许连接到数据库引擎

--设置是否允许连接到数据库引擎

--deny connect sql to '登录名';

--go 

三十六、是否允许登录

--是否允许登录

--alter login '登录名' disable;

--go

----用户状态

----声明默认数据库引用

--use ['登录名']

--go

----是否允许用户连接到数据库引擎

--deny connect sql to ['登录名'];

--go

----是否允许登录

--alter login ['登录名'] disable

--go