Sql Server关于权限、角色以及登录名、用户名的总结

官方文档https://docs.microsoft.com/zh-cn/sql/t-sql/statements/grant-transact-sql?view=sql-server-2017

权限的一点总结
1、实例级别的角色是固定的，就是public、sysadmin、securityadmin、serveradmin、setupadmin、processadmin、diskadmin、dbcreator、bulkadmin
2、每个数据库拥有的角色不一样，msdb数据库中引入了SQLAgentUserRole、SQLAgentReaderRole、SQLAgentOperatorRole，而其他数据库并没有这几个角色
3、登录名是属于实例级别的CREATE LOGIN
4、用户名是属于数据库级别的CREATE USER
5、安装界面Specify SQL Server administratorss时增加的用户，使用SSMS图形界面打开实例时，显示在SSMS的Security–logins这一栏
6、权限是写在库里面的
6.1、在AG的辅助副本上对某个登录名授权时，根本无法授权报错数据库read_only，AG的主副本授权后，权限从AG主副本自动同步到了AG的辅助副本
6.2、两个实例A、B，两者上面有一样的登录名，A上的一个登录名user1拥有某个数据库DB1的owner权限，把DB1恢复到B上，发现B上的登录名user1也自动拥有了数据库DB1的owner权限，把B上DB1删除后，B上登录名user1没有了数据库DB1的owner权限，重新把DB1恢复到B上后，B上登录名user1又自动拥有了数据库DB1的owner权限。当然如果两个实例A、B上面没有一样的登录名，A上的登录名user1拥有某个数据库DB1的owner权限，把DB1恢复到B上，但是B上没有user1这个登录名，恢复也不报错，个人感觉恢复的过程中，其实执行了一个语句ALTER ROLE [db_owner] ADD MEMBER [user1]，虽然B上没有user1，但是恢复过程也不受影响
7、登录名含有特殊字符时，在代码比如sql agert job的step步骤中使用该登录名时，使用[]中括号括起来，不要用""双引号括起来
8、某个登录名想要拥有某个数据库下执行sp的权限，只要该用户拥有该数据库的db_datareader角色和execute权限就可以了(use dbname;ALTER ROLE [db_datareader] ADD MEMBER [Domain\account];grant execute to [Domain\account]😉
9、grant select to username拥有的权限不等于db_datareader角色拥有的权限
10、手工授权和SSMS图形界面有时是有区别的，比如SSMS图形界面某个登录名勾选了某个数据库的public权限，就说明这个用户有connect到这个数据库的权限，不等于use dbname;grant connect to username，因为图形界面登录名在某个数据库勾选了public，此时schema是dbo，而grant connect to username语句执行后，此时SSMS图形界面可以看到schema是username，之后不能在图形界面取消这个登录名在这个数据库的public权限，会报错the database principal owns a schema in the database,and cannot be dropped，只能执行revoke connect from username来取消
11、没有grant connect on dbname to username这样的操作
12、关于操作系统里面的用户或组，如果用户已经是OS系统的administrator组，则该用户直接拥有对所有DB的读写权限，因为这个用户在数据库里面隶属于BUILTIN\Administrators；如果用户隶属于数据库服务器的某个用户组，如用户A1隶属于数据库服务器用户组DA\DBA1，则DA\DBA1有的权限，A1也有；如何在数据库登录列表里面确定是用户还是组，看这个对象前面的图标，如果是一个人的小图标就是用户，如果是两个人的小图标就是组

登录名创建语法
CREATE LOGIN [Domain\sqlprocess] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

查询所有登录名(登录名是基于实例级别的，不是某个数据库级别的)
select * from sys.server_principals where type in (‘U’,‘G’,‘S’)

用户名创建语法
use dbname;
CREATE USER [Domain\sqlprocess] FOR LOGIN [Domain\sqlprocess] WITH DEFAULT_SCHEMA=[Domain\sqlprocess]

查询某个数据库下所有的用户名(用户名是基于某个数据库的)
SELECT * FROM testdb1.sys.sysusers where status<>0

图形界面添加登录名Domain\Wei并赋予该登录名具有某个数据库msdb的db_datareader权限时，其实是按顺序执行了如下三条语句
USE [master]
GO
CREATE LOGIN [Domain\Wei] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [msdb]
GO
CREATE USER [Domain\Wei] FOR LOGIN [Domain\Wei]
GO
USE [msdb]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Domain\Wei]
GO

查看某个数据下，哪些用户拥有哪些角色权限
USE DB;
WITH CTE AS
(
SELECT u.name AS UserName,
g.name AS dbRole,
‘√’ as ‘flag’
FROM sys.database_principals u
INNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_id
INNER JOIN sys.database_principals g ON g.principal_id = m.role_principal_id
)
SELECT * FROM CTE PIVOT(MAX(flag) FOR dbRole IN ([public],
[db_owner],
[db_accessadmin],
[db_securityadmin],
[db_ddladmin],
[db_backupoperator],
[db_datareader],
[db_datawriter],
[db_denydatareader],
[db_denydatawriter])) as rg;

