(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
常用权限:
【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
五、登录名与角色关系查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--创建一个简单的登录,登录名为: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】查看实例级的权限
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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】查看数据库级的权限
仅列出数据库级别的权限,具体的对象名称并没有列出。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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对象权限。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--建立测试用的架构,对象,列 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切换账号即可。
【参考文档】
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 -----------------