（4.5）sql server授权/权限操作/查看权限，权限控制

【0】最佳实践

基本案例

--（1）把实例登录名添加到服务器角色
ALTER SERVER ROLE [sysadmin] ADD MEMBER [WIN-IUPGVIBG48A\sqladmin]   -- 把登录名添加到角色

--（2）把数据库用户名添加到数据库角色
use db;
EXEC sp_addrolemember N'db_datareader', N'test_user'
GO

--（3）授权,库级,test为库名，test_1为数据库用户，test6为表名，dbo为架构名；

use test;
grant select,insert,update,delete  to test_1; --给予test_1用户对于 test库中所有表都可以增删查改
-- grant select,insert,update,delete to database::test to test_1
grant execute to test_1; --给予 test_1 用户对于 test 库中的SP、函数等执行权限；

--（4）授权，表级 grant select,insert,update,delete on test.dbo.test6 to test_1; --在任意环境下执行，给予 test_1 用户 对 test.dbo.test6 表的 增删改查权限 

use test ; -- 在库下，给 test库 t1表权限给 test002
GRANT SELECT ON T1 TO test002;--REVOKE

-- （5）授权，表字段级 
GRANT SELECT(Id,Name) ON T1 TO test002;--REVOKE

【0.0】获取所有登录名、权限、角色信息

SELECT sp.name AS [login_name]  
,CASE WHEN sp.[type]='S' THEN 'SQL 登录名'    
    WHEN sp.[type]='U' THEN 'Windows 登录名'    
    WHEN sp.[type]='G' THEN 'Windows 组'    
    WHEN sp.[type]='R' THEN '服务器角色'    
    WHEN sp.[type]='C' THEN '映射到证书的登录名'    
    WHEN sp.[type]='K' THEN '映射到非对称密钥的登录名'    
