Sql Server關(guān)于權(quán)限、角色以及登錄名、用戶名的總結(jié)

官方文檔https://docs.microsoft.com/zh-cn/sql/t-sql/statements/grant-transact-sql?view=sql-server-2017 權(quán)限的一點(diǎn)總結(jié)
1、實(shí)例級(jí)別的角色是固定的，就是public、sysadmin、securityadmin、serveradmin、setupadmin、processadmin、diskadmin、dbcreator、bulkadmin
2、每個(gè)數(shù)據(jù)庫(kù)擁有的角色不一樣，msdb數(shù)據(jù)庫(kù)中引入了SQLAgentUserRole、SQLAgentReaderRole、SQLAgentOperatorRole，而其他數(shù)據(jù)庫(kù)并沒(méi)有這幾個(gè)角色
3、登錄名是屬于實(shí)例級(jí)別的CREATE LOGIN
4、用戶名是屬于數(shù)據(jù)庫(kù)級(jí)別的CREATE USER
5、安裝界面Specify SQL Server administratorss時(shí)增加的用戶，使用SSMS圖形界面打開(kāi)實(shí)例時(shí)，顯示在SSMS的Security--logins這一欄
6、權(quán)限是寫(xiě)在庫(kù)里面的
6.1、在AG的輔助副本上對(duì)某個(gè)登錄名授權(quán)時(shí)，根本無(wú)法授權(quán)報(bào)錯(cuò)數(shù)據(jù)庫(kù)read_only，AG的主副本授權(quán)后，權(quán)限從AG主副本自動(dòng)同步到了AG的輔助副本
6.2、兩個(gè)實(shí)例A、B，兩者上面有一樣的登錄名，A上的一個(gè)登錄名擁有某個(gè)數(shù)據(jù)庫(kù)DB1的owner權(quán)限，把DB1恢復(fù)到B上，發(fā)現(xiàn)B上登錄名也自動(dòng)擁有了數(shù)據(jù)庫(kù)DB1的owner權(quán)限，把B上DB1刪除后，B上登錄名沒(méi)有了數(shù)據(jù)庫(kù)DB1的owner權(quán)限，重新把DB1恢復(fù)到B上后，B上登錄名又自動(dòng)擁有了數(shù)據(jù)庫(kù)DB1的owner權(quán)限。當(dāng)然如果兩個(gè)實(shí)例A、B上面沒(méi)有一樣的登錄名，A上的登錄名user1擁有某個(gè)數(shù)據(jù)庫(kù)DB1的owner權(quán)限，把DB1恢復(fù)到B上，但是B上沒(méi)有user1這個(gè)登錄名，恢復(fù)也不報(bào)錯(cuò)，個(gè)人感覺(jué)恢復(fù)的過(guò)程中，其實(shí)執(zhí)行了一個(gè)語(yǔ)句ALTER ROLE [db_owner] ADD MEMBER [user1]，雖然B上沒(méi)有user1，但是恢復(fù)過(guò)程也不受影響
7、登錄名含有特殊字符時(shí)，在代碼使用中該登錄名時(shí)，使用[]中括號(hào)括起來(lái)，不要用""雙引號(hào)括起來(lái)
8、某個(gè)登錄名擁有某個(gè)數(shù)據(jù)庫(kù)下執(zhí)行sp的權(quán)限，只要有db_datareader角色和execute就可以了
9、grant select to username擁有的權(quán)限不等于db_datareader角色擁有的權(quán)限
10、手工授權(quán)和SSMS圖形界面有時(shí)是有區(qū)別的，比如SSMS圖形界面某個(gè)登錄名勾選了某個(gè)數(shù)據(jù)庫(kù)的public權(quán)限，就說(shuō)明這個(gè)用戶有connect到這個(gè)數(shù)據(jù)庫(kù)的權(quán)限，不等于use dbname;grant connect to username，因?yàn)閳D形界面登錄名在某個(gè)數(shù)據(jù)庫(kù)勾選了public，此時(shí)schema是dbo，而grant connect to username語(yǔ)句執(zhí)行后，此時(shí)SSMS圖形界面可以看到schema是username，之后不能在圖形界面取消這個(gè)登錄名在這個(gè)數(shù)據(jù)庫(kù)的public權(quán)限，會(huì)報(bào)錯(cuò)the database principal owns a schema in the database,and cannot be dropped，只能執(zhí)行revoke connect from username來(lái)取消
11、沒(méi)有g(shù)rant connect on dbname to username這樣的操作
12、關(guān)于操作系統(tǒng)里面的用戶或組，如果用戶已經(jīng)是OS系統(tǒng)的administrator組，則該用戶直接擁有對(duì)所有DB的讀寫(xiě)權(quán)限，因?yàn)檫@個(gè)用戶在數(shù)據(jù)庫(kù)里面隸屬于BUILTIN\Administrators；如果用戶隸屬于數(shù)據(jù)庫(kù)的某個(gè)登錄組，如用戶A1隸屬于數(shù)據(jù)庫(kù)登錄組DA\DBA1，則DA\DBA1有的權(quán)限，A1也有；如何在數(shù)據(jù)庫(kù)登錄列表里面確定是用戶還是組，看這個(gè)對(duì)象前面的圖標(biāo)，如果是一個(gè)人的小圖標(biāo)就是用戶，如果是兩個(gè)人的小圖標(biāo)就是組

