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上的一个登录名拥有某个数据库DB1的owner权限，把DB1恢复到B上，发现B上登录名也自动拥有了数据库DB1的owner权限，把B上DB1删除后，B上登录名没有了数据库DB1的owner权限，重新把DB1恢复到B上后，B上登录名又自动拥有了数据库DB1的owner权限。当然如果两个实例A、B上面没有一样的登录名，A上的登录名user1拥有某个数据库DB1的owner权限，把DB1恢复到B上，但是B上没有user1这个登录名，恢复也不报错，个人感觉恢复的过程中，其实执行了一个语句ALTER ROLE [db_owner] ADD MEMBER [user1]，虽然B上没有user1，但是恢复过程也不受影响
7、登录名含有特殊字符时，在代码使用中该登录名时，使用[]中括号括起来，不要用""双引号括起来
8、某个登录名拥有某个数据库下执行sp的权限，只要有db_datareader角色和execute就可以了
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权限。

查询某数据库下，角色拥有的成员信息
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;   某个登录名拥有某个数据库下执行sp的权限，只要有db_datareader角色和execute就可以了
use dbname
ALTER ROLE [db_datareader] ADD MEMBER [Domain\account];
grant execute to [Domain\account]; 查看SQLServer用户哪些权限是使用grant命令操作赋予的
use dbname
exec sp_helprotect @username = 'username'
在DB1这个数据库里，对Domain\account登录名赋予db_datareader的操作
USE [DB1]
GO
IF NOT EXISTS(SELECT 1 FROM cndb.sys.sysusers WHERE [NAME]='Domain\account')
BEGIN
    CREATE USER [Domain\account] FOR LOGIN [Domain\account];
END
ALTER ROLE [db_datareader] ADD MEMBER [Domain\account]; 创建一个只读角色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] 数据库的角色

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
--拒绝更改数据库数据的权限