动态创建Sql Server用户及其权限
来源:互联网 发布:主题软件哪个好 编辑:程序博客网 时间:2024/04/28 03:56
一、如何动态创建用户
1.使用存储过程
创建新的 SQL Server 登录,该登录允许用户使用 SQL Server 身份验证连接到 SQL Server 实例。
Transact-SQL 语法约定
sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ] [ , [ @encryptopt= ] 'encryption_option' ]
- [ @loginame = ] 'login'
登录的名称。login 的数据类型为 sysname,无默认值。
- [ @passwd = ] 'password'
登录的密码。password 的数据类型为 sysname,默认值为 NULL。
安全说明: 不要使用空密码。请使用强密码。
- [ @defdb = ] 'database'
登录的默认数据库(在登录后登录首先连接到该数据库)。database 的数据类型为 sysname,默认值为 master。
- [ @deflanguage = ] 'language'
登录的默认语言。language 的数据类型为 sysname,默认值为 NULL。如果未指定 language,则新登录的默认 language 将设置为服务器的当前默认语言。
- [ @sid = ] 'sid'
安全标识号 (SID)。sid 的数据类型为 varbinary(16),默认值为 NULL。如果 sid 为 NULL,则系统将为新登录生成 SID。不管是否使用 varbinary 数据类型,NULL 以外的值的长度都必须正好是 16 个字节,并且一定不能已经存在。指定 sid 非常有用,例如,如果您要编写脚本,或将 SQL Server 登录从一台服务器移动到另一台服务器,并且想让登录在不同服务器上使用相同的 SID,都需要指定它。
- [ @encryptopt = ] 'encryption_option'
指定是以明文形式,还是以明文密码的哈希运算结果来传递密码。注意,不进行加密。在本讨论中使用“加密”一词是为了向后兼容。如果传入明文密码,将对它进行哈希运算。哈希值将存储起来。encryption_option 的数据类型为 varchar(20),可以是下列值之一:
值 说明 NULL
以明文形式传递密码。这是默认设置。
skip_encryption
密码已经过哈希运算。数据库引擎应存储值,且不对其重新进行哈希运算。
skip_encryption_old
所提供的密码由 SQL Server 的早期版本进行哈希运算。数据库引擎应存储值,且不对其重新进行哈希运算。提供该选项只是为了升级。
0(成功)或 1(失败)
SQL Server 登录名可以包含 1 到 128 个字符,其中包括字母、符号和数字。登录名不能包含反斜杠 (/);它可以是保留登录名,例如 sa 或 public,或已经存在;或者是 NULL 或空字符串 (''
)。
如果提供默认数据库的名称,则不用执行 USE 语句就可以连接到指定的数据库。但是,除非数据库所有者授予您(使用 sp_adduser 或 sp_addrolemember 或 sp_addrole)该数据库的访问权,否则不能使用默认的数据库。
SID 号是一个 GUID,用于唯一地标识服务器中的登录名。
更改服务器的默认语言将不会更改现有登录的默认语言。若要更改服务器的默认语言,请使用 sp_configure。
如果在将登录名添加到 SQL Server 时已对密码进行了哈希运算,则使用 skip_encryption 来取消密码哈希运算将是有用的。如果 SQL Server 的早期版本对密码进行了哈希运算,则使用 skip_encryption_old。
不能在用户定义事务内执行 sp_addlogin。
下表显示了数个与 sp_addlogin 一起使用的存储过程。
sp_grantlogin
添加 Windows 用户或组。
sp_password
更改用户密码。
sp_defaultdb
更改用户的默认数据库。
sp_defaultlanguage
更改用户的默认语言。
需要 ALTER ANY LOGIN 权限。
A. 创建 SQL Server 登录
以下示例为用户 Victoria
创建 SQL Server 登录,密码为 B1r12-36
,并且不指定默认数据库。
EXEC sp_addlogin 'Victoria', 'B1r12-36';GO
B. 创建具有默认数据库的 SQL Server 登录
以下示例为用户 Albert
创建 SQL Server 登录,密码为 B5432-3M6
,默认数据库为 corporate
。
EXEC sp_addlogin 'Albert', 'B5432-3M6', 'corporate';GO
C. 创建具有不同默认语言的 SQL Server 登录
以下示例为用户 TzTodorov
创建 SQL Server 登录,密码为 709hLKH7chjfwv
,默认数据库为 AdventureWorks
,默认语言为 Bulgarian
。
EXEC sp_addlogin 'TzTodorov', '709hLKH7chjfwv', 'AdventureWorks', N'български'
D. 创建具有特定 SID 的 SQL Server 登录
以下示例为用户 Michael
创建 SQL Server 登录,密码为 B548bmM%f6
,默认数据库为 AdventureWorks
,默认语言为 us_english
,SID 为 0x0123456789ABCDEF0123456789ABCDEF
。
EXEC sp_addlogin 'Michael', 'B548bmM%f6', 'AdventureWorks', 'us_english', 0x0123456789ABCDEF0123456789ABCDEF
2.使用脚本
创建新的 SQL Server 登录名。
Transact-SQL 语法约定
CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> }<option_list1> ::= PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ] [ , <option_list2> [ ,... ] ]<option_list2> ::= SID = sid | DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language | CHECK_EXPIRATION = { ON | OFF} | CHECK_POLICY = { ON | OFF} | CREDENTIAL = credential_name <sources> ::= WINDOWS [ WITH <windows_options> [ ,... ] ] | CERTIFICATE certname | ASYMMETRIC KEY asym_key_name<windows_options> ::= DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language
- loginName
指定创建的登录名。有四种类型的登录名:SQL Server 登录名、Windows 登录名、证书映射登录名和非对称密钥映射登录名。在创建从 Windows 域帐户映射的登录名时,必须以 [<域名>/<登录名>] 格式使用 Windows 2000 之前的用户登录名。UPN 不能采用“登录名@域名”格式。请参阅本主题稍后部分中的示例 D。
- PASSWORD = 'password'
仅适用于 SQL Server 登录名。指定正在创建的登录名的密码。应使用强密码。有关详细信息,请参阅 强密码。
- PASSWORD = hashed_password
仅适用于 HASHED 关键字。指定要创建的登录名的密码的哈希值。
- HASHED
仅适用于 SQL Server 登录名。指定在 PASSWORD 参数后输入的密码已经过哈希运算。如果未选择此选项,则在将作为密码输入的字符串存储到数据库中之前,对其进行哈希运算。此选项应仅用于在服务器之间迁移数据库。切勿使用 HASHED 选项创建新的登录名。
- MUST_CHANGE
仅适用于 SQL Server 登录名。如果包括此选项,则 SQL Server 将在首次使用新登录名时提示用户输入新密码。
- CREDENTIAL = credential_name
将映射到新 SQL Server 登录名的凭据的名称。该凭据必须已存在于服务器中。当前此选项只将凭据链接到登录名。在未来的 SQL Server 版本中可能会扩展此选项的功能。
- SID = sid
仅适用于 SQL Server 登录名。指定新 SQL Server 登录名的 GUID。如果未选择此选项,则 SQL Server 自动指派 GUID。
- DEFAULT_DATABASE = database
指定将指派给登录名的默认数据库。如果未包括此选项,则默认数据库将设置为 master。
- DEFAULT_LANGUAGE = language
指定将指派给登录名的默认语言。如果未包括此选项,则默认语言将设置为服务器的当前默认语言。即使将来服务器的默认语言发生更改,登录名的默认语言也仍保持不变。
- CHECK_EXPIRATION = { ON | OFF }
仅适用于 SQL Server 登录名。指定是否对此登录帐户强制实施密码过期策略。默认值为 OFF。
- CHECK_POLICY = { ON | OFF }
仅适用于 SQL Server 登录名。指定应对此登录名强制实施运行 SQL Server 的计算机的 Windows 密码策略。默认值为 ON。
- WINDOWS
指定将登录名映射到 Windows 登录名。
- CERTIFICATE certname
指定将与此登录名关联的证书名称。此证书必须已存在于 master 数据库中。
- ASYMMETRIC KEY asym_key_name
指定将与此登录名关联的非对称密钥的名称。此密钥必须已存在于 master 数据库中。
密码是区分大小写的。
只有创建 SQL Server 登录名时,才支持对密码预先进行哈希运算。
如果指定 MUST_CHANGE,则 CHECK_EXPIRATION 和 CHECK_POLICY 必须设置为 ON。否则,该语句将失败。
不支持 CHECK_POLICY = OFF 和 CHECK_EXPIRATION = ON 的组合。
如果 CHECK_POLICY 设置为 OFF,将对 lockout_time 进行重置,并将 CHECK_EXPIRATION 设置为 OFF。
从证书或非对称密钥创建的登录名仅用于代码签名。不能用于连接到 SQL Server。仅当 master 中已存在证书或非对称密钥时,才能从证书或非对称密钥创建登录名。
需要对服务器拥有 ALTER ANY LOGIN 或 ALTER LOGIN 权限。
如果使用 CREDENTIAL 选项,则还需要对此服务器的 ALTER ANY CREDENTIAL 权限。
A. 创建带密码的登录名
下例为特定用户 ID 创建登录名并分配密码。MUST_CHANGE
选项要求用户在首次连接服务器时更改此密码。
CREATE LOGIN <loginName> WITH PASSWORD = '<enterStrongPasswordHere>' MUST_CHANGE;GO
B. 创建映射到凭据的登录名
下例使用用户 ID 为特定用户创建登录名。此登录名映射到凭据。
CREATE LOGIN <loginName> WITH PASSWORD = '<enterStrongPasswordHere>', CREDENTIAL = <credentialName>;GO
C. 从证书创建登录名
下面的示例用 master 中的证书为特定用户 ID 创建登录名。
USE MASTER;CREATE CERTIFICATE <certificateName> WITH SUBJECT = '<loginName> certificate in master database', EXPIRY_DATE = '12/05/2025';GOCREATE LOGIN <loginName> FROM CERTIFICATE <certificateName>;GO
D. 从 Windows 域帐户创建登录名
下例用 Windows 域帐户创建一个登录名。
CREATE LOGIN [<domainName>/<loginName>] FROM WINDOWS;GO
二、如何删除用户
1.使用存储过程
删除 SQL Server 登录名。这样将阻止使用该登录名对 SQL Server 实例进行访问。
Transact-SQL 语法约定
sp_droplogin [ @loginame = ] 'login'
- [ @loginame = ] 'login'
要删除的登录名。login 的数据类型为 sysname,无默认值。login 必须已存在于 SQL Server 中。
0(成功)或 1(失败)
sp_droplogin 调用 DROP LOGIN。
不能在用户定义的事务内执行 sp_droplogin。
需要对服务器具有 ALTER ANY LOGIN 权限。
以下示例使用 DROP LOGIN
从 SQL Server 实例中删除登录名 Victoria
。这是首选方法。
DROP LOGIN Victoria;GO
2.使用脚本
删除 SQL Server 登录帐户。
Transact-SQL 语法约定
DROP LOGIN login_name
- login_name
指定要删除的登录名。
不能删除正在登录的登录名,也不能删除拥有任何安全对象、服务器级对象或 SQL Server 代理作业的登录名。
可以删除数据库用户映射到的登录名,但是这会创建孤立用户。有关详细信息,请参阅孤立用户故障排除。
需要对服务器具有 ALTER ANY LOGIN 权限。
下列示例将删除登录名 WilliJo
。
DROP LOGIN WilliJo;GO
三、如何修改用户
1.使用脚本
更改 SQL Server 登录帐户的属性。
Transact-SQL 语法约定
ALTER LOGIN login_name { <status_option> | WITH <set_option> [ ,... ] | <cryptographic_credential_option> } <status_option> ::= ENABLE | DISABLE<set_option> ::= PASSWORD = 'password' | hashed_password HASHED [ OLD_PASSWORD = 'oldpassword' | <password_option> [<password_option> ] ] | DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language | NAME = login_name | CHECK_POLICY = { ON | OFF } | CHECK_EXPIRATION = { ON | OFF } | CREDENTIAL = credential_name | NO CREDENTIAL <password_option> ::= MUST_CHANGE | UNLOCK<cryptographic_credentials_option> ::= ADD CREDENTIAL credential_name | DROP CREDENTIAL credential_name
- login_name
指定正在更改的 SQL Server 登录的名称。域登录名必须用方括号括起来,其格式为 [domain/user]。
- ENABLE | DISABLE
启用或禁用此登录名。
- PASSWORD = 'password'
仅适用于 SQL Server 登录名。指定正在更改的登录名的密码。密码是区分大小写的。
- PASSWORD = hashed_password
仅适用于 HASHED 关键字。指定要创建的登录名的密码的哈希值。
- HASHED
仅适用于 SQL Server 登录名。指定在 PASSWORD 参数后输入的密码已经过哈希运算。如果未选择此选项,则在将密码存储到数据库之前,对其进行哈希运算。此选项只能用于在两台服务器之间同步登录名。切勿使用 HASHED 选项定期更改密码。
注意: 此参数只能用于 SQL Server 2000 或更高版本所生成的哈希。
- OLD_PASSWORD = 'oldpassword'
仅适用于 SQL Server 登录名。要指派新密码的登录名的当前密码。密码是区分大小写的。
- MUST_CHANGE
仅适用于 SQL Server 登录名。如果包括此选项,则 SQL Server 将在首次使用已更改的登录名时提示输入更新的密码。
- DEFAULT_DATABASE = database
指定将指派给登录名的默认数据库。
- DEFAULT_LANGUAGE = language
指定将指派给登录名的默认语言。
- NAME = login_name
正在重命名的登录的新名称。如果是 Windows 登录,则与新名称对应的 Windows 主体的 SID 必须匹配与 SQL Server 中的登录相关联的 SID。SQL Server 登录的新名称不能包含反斜杠字符 (/)。
- CHECK_EXPIRATION = { ON | OFF }
仅适用于 SQL Server 登录名。指定是否对此登录名强制实施密码过期策略。默认值为 OFF。
- CHECK_POLICY = { ON | OFF }
仅适用于 SQL Server 登录名。指定应对此登录名强制实施运行 SQL Server 的计算机的 Windows 密码策略。默认值为 ON。
- CREDENTIAL = credential_name
将映射到 SQL Server 登录名的凭据的名称。该凭据必须已存在于服务器中。有关详细信息,请参阅 凭据(数据库引擎)。
- NO CREDENTIAL
删除登录名与服务器凭据的当前所有映射。有关详细信息,请参阅凭据(数据库引擎)。
- UNLOCK
仅适用于 SQL Server 登录名。指定应解锁被锁定的登录名。
- ADD CREDENTIAL
将可扩展的密钥管理 (EKM) 提供程序凭据添加到登录名。有关详细信息,请参阅了解可扩展的密钥管理 (EKM)。
- DROP CREDENTIAL
删除登录名的可扩展密钥管理 (EKM) 提供程序凭据。有关详细信息,请参阅了解可扩展的密钥管理 (EKM)。
如果 CHECK_POLICY 设置为 ON,则无法使用 HASHED 参数。
如果 CHECK_POLICY 更改为 ON,则将出现以下行为:
- CHECK_EXPIRATION 也设置为 ON,除非它被显式设置为 OFF。
- 密码历史记录使用当前的密码哈希值初始化。
如果 CHECK_POLICY 更改为 OFF,则将出现以下行为:
- CHECK_EXPIRATION 也设置为 OFF。
- 清除密码历史记录。
- lockout_time 的值被重置。
如果指定 MUST_CHANGE,则 CHECK_EXPIRATION 和 CHECK_POLICY 必须设置为 ON。否则,该语句将失败。
如果 CHECK_POLICY 设置为 OFF,则 CHECK_EXPIRATION 不能设置为 ON。包含此选项组合的 ALTER LOGIN 语句将失败。
不能使用带 DISABLE 参数的 ALTER_LOGIN 来拒绝对 Windows 组的访问。例如,ALTER_LOGIN [domain/group] DISABLE 将返回以下错误消息:
“消息 15151,级别 16,状态 1,第 1 行”
“无法对登录名‘Domain/Group' 执行更改,因为它不存在,或者您没有所需的权限。”
这是默认设置。
需要 ALTER ANY LOGIN 权限。
如果使用 CREDENTIAL 选项,则还需要 ALTER ANY CREDENTIAL 权限。
如果正在更改的登录名是 sysadmin 固定服务器角色的成员或 CONTROL SERVER 权限的被授权者,则进行以下更改时还需要 CONTROL SERVER 权限:
- 在不提供旧密码的情况下重置密码。
- 启用 MUST_CHANGE、CHECK_POLICY 或 CHECK_EXPIRATION。
- 更改登录名。
- 启用或禁用登录名。
- 将登录名映射到其他凭据。
主体可更改用于自身登录的密码、默认语言以及默认数据库。
A. 启用已禁用的登录名
以下示例将启用 Mary5
登录名。
ALTER LOGIN Mary5 ENABLE;
B. 更改登录密码
以下示例将登录名 Mary5
的密码更改为强密码。
ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';
C. 更改登录名称
以下示例将 Mary5
登录名称更改为 John2
。
ALTER LOGIN Mary5 WITH NAME = John2;
D. 将登录名映射到凭据
以下示例将登录名 John2
映射到凭据 Custodian04
。
ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;
E. 将登录名映射到可扩展密钥管理凭据
以下示例将登录名 Mary5
映射到 EKM 凭据 EKMProvider1
。
ALTER LOGIN Mary5ADD CREDENTIAL EKMProvider1;GO
F. 解除锁定登录名
若要解除锁定 SQL Server 登录名,请执行以下语句,并将 **** 替换为所需帐户密码。
ALTER LOGIN [Mary5] WITH PASSWORD = '****' UNLOCK ;GO
若要在不更改密码的情况下解除锁定登录名,请关闭检查策略,然后再打开此检查策略。
ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;GO
G. 使用 HASHED 更改登录名的密码
以下示例将 TestUser
登录名的密码更改为已经过哈希运算的值。
ALTER LOGIN TestUser WITH PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED ;GO
- 动态创建Sql Server用户及其权限
- sql server 创建角色,用户,权限
- SQL Server 创建角色,用户,权限
- SQL SERVER创建用户,设置权限
- SQL Server 创建用户及权限管理
- SQL Server 2016 创建用户 配置权限
- SQL SERVER 创建登录用户,授予权限
- SQL Server 2000 创建角色,用户,权限(转)
- SQL SERVER创建用户
- SQL Server 创建用户
- sql server 创建用户角色,给角色赋存储过程执行权限, 将角色赋预某用户
- sql server数据库:创建数据库、建立数据库用户、为用户赋予权限
- sql server 2008 创建用户
- ms sql server 创建用户
- sql server 2008 创建用户
- SQL Server 2008数据库创建用户只读权限的两种方式
- SQL Server的用户及权限
- SQL Server的用户及权限
- C# WinForm开发系列 - 介绍一些开源的第三方控件
- 玉山结婚闹洞房——撒帐词
- (*((volatile unsigned short *) 0x83200000))
- C++中extern “C”含义深层探索
- java 输入一16位的整数,统计0~9这十个数字每一个出现的次数。
- 动态创建Sql Server用户及其权限
- 架构师应该care的东西
- windows 中编译mplayer
- php取服务器当前时间(china)【转】
- cvc-complex-type.2.4.a: Invalid content was found starting with element 'taglib'错误
- 设计模式---工厂模型
- Ext JS高级程序设计
- Server.MapPath() 解析
- MD5加密