角色

查询实例级别的角色名称
select * from sys.server_principals where type=‘R’

查询某个数据库拥有的角色名称
select * from sys.database_principals where type=‘R’
每个数据拥有的角色是不一样的，比如msdb数据库中引入了SQLAgentUserRole、SQLAgentReaderRole、SQLAgentOperatorRole，而其他数据库并没有这几个角色

SQLAgentReaderRole对视图msdb.dbo.sysjobs_view有SELECT权限(继承SQLAgentUserRole的权限)，GUI界面操作时从视图获取数据。所以添加到此角色后，展开作业就能返回所有作业。
我们用语句查询作业时，习惯直接从msdb.dbo.sysjobs这类表入手。但SQLAgentUserRole角色并没有对此类表有SELECT权限，因此常规语句会报拒绝对对象的SELECT权限。

查询某数据库下，角色拥有的成员信息(比如db_datareader这个角色，哪些登录名拥有了这个角色)

SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, ‘No members’) AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = ‘R’
ORDER BY DP1.name;

查看SQLServer用户哪些权限是使用grant命令操作赋予的
use dbname
exec sp_helprotect @username = ‘username’

创建一个只读角色db_reader的操作
CREATE SERVER ROLE [db_reader];
GRANT VIEW ANY DATABASE TO [db_reader];
GRANT CONNECT ANY DATABASE TO [db_reader];
GRANT SELECT ALL USER SECURABLES TO [db_reader];

sqlserver为何本地administrator也无法登录的理解
原因：是因为安装数据库后，没有把administrator加入到sqlserver的登录用户中。
比如一台服务器名称为dbprod127,但是登录名里面并没有builtin\administrator和dbprod127\administrator,这样使用本地administrator登录操作系统后,再打开SSMS无法登录本地的sqlserver数据库

修改权限报错Cannot add the principal，incorrect syntax near ‘XX’
ALTER ROLE [db_datareader] ADD MEMBER [Domain\wang]
报错：Cannot add the principal ‘Domain\wang’, because it does not exist or you do not have permission

解决方法，把代码修改为如下，增加user即可
IF NOT EXISTS ( SELECT TOP (1) 1 FROM sys.database_principals WHERE name = ‘Domain\wang’ )
BEGIN
CREATE USER [Domain\wang] FOR LOGIN [Domain\wang]
END
GO
ALTER ROLE [db_datareader] ADD MEMBER [Domain\wang]

登录名含有特殊字符时，使用[]中括号括起来，不要用""双引号括起来
在job里面的代码直接写成如下会报错:incorrect syntax near ‘Domain\wang’
grant execute to “Domain\j.wang”

修改成如下即可
grant execute to [Domain\j.wang]

删除登录名报错及对应解决方法
DROP LOGIN [Domain\user1]
报错：Server principal ‘Domain\user1’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.
解决方法
SELECT b.name as Grantor, c.name as Grantee, a.state_desc as PermissionState
, a.class_desc as PermissionClass, a.type as PermissionType
, a.permission_name as PermissionName, a.major_id as SecurableID
FROM sys.server_permissions a JOIN sys.server_principals b
ON a.grantor_principal_id = b.principal_id
JOIN sys.server_principals c
ON a.grantee_principal_id = c.principal_id
WHERE grantor_principal_id =
(SELECT principal_id FROM sys.server_principals WHERE name = ‘Domain\user1’)
–Domain\user1拥有ENDPOINT的权限
select * from sys.endpoints where endpoint_id=上面的a.major_id
–查询该用户ENDPOINT对应的具体名称， 原来是mirror
ALTER AUTHORIZATION ON ENDPOINT::mirroring TO [Domain\userXX]
–修改mirror的权限给其他用户Domain\userXX
DROP LOGIN [Domain\user1]
–正常删除了

数据库的角色

public
–public 角色是一个特殊的数据库角色，每个数据库用户都属于它。public 角色：
–捕获数据库中用户的所有默认权限。
–无法将用户、组或角色指派给它，因为默认情况下它们即属于该角色。
–含在每个数据库中，包括 master、msdb、tempdb、model 和所有用户数据库。
–无法除去。

db_owner
–进行所有数据库角色的活动，以及数据库中的其它维护和配置活动。
–该角色的权限跨越所有其它固定数据库角色。

db_accessadmin
–在数据库中添加或删除 Windows NT 4.0 或 Windows 2000 组和用户以及 SQL Server 用户。

db_datareader
–查看来自数据库中所有用户表的全部数据。

db_datawriter
–添加、更改或删除来自数据库中所有用户表的数据

db_ddladmin
–添加、修改或除去数据库中的对象(运行所有 DDL)

db_securityadmin
–管理 SQL Server 2000 数据库角色的角色和成员，并管理数据库中的语句和对象权限

db_backupoperator
–有备份数据库的权限

db_denydatareader
–拒绝选择数据库数据的权限

db_denydatawriter
–拒绝更改数据库数据的权限