SQL Server 登录账号与数据库用户

我们在使用 SQL Server 时，经常会遇到账号创建及授权、数据库迁移、实例迁移、镜像或复制或ALwaysON AG 等高可用同步，这些操作都是实例以下级别的操作，需要我们一个个去迁移相关的服务器对象或者数据库对象。除了数据结构和数据的迁移，遇到较多的应该是账号迁移了。

登录账号与数据库用户管理

实例之间的账号如何迁移呢？在这前，我们首先要了解登录账号（Login）与数据库用户（User）之间有什么样的关系。这里我创建了2个数据库（DB01、DB02），创建了2个登录账号（LoginA、LoginB），创建了3个数据库用户，关联信息如下图。

注：这里只考虑带有登录名的数据库用户

上图说明：

• 一个登录账号（Login）可以关联多个数据库用户（User），即一个登录账号可以访问多个数据库；

• 一个数据库用户（User）仅可以关联一个登录账号（Login）；

• 登录账号（Login）有9个固定服务器角色可分配；

• 数据库用户（User）有10个固定服务器角色可分配；

创建一个Login ，通过分配不同的服务器角色，Login可以对服务器相关模块进行管理。如果Login未授予任何服务器角色，默认只有“连接SQL”的权限，及通用的服务器角色public的权限。

类似地，创建一个数据库User ，通过分配不同的数据库角色，User可以对它所在的数据库相关模块进行管理。如果User未授予任何数据库角色，默认只有通用的数据库角色public的权限。

对于服务器角色或当前数据库角色，可以使用以下SQL查看

EXEC sp_helpsrvrole --所有服务器角色
EXEC sp_srvrolepermission @srvrolename = 'sysadmin' --查看某角色详细权限
EXEC sp_helpsrvrolemember @srvrolename = 'sysadmin' --查看某角色成员


EXEC sp_helpdbfixedrole  --所有数据库角色
EXEC sp_dbfixedrolepermission @rolename = 'db_owner' --查看某角色详细权限
EXEC sp_helprolemember @rolename = 'db_owner' --查看某角色成员

通常为了使访问更加安全，使用最小权限原则，我们只允许业务账号对数据库进行DML操作。那么我们应该如何分配权限呢？

如上图，我们创建一个登录账号Login，该账号并未授予任何权限。2个数据库中也创建一个数据库用户User并关联Login，并把数据库角色 db_datareader、db_datawriter 分配给用户User。完成后，该登录账号只能访问 2个数据库，且只能对数据库进行 SELECT、INSERT、UPDATE、DELETE 操作。下图为SSMS管理工具的操作，创建账号同时创建数据库用户并授权。下图中，当我们勾选某个数据库的时，系统默认创建与登录账号同名的数据库用户，你也可以更改，但我们建议保持账号与用户名称一样。

除了服务器角色或者数据库角色，你也可以自己创建相应的角色并分配权限。以上分配的都是角色，角色只是让我们更方便管理和分配权限，你还可以对更详细的对象进行权限分配。如下图，左侧为服务器对象及相关权限，可以对登录账号Login进行其他授权。右侧为数据库级别的对象，可以对数据库用户User进行单独授权（如只能对某张表进行查询）。

SELECT * FROM sys.fn_builtin_permissions(default)

例如，对当前数据库schema为dbo授予DML权限给某个数据库用户，同样达到相同的效果。

GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[dbo] TO [LoginD]

数据库用户的权限分配，有DB级别、Schema级别、对象级别。此外，数据库还有所有者的概念，即相当于该数据库的管理员。SQL Server 在对象创建期间将所有者分配给安全对象，以保护并确保所有者对这些对象的永久访问，以便可以管理对象。在更改数据库所有者之前，需要删除同名的数据库用户，否则会冲突。如果需要删除该登录账号，须更改数据库所有者为别的账号，才允许删除。如下把数据库DBA的所有者更改为账号LoginD，即使账号LoginD没有分配任何权限，也可以操作该数据库中的对象。

ALTER AUTHORIZATION ON DATABASE::[DBA] TO [LoginD]

登录账号与数据库用户关联