登錄名創(chuàng)建語(yǔ)法
CREATE LOGIN [Domain\sqlprocess] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

查詢所有登錄名(登錄名是基于實(shí)例級(jí)別的，不是某個(gè)數(shù)據(jù)庫(kù)級(jí)別的)
select * from sys.server_principals where type in ('U','G','S')

用戶名創(chuàng)建語(yǔ)法
use dbname;
CREATE USER [Domain\sqlprocess] FOR LOGIN [Domain\sqlprocess] WITH DEFAULT_SCHEMA=[Domain\sqlprocess]

查詢某個(gè)數(shù)據(jù)庫(kù)下所有的用戶名(用戶名是基于某個(gè)數(shù)據(jù)庫(kù)的)
SELECT * FROM testdb1.sys.sysusers where status<>0
圖形界面添加登錄名Domain\Wei并賦予該登錄名具有某個(gè)數(shù)據(jù)庫(kù)msdb的db_datareader權(quán)限時(shí)，其實(shí)是按順序執(zhí)行了如下三條語(yǔ)句
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 查看某個(gè)數(shù)據(jù)下，哪些用戶擁有哪些角色權(quán)限
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; 角色

查詢實(shí)例級(jí)別的角色名稱
select * from sys.server_principals where type='R'

查詢某個(gè)數(shù)據(jù)庫(kù)擁有的角色名稱
select * from sys.database_principals where type='R'
每個(gè)數(shù)據(jù)擁有的角色是不一樣的，比如msdb數(shù)據(jù)庫(kù)中引入了SQLAgentUserRole、SQLAgentReaderRole、SQLAgentOperatorRole，而其他數(shù)據(jù)庫(kù)并沒(méi)有這幾個(gè)角色

SQLAgentReaderRole對(duì)視圖msdb.dbo.sysjobs_view有SELECT權(quán)限(繼承SQLAgentUserRole的權(quán)限)，GUI界面操作時(shí)從視圖獲取數(shù)據(jù)。所以添加到此角色后，展開(kāi)作業(yè)就能返回所有作業(yè)。
我們用語(yǔ)句查詢作業(yè)時(shí)，習(xí)慣直接從msdb.dbo.sysjobs這類表入手。但SQLAgentUserRole角色并沒(méi)有對(duì)此類表有SELECT權(quán)限，因此常規(guī)語(yǔ)句會(huì)報(bào)拒絕對(duì)對(duì)象的SELECT權(quán)限。

