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
--拒絕更改數據庫數據的權限