Sql Server關於許可權、角色以及登入名、使用者名稱的總結

官方文件


許可權的一點總結
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
--拒絕更改資料庫資料的許可權