查詢某數(shù)據(jù)庫(kù)下，角色擁有的成員信息
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;   某個(gè)登錄名擁有某個(gè)數(shù)據(jù)庫(kù)下執(zhí)行sp的權(quán)限，只要有db_datareader角色和execute就可以了
use dbname
ALTER ROLE [db_datareader] ADD MEMBER [Domain\account];
grant execute to [Domain\account]; 查看SQLServer用戶哪些權(quán)限是使用grant命令操作賦予的
use dbname
exec sp_helprotect @username = 'username'
在DB1這個(gè)數(shù)據(jù)庫(kù)里，對(duì)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]; 創(chuàng)建一個(gè)只讀角色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也無(wú)法登錄的理解
原因：是因?yàn)榘惭b數(shù)據(jù)庫(kù)后，沒(méi)有把a(bǔ)dministrator加入到sqlserver的登錄用戶中。
比如一臺(tái)服務(wù)器名稱為dbprod127,但是登錄名里面并沒(méi)有builtin\administrator和dbprod127\administrator,這樣使用本地administrator登錄操作系統(tǒng)后,再打開(kāi)SSMS無(wú)法登錄本地的sqlserver數(shù)據(jù)庫(kù) 修改權(quán)限報(bào)錯(cuò)Cannot add the principal，incorrect syntax near 'XX'
ALTER ROLE [db_datareader] ADD MEMBER [Domain\wang]
報(bào)錯(cuò)：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] 登錄名含有特殊字符時(shí)，使用[]中括號(hào)括起來(lái)，不要用""雙引號(hào)括起來(lái)
在job里面的代碼直接寫(xiě)成如下會(huì)報(bào)錯(cuò):incorrect syntax near 'Domain\wang'
grant execute to "Domain\j.wang"

修改成如下即可
grant execute to [Domain\j.wang] 數(shù)據(jù)庫(kù)的角色

public
--public 角色是一個(gè)特殊的數(shù)據(jù)庫(kù)角色，每個(gè)數(shù)據(jù)庫(kù)用戶都屬于它。public 角色：
--捕獲數(shù)據(jù)庫(kù)中用戶的所有默認(rèn)權(quán)限。
--無(wú)法將用戶、組或角色指派給它，因?yàn)槟J(rèn)情況下它們即屬于該角色。
--含在每個(gè)數(shù)據(jù)庫(kù)中，包括 master、msdb、tempdb、model 和所有用戶數(shù)據(jù)庫(kù)。
--無(wú)法除去。

db_owner
--進(jìn)行所有數(shù)據(jù)庫(kù)角色的活動(dòng)，以及數(shù)據(jù)庫(kù)中的其它維護(hù)和配置活動(dòng)。
--該角色的權(quán)限跨越所有其它固定數(shù)據(jù)庫(kù)角色。

db_accessadmin
--在數(shù)據(jù)庫(kù)中添加或刪除 Windows NT 4.0 或 Windows 2000 組和用戶以及 SQL Server 用戶。

db_datareader
--查看來(lái)自數(shù)據(jù)庫(kù)中所有用戶表的全部數(shù)據(jù)。

db_datawriter
--添加、更改或刪除來(lái)自數(shù)據(jù)庫(kù)中所有用戶表的數(shù)據(jù)

db_ddladmin
--添加、修改或除去數(shù)據(jù)庫(kù)中的對(duì)象(運(yùn)行所有 DDL)

db_securityadmin
--管理 SQL Server 2000 數(shù)據(jù)庫(kù)角色的角色和成員，并管理數(shù)據(jù)庫(kù)中的語(yǔ)句和對(duì)象權(quán)限

db_backupoperator
--有備份數(shù)據(jù)庫(kù)的權(quán)限

db_denydatareader
--拒絕選擇數(shù)據(jù)庫(kù)數(shù)據(jù)的權(quán)限

db_denydatawriter
--拒絕更改數(shù)據(jù)庫(kù)數(shù)據(jù)的權(quán)限