上文我们可以了解到，登录账号与数据库用户存在某种关联，但是怎么关联的呢？我曾多次遇到过同事的问题，他的数据库迁移了或者删除了登录账号，再手动创建一个账号密码一致的登录账号，但该新账号却无法对数据库进行操作。

这就是我们应该关注的一个重要概念：安全标识符（SID, Security Identifiers）。登录名就是使用安全标识符（SID）映射并标识给数据库用户的。SID 结构取决于 SQL Server 版本，登录账号 SID 是一个 16进制的值（如 0x1894043E35CB3E4A95832AB9B01F652F）。为了保证新创建的账号与数据库用户关联起来，我们要保证登录账号的SID与数据库用户的SID一致，下面就是一个简单的实验。

USE master
GO
--创建登录账号
CREATE LOGIN LoginC WITH PASSWORD = 'LoginC', CHECK_POLICY=OFF;
GO
--创建数据库用户关联此登录账号，并授权权限
USE [DBA]
GO
CREATE USER [LoginC] FOR LOGIN [LoginC]
GO
ALTER ROLE [db_owner] ADD MEMBER [LoginC]
GO
--查看登录账号SID
SELECT name, sid FROM sys.sql_logins WHERE name = 'LoginC';
--LoginC  0x24EB1ABC8E1E41429A2828E08F2E418C
GO


--删除登录账号，此时的数据库用户，我们称之为孤立用户
DROP LOGIN LoginC;
GO
--查看当前数据库的孤立用户
exec sp_change_users_login @Action='Report';
--LoginC  0x24EB1ABC8E1E41429A2828E08F2E418C
GO


--重新创建SID一致的登录账号
CREATE LOGIN LoginC WITH PASSWORD = 'LoginC', SID = 0x24EB1ABC8E1E41429A2828E08F2E418C, CHECK_POLICY=OFF;
GO

此时登录账号 LoginC，仍然有数据库DBA的db_owner权限，因为新账号SID与数据库用户的SID一样。你也可以创建不同名称的登录账号（如账号LoginD），只要保证SID一样即可。

综上所述，当我们迁移数据库的时候，数据库内的用户和权限是一直存在的，它们是数据库的内部对象，存在数据库中，随着数据库的迁移保持一致。但是，登录账号则不一样，登录账号是数据库之外的对象，是实例级别下的对象，需要我们手动迁移。

如果你不需要以前的账号和密码，你可以可以手动创建一个新的登录账号，再关联数据库用户。或者与上面提到的一样，创建一个SID相同的登录账号。

USE DBA;
CREATE LOGIN LoginC WITH PASSWORD = 'LoginC', CHECK_POLICY=OFF;;
GO
--  对孤立用户连接到现有的登录名
exec sp_change_users_login 
  @action='update_one', 
  @usernamepattern='LoginC', --当前数据库孤立用户
  @loginname='LoginC'; --关联的登录账号
GO
--  也可以再次修改密码
sp_password @old=null, @new='LoginC', @loginame='LoginC';
GO

迁移账号除了保持SID与数据库用户SID一致，最重要的一点是，我们不必知道登录账号的密码，也可以保持密码一致进行迁移！系统对象 sys.sql_logins 中可查看账号的密码哈希，从 SQL Server 2012 (11.x) 开始，存储的密码信息使用 SHA-512 加盐密码进行计算。

通过以下脚本，可以导出SQL登录账号的创建脚本，包含SID值和已加密的密码。