END AS [principal_type]  
,sp.is_disabled  
,ISNULL(sp.default_database_name,'') as [default_database_name]  
,ISNULL(rsp.name,'public') AS [server_role]    
,STUFF((SELECT ','+permission_name FROM sys.server_permissions spp where sp.principal_id=spp.grantee_principal_id for xml path('')),1,1,'') as [permissions]  
FROM sys.server_principals sp     
LEFT JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id     
LEFT JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id   
where rsp.name  is not null
ORDER BY [principal_type],sp.principal_id 
--授权服务器角色
select N'EXEC sp_addsrvrolemember  N''' +sp.name+ ''' ,N''' + rsp.name+''' '
FROM sys.server_principals sp     
LEFT JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id     
LEFT JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id   
where rsp.name  is not null

--  授予【数据库角色成员身份】权限  
SELECT 'exec sp_addrolemember N'''+g.name+''', N'''+u.name+''''  
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  
ORDER BY g.name,u.name  
--  授予【安全对象】权限  
SELECT N'grant '+B.permission_name  collate chinese_prc_ci_ai_ws+N' on ['+A.name+N'] to ['+C.name+N']'  
FROM sys.sysobjects A(NOLOCK)   
INNER JOIN sys.database_permissions B(NOLOCK) ON A.id=B.major_id   
INNER JOIN sys.database_principals C(NOLOCK) ON B.grantee_principal_id=C.principal_id  
--WHERE C.name='kk' --A.name='objectName'

 

【0.1】创建一个只读的登录名账户

Declare @login varchar(200),@role varchar(200), @login_pwd varchar(200)
Set @login='test'
Set @login_pwd='123456'
SET @role='db_datareader'
DECLARE @sql VARCHAR(max)
SET @sql=CAST('use master;CREATE LOGIN '+@login+' WITH PASSWORD=N'''+@login_pwd +''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; GRANT VIEW SERVER STATE TO '+@login+';
GRANT VIEW ANY DEFINITION TO '+@login+';' AS VARCHAR(max))
select @sql=@sql+CAST('use ['+name+'];CREATE USER '+@login+' FOR LOGIN '+@login+';
EXEC sp_addrolemember N'''+@role+''', N'''+@login+''';'+CHAR(10) AS VARCHAR(max)) 
from master.sys.databases where state=0 and database_id>4 and source_database_id is null and is_read_only=0
EXEC(@sql)
Go


-- 从库建立相同sid 登录名即可
select 'create login '+name+' with password=''123456'',sid='+master.sys.fn_varbintohexstr(sid)+' ,CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;' 
from  sys.server_principals where name='test';

 

【0.2】给一个数据库表授权【最佳实践】

USE [master]
GO
if not exists(select 1 from sys.syslogins where name = 'ruser')
begin
    print 'create login'
    CREATE LOGIN [ruser] WITH PASSWORD=N'suy6NfT6cJcsZsmFt', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
end
else
    print 'exists login'
GO

use BOX_logCenter;
if not exists(select * from sys.sysusers where name ='ruser')
begin
    print 'create user'
    create user ruser for login ruser;
end
else
    print  'exists user'

grant select on tTeaNew_Money to ruser;
grant select on tTeaHisBillScore to ruser;

【0.3】 获取该数据库下的账户角色信息 及 创建语句（新建库复制原库用户、角色权限）

use test
go

DECLARE  @Database varchar(255),
@loginName varchar(255),
@roleName varchar(255),
@sql nvarchar(max);
 SET @sql=N'';
 
DECLARE curLogin CURSOR LOCAL for
select db_name() as dbname,dp.name as username,dpr.name as rolename 
from sys.database_principals dp 
join sys.database_role_members drm on drm.member_principal_id=dp.principal_id
join sys.database_principals dpr on drm.role_principal_id=dpr.principal_id 
join sys.server_principals sp on sp.name=dp.name
where 1=1
and dpr.is_fixed_role=1  
and dp.type<>'R'
and dp.type in('S','U','G') --SQL USER,WINDOWS USER AND windows group
order by username,rolename

OPEN curLogin;
FETCH NEXT FROM curLogin INTO @Database,@loginName,@roleName;
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @sql=@sql+N'
     use '+@Database+';
     if not exists(select * from '+@Database+'.sys.database_principals where name='''+@LoginName+''') 
     begin
        CREATE USER '+QUOTENAME(@LoginName)+' with login = '+quotename(@loginName)+';
     end
     else 
     begin
        ALTER USER '+QUOTENAME(@LoginName)+' with login = '+QUOTENAME(@LoginName)+'
     end;
 '
 print @sql
 --exec sp_executesql @sql
 select @sql=@sql+N'
 use '+@Database+';
 exec sp_addrolemember '+@roleName+', '+@LoginName+''
FETCH NEXT FROM curLogin INTO @Database,@loginName,@roleName;
END
CLOSE curLogin
DEALLOCATE curLogin;

print len(@sql)
print @sql --this will be truncated 
go

【0.4】复制DB账户与角色信息到新库

-- #temp1
declare @newdb nvarchar(200),@olddb nvarchar(200)
select @olddb='QPRecordDB_210716',@newdb='QPRecordDB' --modify here

use tempdb
if object_id('#temp1') is not null
drop table  #temp1

use QPRecordDB_210716  --modify here,olddb

select 
db_name() as dbname,dp.name as username,dpr.name as rolename ,row_number() over(order by dp.name) as rn
into #temp1
from sys.database_principals dp 
join sys.database_role_members drm on drm.member_principal_id=dp.principal_id
join sys.database_principals dpr on drm.role_principal_id=dpr.principal_id 
join sys.server_principals sp on sp.name=dp.name
where 1=1
and dpr.is_fixed_role=1  
and dp.type<>'R'
and dp.type in('S','U','G') --SQL USER,WINDOWS USER AND windows group
order by username,rolename

-- main
declare @rn int,@rn_count int
declare @sql nvarchar(max)

declare @loginName nvarchar(200),@roleName nvarchar(200)

set @sql=N''
set @rn=1
select @rn_count=count(1) from #temp1

while @rn<=@rn_count
begin
    select @loginName=username,@roleName=rolename from #temp1 where rn=@rn
    set @sql=@sql+N'
    use '+@newdb+';
     if not exists(select * from '+@newdb+'.sys.database_principals where name='''+@newdb+''') 
     begin
        CREATE USER '+QUOTENAME(@LoginName)+' for login '+quotename(@loginName)+';
     end
'    

select @sql=@sql+N'
use '+@newdb+';
exec sp_addrolemember '+@roleName+', '+@LoginName+''

set @rn=@rn+1
end
print @sql
exec sp_executesql @sql

【0.5】存储过程实现，拷贝账户及角色权限

use master;
if object_id('copy_privileges') is not null
drop proc copy_privileges
go

create proc copy_privileges
@olddb nvarchar(255),
@newdb nvarchar(255)
as
begin
-- get privileges info
    DECLARE @privileges Table 
　　      ( 
　　      dbname varchar(255), 
　　      username varchar(255), 
　　      rolename  varchar(255), 
        rn int
    )
    insert into @privileges
    exec('
    select 
    db_name() as dbname,dp.name as username,dpr.name as rolename ,row_number() over(order by dp.name) as rn
    from '+@olddb+'.sys.database_principals dp 
    join '+@olddb+'.sys.database_role_members drm on drm.member_principal_id=dp.principal_id
    join '+@olddb+'.sys.database_principals dpr on drm.role_principal_id=dpr.principal_id 
    join '+@olddb+'.sys.server_principals sp on sp.name=dp.name
    where 1=1
    and dpr.is_fixed_role=1  
    and dp.type<>''R''
    and dp.type in(''S'',''U'',''G'') --SQL USER,WINDOWS USER AND windows group
    order by username,rolename
    ')

-- main
    declare @rn int,@rn_count int
    declare @sql nvarchar(max)
    declare @loginName nvarchar(200),@roleName nvarchar(200)

    set @sql=N''
    set @rn=1
    select @rn_count=count(1) from @privileges

    while @rn<=@rn_count
    begin
        select @loginName=username,@roleName=rolename from @privileges where rn=@rn
        set @sql=@sql+N'
        use '+@newdb+';
         if not exists(select * from '+@newdb+'.sys.database_principals where name='''+@loginName+''') 
         begin
            CREATE USER '+QUOTENAME(@LoginName)+' for login '+quotename(@loginName)+';
         end
    '    
        select @sql=@sql+N'
        use '+@newdb+';
        exec sp_addrolemember '+@roleName+', '+@LoginName+''
        set @rn=@rn+1
    end
    print @sql
    exec sp_executesql @sql
end

go
-- run proc
use master;
go
copy_privileges 'test100','test22'

【0.6】今天库重名归档，拷贝权限过去

---------------------------------begin copy grant info -----------------
use master;
if object_id('copy_privileges') is not null
drop proc copy_privileges
go

create proc copy_privileges
@olddb nvarchar(255),
@newdb nvarchar(255)
as
begin
-- get privileges info
DECLARE @privileges Table 
( 
dbname varchar(255), 
username varchar(255), 
rolename  varchar(255), 
rn int
)
insert into @privileges
exec('
select 
db_name() as dbname,dp.name as username,dpr.name as rolename ,row_number() over(order by dp.name) as rn
from '+@olddb+'.sys.database_principals dp 
join '+@olddb+'.sys.database_role_members drm on drm.member_principal_id=dp.principal_id
join '+@olddb+'.sys.database_principals dpr on drm.role_principal_id=dpr.principal_id 
join '+@olddb+'.sys.server_principals sp on sp.name=dp.name
where 1=1
and dpr.is_fixed_role=1  
and dp.type<>''R''
and dp.type in(''S'',''U'',''G'') --SQL USER,WINDOWS USER AND windows group
order by username,rolename
')

-- main
declare @rn int,@rn_count int
declare @sql nvarchar(max)
declare @loginName nvarchar(200),@roleName nvarchar(200)

set @sql=N''
set @rn=1
select @rn_count=count(1) from @privileges

while @rn<=@rn_count
begin
select @loginName=username,@roleName=rolename from @privileges where rn=@rn
set @sql=@sql+N'
use '+@newdb+';
if not exists(select * from '+@newdb+'.sys.database_principals where name='''+@loginName+''') 
begin
CREATE USER '+QUOTENAME(@LoginName)+' for login '+quotename(@loginName)+';
end
'    
select @sql=@sql+N'
use '+@newdb+';
exec sp_addrolemember '+@roleName+', '+@LoginName+''
set @rn=@rn+1
end
print @sql
exec sp_executesql @sql
end

go
-- run proc
use master;
go
DECLARE @sql VARCHAR(300),@db_name VARCHAR(500)
DECLARE @olddb VARCHAR(500),@newdb VARCHAR(500)
SET @newdb='QPRecordDB'
select @olddb=@db_name+'_'+convert(varchar(100),getdate(),12)
exec copy_privileges @olddb,@newdb

go
drop proc copy_privileges
---------------------------------end copy grant info -----------------

【0.7】孤立用户相关，获取登录名创建语句

SELECT  'CREATE LOGIN [' + p.name + '] '
       + CASE WHEN p.type IN ( 'U', 'G' ) THEN 'FROM windows '
              ELSE ''
         END + 'WITH ' + CASE WHEN p.type = 'S'
                              THEN 'password = '
                                   + master.sys.fn_varbintohexstr(l.password_hash)
                                   + ' hashed, ' + 'sid = '
                                   + master.sys.fn_varbintohexstr(l.sid)
                                   + ', check_expiration = '
                                   + CASE WHEN l.is_expiration_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END + 'check_policy = '
                                   + CASE WHEN l.is_policy_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END
                                   + CASE WHEN l.credential_id > 0
                                          THEN 'credential = ' + c.name
                                               + ', '
                                          ELSE ''
                                     END
                              ELSE ''
                         END + 'default_database = '
       + p.default_database_name
       + CASE WHEN LEN(p.default_language_name) > 0
              THEN ', default_language = ' + p.default_language_name
              ELSE ''
         END
FROM    sys.server_principals p
       LEFT JOIN sys.sql_logins l
       ON p.principal_id = l.principal_id
       LEFT JOIN sys.credentials c
       ON l.credential_id = c.credential_id
WHERE   p.type IN ( 'S', 'U', 'G' )
       --AND p.name NOT IN ( 'sa')
               AND p.name NOT LIKE '%##%'
               AND p.name NOT LIKE '%NT SERVICE%'
               AND p.name NOT LIKE '%NT AUTHORITY%'

 

【1】概述

【1.1】三大操作：基本概述

--1、GRANT 将安全对象的权限授予主体。

GRANT SELECT ON Marketing.Salesperson(SalespersonID, EmailAlias)

--2、DENY 　拒绝授予主体权限。防止主体通过其组或角色成员身份继承权限。

DENY SELECT ON Schema::Customers FROM Sales　　　　

--3、REVOKE　　取消以前授予或拒绝了的权限。

REVOKE SELECT ON Object::Regions FROM UserA

【1.2】权限类别

权限层次图：

　　

 

都有哪些权限：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver15

常用权限：

　　（1）服务器权限：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/grant-server-permissions-transact-sql?view=sql-server-ver15

　　（2）数据库权限：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-ver15

【2】示例

　　SQL Server 2012的实例中有一个名为 SalesDB 的数据库。

　　SalesDB包含一个名为 Customers 的架构，此架构中有一个名为 Regions 表 。

　　数据库有一个名为 Sales 的角色，此角色中有一个名为UserA的用户。

　　用户UserA被授予对SalesDB.Customers.Regions的Select权限。

　　角色Sales被授予对架构Customers的Select权限。

 

1、不允许/角色Sales（包括用户UserA）在架构Customers中Select任何表。　　

DENY SELECT ON Schema::Customers FROM Sales 
-- GRANT SELECT ON Schema::Customers to Sales 

2、不允许用户 UserA 在架构 Customers 中 Select 任何表

-- GRANT SELECT　On Schema::dbo from test;
DENY SELECT ON Schema::Customers FROM UserA

 

3、移除用户UserA对SalesDB.Customers.Regions 表的Select权限，同时让用户UserA通过角色Sales的权限仍然可以访问架构Customers中的所有表。

REVOKE SELECT ON Object::Regions FROM UserA
-- GRANT SELECT ON Object::Regions FROM UserA

4、不允许角色Sales（包括用户UserA）对SalesDB.Customers.Regions的Select权限。

DENY SELECT ON Object::Regions FROM sales
GRANT SELECT ON Object::Regions To sales

5、不允许用户UserA对SalesDB.Customers.Regions的Select权限

DENY SELECT ON Object::Regions FROM UserA

6、移除角色Sales在架构Customers中的Select权限，但用户UserA有SalesDB.Customers.Regions的Select权限。

REVOKE SELECT ON Schema::Customers FROM Sales 

三、列级的安全

权限可以在列一级进行分配

可以一个语句中对多个列分配权限

一个列级的 GRANT 将覆盖一个表级的 DENY

基本形式：

--（1）库.架构.表名形式
GRANT SELECT ON db.schema.object_table(column1,column2..) TO test_1;

--（2）切换到库，架构.表名形式
use db;
GRANT SELECT ON schema.object_table(column1,column2..) TO test_1;

案例：

这里的 Marketing 是架构名；

use db;

GRANT SELECT ON Marketing.Salesperson(SalespersonID, EmailAlias) TO James;
GO

DENY SELECT ON Marketing.Salesperson TO Holly;
GO

GRANT SELECT ON Marketing.Salesperson(SalespersonID, FirstName, LastName) TO Holly;
GO

 

  

四、再次授权（re-grant）

使用 WITH GRANT OPTION 可以使受让者将其得到的授权再次 GRANT 给其他主体

CASCADE 选项同时 REVOKE（或DENY）从受让者发出的授权

GRANT UPDATE ON Marketing.Salesperson TO James WITH GRANT OPTION;
GO

REVOKE UPDATE ON Marketing.Salesperson FROM James CASCADE;
GO

 

 

五、登录名与角色关系查询

--创建一个简单的登录，登录名为：newlogin；登录密码：123456；默认数据库：master，默认数据库也可以不指定。

EXEC sp_addlogin 'newlogin','123456','master'

--创建用户

--创建一个简单的用户，如果不指定用户名，则添加到当前数据库登录名中，如果不指定角色，则该用户默认属于public角色。下为添加newlogin登录名。

EXEC sp_adduser 'newlogin'

--创建一个带用户名的用户，用户可以与登录名相同（同上一种类似），也可以不同，但要设定当前登录名，用户角色可选，默认为public。下为将用户newuser添加到newlogin登录名中。

EXEC sp_adduser 'newlogin','newuser'

--创建角色

EXEC sp_addrole 'newrole'

--下为将用户下为将用户newuser添加到newlogin登录名中。并指定newrole角色。

EXEC sp_adduser 'newlogin','newuser','newrole'

--为角色newrole赋予jobs表的所有权限

GRANT ALL ON jobs TO newrole

--为角色newrole赋予sales表的查、改权限

GRANT SELECT,UPDATE ON sales TO newrole

--禁止角色newrole使用employees表的插入权限

DENY INSERT ON employees TO newrole

 

另一种创建用户和赋予角色的方式

--为登录newlogin在数据库中添加安全账户newuser

EXEC sp_grantdbaccess 'newlogin','newuser'
--添加newuser为角色newrole的成员
EXEC sp_addrolemember 'newrole','newuser'

 

--数据库用户、角色、登录的删除操作

--删除当前数据库用户

EXEC sp_revokedbaccess 'newuser';

--删除数据库登录

EXEC sp_droplogin 'newlogin'

--删除数据库角色

EXEC sp_droprole 'newrole'

--从数据库角色(newrole)中删除用户(newuser)

EXEC sp_droprolemember 'newrole', 'newuser'

--用SQL代码新建登录、用户

--创建带密码的mylogin登录名，MUST_CHANGE 选项需要用户首次连接服务器时更改此密码。

CREATE LOGIN mylogin WITH PASSWORD = '123456' MUST_CHANGE;

--创建映射到凭据的登录名。

--以下示例将创建mylogin登录名。此登录名将映射到mycredential凭据。

CREATE LOGIN mylogin WITH PASSWORD = '123456',

CREDENTIAL = mycredential;

--从Windows 域帐户创建登录名

--如果从Windows 域帐户映射登录名，则登录名必须用方括号([ ]) 括起来。

CREATE LOGIN [jack\xiangzhao] FROM WINDOWS;

--如果指定用户名，则不使用默认登录名作为该数据库用户

CREATE USER myuser FOR LOGIN mylogin

--以下示例将创建用户myuser拥有的数据库角色myrole

CREATE ROLE myrole AUTHORIZATION myuser;

--以下示例将创建db_role固定数据库角色拥有的数据库角色myrole

CREATE ROLE myrole AUTHORIZATION db_role

 ALTER SERVER ROLE [sysadmin] ADD MEMBER [WIN-IUPGVIBG48A\sqladmin]   把登录名添加到角色

 

---------------

以下部分转自：https://www.cnblogs.com/seusoftware/p/4848940.html

在SQL Server数据库中，登录账号分类如下：

(1) SQL账号，需要单独设置密码，比如：sa；

(2) Windows账号，登录windows的账号，比如: administrator，不需要另设密码；

(3) Windows Group 账号, 为本地用户组或域用户组，将组添加到登录，组成员不需要单独创建登录；

查看Windows账号，是否属于某一个/多个用户组：

exec xp_logininfo 'windows_acount','ALL'  --域用户格式为：domain_name\account_name

以下脚本，均假设最终登录账号为：test_login，所有数据库对应的user为test_user

 一、是否有某些权限

【5.1】 检查有没有登录权限

--是否存在有效的登录账号：是否被禁用，sql login还有：密码是否过期，是否被锁定
select is_disabled
,loginproperty(name,'Isexpired') is_expired
,loginproperty(name,'Islocked') is_locked
,* 
from sys.server_principals
where name = 'test_login'

【5.2】检查有没有访问某数据库的权限

USE DBA
GO

--检查是否有数据库的CONNECT权限即可
select b.* from sys.database_principals a
inner join sys.database_permissions b
on a.principal_id = b.grantee_principal_id
where SUSER_SNAME(a.sid) = 'test_login'
and b.permission_name = 'CONNECT'

--老的系统表sysusers也可以检查
SELECT name, hasdbaccess,* FROM sysusers a
WHERE SUSER_SNAME(a.sid) = 'test_login'

如果有很多个数据库，写个游标1个个去检查即可。

【5.3】检查有没有某个对象的权限

检查有没有某个对象的权限，一般是去尝试运行下脚本比较直观，如果去查各种权限表，角色错综复杂时，很难分辨；

SQL Server 2008之后引入了HAS_PERMS_BY_NAME这个函数，它可以检查当前账号的各种权限，检查其他用户需要用EXECUTE AS来切换：

USE DBA
GO
EXECUTE AS user = 'test_user'
GO
--对象权限
SELECT HAS_PERMS_BY_NAME('Sales.SalesPerson', 'OBJECT', 'INSERT');
SELECT HAS_PERMS_BY_NAME('sp_send_dbmail', 'OBJECT', 'EXEC');
--架构权限
SELECT HAS_PERMS_BY_NAME('test_schema', 'SCHEMA', 'SELECT');
REVERT;
GO

对于是否有登录、访问数据库的权限，用这个函数也可以判断：

USE master
GO
EXECUTE AS login = 'test_login'
GO
--登录权限，本机前2个参数为空即可
SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'CONNECT SQL');
REVERT;
GO

USE DBA
GO
EXECUTE AS user = 'test_user'
GO
--数据库权限
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CONNECT');
REVERT;

 

二. 有哪些权限

权限可以直接分配给账号，也可以分配给账号所属的role，所以要把账号自身权限、所属role权限合并才是最终的账号权限。

Windows账号权限还可以通过用户组分配，所以还要检查这个Windows账号有没有属于某个用户组，如果有还需要加上这个用户组的权限；

下面的脚本，仅检查单个用户/用户组权限。

【5.4】查看实例级的权限

use master
GO
declare @svr_principal_name varchar(1024)
set @svr_principal_name = 'test_login'

declare @svr_principal_id int
select @svr_principal_id = principal_id 
from sys.server_principals p
where p.name = @svr_principal_name

if OBJECT_ID('tempdb..#tmp_svr_role','U') is not null
    drop table #tmp_svr_role;
create table #tmp_svr_role
(
member_principal_id     int,
member_principal_name   varchar(512),
role_principal_id       int, 
role_principal_name     varchar(512)
)

--获取登录账号的所有server role, 从sql server 2012开始，server role可以自定义，成员仅可为fixed server role
;with tmp
as
(
select * from sys.server_role_members 
where member_principal_id = @svr_principal_id
union all
select rm.* from sys.server_role_members rm
inner join tmp 
on rm.member_principal_id = tmp.role_principal_id
)
insert into #tmp_svr_role 
select a.member_principal_id, b.name,
       a.role_principal_id, c.name
 from tmp a
inner join sys.server_principals b
on a.member_principal_id = b.principal_id
inner join sys.server_principals c
on a.role_principal_id = c.principal_id

--登录账号自身权限, sys.server_permissions不包含fixed server role权限，同时手动排除掉public权限
select a.principal_id as member_principal_id, a.name as member_principal_name,
       null as role_principal_id, null as role_principal_name,
       b.permission_name, b.state_desc
from sys.server_principals a
inner join sys.server_permissions b
on a.principal_id = b.grantee_principal_id
where a.principal_id = @svr_principal_id
and b.permission_name <> 'CONNECT SQL'
union all
--server role权限，包含fixed server role和自定义的server role
select a.member_principal_id, a.member_principal_name,
       a.role_principal_id, a.role_principal_name,
       isnull(b.permission_name,'Fixed Server-Level Role: '+role_principal_name) as permission_name, isnull(b.state_desc,'GRANT') as state_desc
from #tmp_svr_role a
left join sys.database_permissions b
on a.role_principal_id = b.grantee_principal_id
union all
--public server role权限，不可以取消public权限，它是每个登录账号的最小权限，仅可连接数据库实例
select @svr_principal_id as member_principal_id,@svr_principal_name as member_principal_name,
       principal_id as role_principal_id, name as role_principal_name, 
       'CONNECT SQL' as permission_name, 'GRANT' as state_desc
from sys.server_principals
where name = 'public'

Instance-Level Permissions

 

注意：服务器角色的权限可以做什么具体的事情，exec sp_srvrolepermission 有大致的介绍，但是也并没有全部列出每一种数据库操作，因为有些操作是被更高级的操作包含的。

【5.5】查看数据库级的权限

仅列出数据库级别的权限，具体的对象名称并没有列出。

use DBA
GO
declare @svr_principal_name varchar(1024)
set @svr_principal_name = 'test_login'

declare @db_principal_id    int,
        @db_principal_name  varchar(512)
select @db_principal_id = principal_id,
       @db_principal_name = name
from sys.database_principals p
where SUSER_SNAME(sid) = @svr_principal_name

if OBJECT_ID('tempdb..#tmp_db_role','U') is not null
    drop table #tmp_db_role;
create table #tmp_db_role
(
member_principal_id     int,
member_principal_name   varchar(512),
role_principal_id       int, 
role_principal_name     varchar(512)
)

--获取登录账号在当前数据库的所有database role
;with tmp
as
(
select * from sys.database_role_members 
where member_principal_id = @db_principal_id
union all
select rm.* from sys.database_role_members rm
inner join tmp 
on rm.member_principal_id = tmp.role_principal_id
)
insert into #tmp_db_role 
select a.member_principal_id, b.name,
       a.role_principal_id, c.name
 from tmp a
inner join sys.database_principals b
on a.member_principal_id = b.principal_id
inner join sys.database_principals c
on a.role_principal_id = c.principal_id

--登录账号在当前数据库的自身权限, sys.database_permissions不包含fixed database role权限，同时手动排除掉public权限
select a.principal_id as member_principal_id, a.name as member_principal_name,
       null as role_principal_id, null as role_principal_name,
       b.permission_name, b.state_desc
from sys.database_principals a
inner join sys.database_permissions b
on a.principal_id = b.grantee_principal_id
where a.principal_id = @db_principal_id
and b.permission_name <> 'CONNECT'
union all
--database role权限，包含fixed database role和自定义的database role
select a.member_principal_id, a.member_principal_name,
       a.role_principal_id, a.role_principal_name,
       isnull(b.permission_name,'Fixed Database-Level Role: '+role_principal_name) as permission_name, isnull(b.state_desc,'GRANT') as state_desc
from #tmp_db_role a
left join sys.database_permissions b
on a.role_principal_id = b.grantee_principal_id
union all
--public database role权限，不可以取消public权限，它是每个登录账号映射到当前数据库的最小权限，仅可连接当前数据库
select @db_principal_id as member_principal_id, @db_principal_name as member_principal_name,
       principal_id as role_principal_id, name as role_principal_name, 
       'CONNECT' as permission_name, 'GRANT' as state_desc
from sys.database_principals
where name = 'public'

Database-Level Permissions

 

注意：sysadmin的账号在数据库里可能并没有做映射，但权限是有的，隐式映射的用户是dbo

 

【5.6】查看对象级的权限

sys.database_permissions有很多对象类型，major_id, minor_id取决于class_desc，不同的对象关联不同的系统表/视图，脚本里仅列出了最常见的OBJECT_OR_COLUMN, SCHEMA对象权限。

--建立测试用的架构，对象，列
use DBA
GO
if object_id('test_grant','U') is not null
    drop table test_grant
GO
create table test_grant(c1 int, c2 int, c3 int)
grant select (c1, c2) on test_grant to test_user;

if object_id('test_schema.test_t1','U') is not null
    drop table test_schema.test_t1
GO
if exists(select 1 from sys.schemas where name  = 'test_schema')
    drop schema test_schema
GO
create schema test_schema
create table test_schema.test_t1(c1 int, c2 int)
grant select on schema::test_schema to test_user;
GO

--开始获取对象权限
use DBA
GO
declare @svr_principal_name varchar(1024)
set @svr_principal_name = 'test_login'

declare @db_principal_id    int,
        @db_principal_name  varchar(512)
select @db_principal_id = principal_id,
       @db_principal_name = name
from sys.database_principals p
where SUSER_SNAME(sid) = @svr_principal_name

if OBJECT_ID('tempdb..#tmp_db_role','U') is not null
    drop table #tmp_db_role;
create table #tmp_db_role
(
member_principal_id     int,
member_principal_name   varchar(512),
role_principal_id       int, 
role_principal_name     varchar(512)
)

--获取登录账号在当前数据库的所有database role
;with tmp
as
(
select * from sys.database_role_members 
where member_principal_id = @db_principal_id
union all
select rm.* from sys.database_role_members rm
inner join tmp 
on rm.member_principal_id = tmp.role_principal_id
)
insert into #tmp_db_role 
select a.member_principal_id, b.name,
       a.role_principal_id, c.name
 from tmp a
inner join sys.database_principals b
on a.member_principal_id = b.principal_id
inner join sys.database_principals c
on a.role_principal_id = c.principal_id

--登录账号在当前数据库的自身对象权限(OBJECT_OR_COLUMN)
select a.principal_id as member_principal_id, a.name as member_principal_name,
       null as role_principal_id, null as role_principal_name,
       o.name as major_name, c.name as minor_name,
       b.permission_name, b.state_desc
from sys.database_principals a
inner join sys.database_permissions b
on a.principal_id = b.grantee_principal_id
left join sys.objects o
on b.major_id = o.object_id
left join sys.columns c 
on (b.major_id = c.object_id and b.minor_id = c.column_id)
where a.principal_id = @db_principal_id
and b.class_desc = 'OBJECT_OR_COLUMN'
union all
--登录账号在当前数据库的自身对象权限(SCHEMA)
select a.principal_id as member_principal_id, a.name as member_principal_name,
       null as role_principal_id, null as role_principal_name,
       s.name as major_name, null as minor_name,
       b.permission_name, b.state_desc
from sys.database_principals a
inner join sys.database_permissions b
on a.principal_id = b.grantee_principal_id
left join sys.schemas s
on b.major_id = s.schema_id
where a.principal_id = @db_principal_id
and b.class_desc = 'SCHEMA'
union all
--database role的对象权限(OBJECT_OR_COLUMN)
select a.member_principal_id, a.member_principal_name,
       a.role_principal_id, a.role_principal_name,
       o.name as major_name, c.name as minor_name,
       b.permission_name, b.state_desc
from #tmp_db_role a
inner join sys.database_permissions b --inner join, 仅自定义的database role
on a.role_principal_id = b.grantee_principal_id
left join sys.objects o
on b.major_id = o.object_id
left join sys.columns c 
on (b.major_id = c.object_id and b.minor_id = c.column_id)
where b.class_desc = 'OBJECT_OR_COLUMN'
union all
--database role的对象权限(SCHEMA)
select a.member_principal_id, a.member_principal_name,
       a.role_principal_id, a.role_principal_name,
       s.name as major_name, null as minor_name,
       b.permission_name, b.state_desc
from #tmp_db_role a
inner join sys.database_permissions b --inner join, 仅自定义的database role
on a.role_principal_id = b.grantee_principal_id
left join sys.schemas s
on b.major_id = s.schema_id
where b.class_desc = 'SCHEMA'
/*
union all
--public role有一些系统视图的select权限，可以忽略
select a.principal_id as member_principal_id, a.name as member_principal_name,
       null as role_principal_id, null as role_principal_name,
       o.name as major_name, c.name as minor_name,
       b.permission_name, b.state_desc
from sys.database_principals a
inner join sys.database_permissions b
on a.principal_id = b.grantee_principal_id
left join sys.all_objects o
on b.major_id = o.object_id
left join sys.all_columns c 
on (b.major_id = c.object_id and b.minor_id = c.column_id)
where a.name = 'public'
*/

注意：如果对象的权限是通过role衍生的，而不是直接分配给user或者role，那么并不会被列出来。试想sysadmin 的角色，难道要列出所有数据库的所有对象吗？

三. 查看自己的权限

1. 有没有登录权限

登录失败并不一定是没权限，还是找别人来检查自己账号的登录权限吧；

【5.7】 有没有数据库访问权限

--列出所有可访问的数据库
SELECT *
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1

【5.8】有没有对象访问权限

用上面提到HAS_PERMS_BY_NAME函数，它可以检查当前账号的各种权限；

SELECT HAS_PERMS_BY_NAME('test_sp', 'Object' , 'Execute')
SELECT HAS_PERMS_BY_NAME('test', 'Database' , 'Execute')

【5.9】有哪些权限

--实例级权限
SELECT * FROM fn_my_permissions(NULL, 'SERVER'); 
--数据库级权限
SELECT * FROM fn_my_permissions ('DBA', 'DATABASE'); 
--对象权限，只能一个个对象检查，不能一次返回所有对象权限，和HAS_PERMS_BY_NAME类似
SELECT * FROM fn_my_permissions ('test_grant', 'OBJECT'); 

 

 用于检查自己权限的方法，同样也可以检查其他账号，用EXECUTE AS切换账号即可。

【参考文档】

每个权限的概念：https://docs.microsoft.com/zh-cn/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15

grant各个类别的操作：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/grant-server-permissions-transact-sql?view=sql-server-ver15

revoke各个类别的操作：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/revoke-transact-sql?view=sql-server-ver15

参考：http://blog.51cto.com/jimshu/1176573

更细致的大佬博客（必看）：https://www.cnblogs.com/chenmh/p/4080420.html

---------------------------------begin copy grant info -----------------use master;if object_id('copy_privileges') is not nulldrop proc copy_privilegesgo
create proc copy_privileges@olddb nvarchar(255),@newdb nvarchar(255)asbegin-- get privileges infoDECLARE @privileges Table ( dbname varchar(255), username varchar(255), rolename  varchar(255), rn int)insert into @privilegesexec('select db_name() as dbname,dp.name as username,dpr.name as rolename ,row_number() over(order by dp.name) as rnfrom '+@olddb+'.sys.database_principals dp join '+@olddb+'.sys.database_role_members drm on drm.member_principal_id=dp.principal_idjoin '+@olddb+'.sys.database_principals dpr on drm.role_principal_id=dpr.principal_id join '+@olddb+'.sys.server_principals sp on sp.name=dp.namewhere 1=1and dpr.is_fixed_role=1  and dp.type<>''R''and dp.type in(''S'',''U'',''G'') --SQL USER,WINDOWS USER AND windows grouporder by username,rolename')
-- maindeclare @rn int,@rn_count intdeclare @sql nvarchar(max)declare @loginName nvarchar(200),@roleName nvarchar(200)
set @sql=N''set @rn=1select @rn_count=count(1) from @privileges
while @rn<=@rn_countbeginselect @loginName=username,@roleName=rolename from @privileges where rn=@rnset @sql=@sql+N'use '+@newdb+';if not exists(select * from '+@newdb+'.sys.database_principals where name='''+@loginName+''') beginCREATE USER '+QUOTENAME(@LoginName)+' for login '+quotename(@loginName)+';end'    select @sql=@sql+N'use '+@newdb+';exec sp_addrolemember '+@roleName+', '+@LoginName+''set @rn=@rn+1endprint @sqlexec sp_executesql @sqlend
go-- run procuse master;goDECLARE @sql VARCHAR(300),@db_name VARCHAR(500)DECLARE @olddb VARCHAR(500),@newdb VARCHAR(500)SET @newdb='QPRecordDB'select @olddb=@db_name+'_'+convert(varchar(100),getdate(),12)exec copy_privileges @olddb,@newdb
godrop proc copy_privileges---------------------------------end copy grant info -----------------