SELECT 'create login [' + sp.name + '] ' 
+ CASE WHEN sp.type IN ('U','G') THEN 'from windows ' ELSE '' END + 'with ' 
+ CASE WHEN sp.type = 'S'
  THEN 'password = ' + master.sys.fn_varbintohexstr(sl.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(sl.sid) + ', check_expiration = ' 
    + CASE WHEN sl.is_expiration_checked > 0 THEN 'ON, ' ELSE 'OFF, ' END + 'check_policy = ' 
    + CASE WHEN sl.is_policy_checked > 0 THEN 'ON, ' ELSE 'OFF, ' END 
    + CASE WHEN sl.credential_id > 0 THEN 'credential = ' + c.name + ', ' ELSE '' END
    ELSE '' END + 'default_database = ' + sp.default_database_name 
+ CASE WHEN len(sp.default_language_name) > 0 THEN ', default_language = ' + sp.default_language_name ELSE '' END
FROM sys.server_principals sp
LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
LEFT JOIN sys.credentials c ON sl.credential_id = c.credential_id
WHERE sp.type ='S' AND sp.name <> 'sa'
AND sp.name NOT LIKE '##%'
GO

账号同步

在高可用场景，数据库及相关对象是可同步的，而登录账号则需要我们使用其他方案进行同步，这是DBA常遇到的问题。那么，有什么方法可以在实例之间同步账号呢？

• Dbatools 脚本同步

在某台服务器上执行相应的脚本，查找所有的可用性副本并连接实例进行操作。可设置Windows定时任务执行。

Get-DbaLogin -SqlInstance $P_Instance | Copy-DbaLogin -Destination $S_Instance | Out-Null


$LoginNames   = Get-DbaLogin -SqlInstance $P_Instance | Select-Object -ExpandProperty "Name"
Sync-DbaLoginPermission -Source $P_Instance -Destination $S_Instance -Login $LoginNames | Out-Null

或者，也可以使用SQL Server 代理作业来调用Powershell脚本。

• SSIS进行账号传输

创建SSIS任务，使用SQL Server代理定时执行。

https://learn.microsoft.com/en-us/sql/integration-services/control-flow/transfer-logins-task

• 链接服务器同步

创建链接服务器，主副本将生成的login脚本，遍历所有辅助副本执行。这需要借助SQL Server代理作业做定时任务检查和执行，不过需要维护好链接服务器。

• 自设计同步

以 AlwaysOn AG 为例，主副本创建一个账号表，用于记录实例的所有SQL账号信息，并设置同步。主副本的代理作业可设置定时1小时检查是否有最新SQL账号、或者是否有账号变更（如改密码、禁用等），如果有，则新增/更新到账号表。对于所有的辅助副本，同样创建一个代理作业，定时1小时检查同步过来的账号表，如果有变化，则创建/更新相应账号，达到同步目的。此方案的好处是不需要编写外部脚本来操作和调度。

• 编写脚本同步

当然，我们通过SQL能生成账号脚本后，几乎可以使用任意第三方脚本来进行同步。只要能连接数据库、只要有一个任务调度工具，我们就可以使用更多的方案进行账号同步。https://learn.microsoft.com/en-US/troubleshoot/sql/security/transfer-logins-passwords-between-instances

其实MSSQL有自带的实时方案同步账号，很多实时方案可使用触发器、警报、审计等捕获Login事件，但操作属于同一个事务中，跨服务器的操作放同一个事务中确实不太好。当然也可以实时捕获Login操作放至异步队列中同步，但也增加了复杂度。账号变更的操作是比较少的，我本人建议使用定时任务检查并执行脚本同步。如果你有更好的方案，欢迎讨论。

文中主要讨论的是具有账号密码访问的登录账号与数据库用户的关系。在实例级别的访问上，除了SQL账号，还可以使用Windows账号、域账号、组。而在数据库级别上，还可以创建不关联登录账号的数据库用户，如无密码的用户、包含性数据库中独立密码的用户、证书或密钥映射的用户。更多了解官方文档《权限层次结构》。https://learn.microsoft.com/zh-cn/sql/relational-databases/security/permissions-hierarchy-database-engine

值得关注的是，SQL Server 2022 发布了新特性 Contained Availability Group，它支持在可用性组级别以及实例级别管理元数据对象（用户、登录名、权限、SQL 代理作业等），在每个包含的可用性组都有自己独立的 master 和 msdb 系统数据库。

最近文章推荐：

• Windows 电源计划对 SQL 查询影响
  

• SQL Server 存储过程最佳实践
  

• SQL Server 数据库精确点还原
  

• SQL Server NUL 设备
  

• SQL Server AlwaysOn AG 无AD域无DNS的跨子网集群实战
  

历史文章推荐：

• Windows Cluster 投票权问题
  

• Windows Cluster 分布式算法
  

• SQL Server 高可用方案介绍
  

• SQL Server 增量数据同步
  

• SQL Server 配置 Zabbix 监控