SQL Server查看login所授予的具体权限
程序员文章站
2023-11-18 11:06:16
在SQL Server数据库中如何查看一个登录名(login)的具体权限呢，如果使用SSMS的UI界面查看登录名的具体权限的话，用户数据库非常多的话，要梳理完它所有的权限，操作又耗时又麻烦，个人十分崇尚简洁、高效的方法，反感那些需要大量手工操作的UI界面操作方式，哪怕就是脚本，如果不能一次搞定，手工... ......
在sql server数据库中如何查看一个登录名(login)的具体权限呢，如果使用ssms的ui界面查看登录名的具体权限的话，用户数据库非常多的话，要梳理完它所有的权限，操作又耗时又麻烦，个人十分崇尚简洁、高效的方法，反感那些需要大量手工操作的ui界面操作方式，哪怕就是脚本，如果不能一次搞定，手工多操作几次(例如，切换数据库)，都是不可接受的。最近遇到这个需求，就完善了一下之前的脚本get_login_rights_script.sql，输入登录名参数，将这个登录名所拥有的服务器角色、数据库角色、以及所授予具体对象的相关权限使用脚本查询出来，脚本分享如下：
--==================================================================================================================
-- scriptname : get_login_rights_script.sql
-- author : 潇湘隐者
-- createdate : 2015-12-18
-- description : 查看某个登录名被授予的数据库对象的权限的脚本（授权脚本和回收权限脚本）
-- note :
/******************************************************************************************************************
parameters : 参数说明
********************************************************************************************************************
@login_name : 你要查看权限的登录名(需要输入替换的参数)
********************************************************************************************************************
modified date modified user version modified reason
********************************************************************************************************************
2018-08-03 潇湘隐者 v01.00.00 新建该脚本。
2019-04-04 潇湘隐者 v01.01.00 fix掉一个bug，某个表只允许更新某个字段，但是这里显示更新整个表。
2019-09-25 潇湘隐者 v01.02.00 解决只能查看某个用户数据库，不能查看所有数据库的权限问题。
2019-09-25 潇湘隐者 v01.03.00 解决数据库名包含中划线[-], 出现下面错误问题
-------------------------------------------------------------------------------------------------------------------
msg 911, level 16, state 1, line 1
database 'xxxx' does not exist. make sure that the name is entered correctly.
-------------------------------------------------------------------------------------------------------------------
2019-09-26 潇湘隐者 v01.04.00 解决系统表和系统视图大小写问题（排序规则区分大小时，会报错）
2019-09-26 潇湘隐者 v01.04.00 加入数据库角色详细信息
*******************************************************************************************************************/
declare @login_name nvarchar(32)= 'test1';
declare @database_name nvarchar(64);
declare @cmdtext nvarchar(max);
if object_id('tempdb.dbo.#databases') is not null
drop table dbo.#databases;
create table #databases
(
database_id int,
database_name sysname
);
if object_id('tempdb.dbo.#user_db_roles') is not null
drop table dbo.#user_db_roles;
create table dbo.#user_db_roles
(
[db_name] nvarchar(64)
,[user_name] nvarchar(64)
,[role_name] nvarchar(64)
,[principal_type_desc] nvarchar(64)
,[class_desc] nvarchar(64)
,[permission_name] nvarchar(64)
,[object_name] nvarchar(128)
,[permission_state_desc] nvarchar(128)
);
if object_id('tempdb.dbo.#user_object_rights') is not null
drop table dbo.#user_object_rights;
create table dbo.#user_object_rights
(
[database_name] nvarchar(128),
[schema_name] nvarchar(64),
[object_name] nvarchar(128),
[user_name] nvarchar(32),
[permissions_type] char(12),
[permission_name] nvarchar(128),
[permission_state] nvarchar(64),
[class_desc] nvarchar(64),
[column_name] nvarchar(32),
[state_desc] nvarchar(64),
[grant_stmt] nvarchar(max),
[revoke_stmt] nvarchar(max)
)
insert into #databases
select database_id ,
name
from sys.databases
where name not in ('model') and state = 0; --state_desc=online
--登录名授予的服务器角色
select username = u.name ,
serverrole = g.name ,
type = u.type,
type_desc = u.type_desc,
create_date = u.create_date,
modify_date = u.modify_date,
denylogin = l.denylogin
from sys.server_role_members m
inner join sys.server_principals g on g.principal_id = m.role_principal_id
inner join sys.server_principals u on u.principal_id = m.member_principal_id
inner join sys.syslogins l on u.name = l.name
where l.name=@login_name
order by u.name,g.name;
while 1= 1
begin
select top 1 @database_name= database_name
from #databases
order by database_id;
if @@rowcount =0
break;
set @cmdtext = n'use ' + quotename(@database_name) + n';' +char(10)
--登录名授予的数据库角色
/********************************************************************************
select @cmdtext += n'insert into #user_db_roles
select db_name() as [db_name]
,m.name as [user_name]
,r.name as [role_name]
from sys.database_role_members rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals m on rm.member_principal_id = m.principal_id
where m.name=@p_login_name' + char(10);
exec sp_executesql @cmdtext, n'@p_login_name nvarchar(32)',@p_login_name=@login_name;
***********************************************************************************/
select @cmdtext += n'insert into #user_db_roles
select db_name() as [db_name] ,
u.name as [user_name] ,
r.name as [role_name] ,
t.[principal_type_desc] ,
t.[class_desc] ,
t.[permission_name] ,
t.[object_name] ,
t.permission_state_desc
from sys.database_role_members as m
inner join sys.database_principals as r on r.principal_id = m.role_principal_id
inner join sys.database_principals as u on u.principal_id = m.member_principal_id
left join ( select user_name(p.grantee_principal_id) as principal_name ,
dp.type_desc as principal_type_desc ,
p.class_desc as class_desc ,
p.permission_name as [permission_name] ,
object_name(p.major_id) as [object_name] ,
p.state_desc as [permission_state_desc]
from sys.database_permissions p
inner join sys.database_principals dp on p.grantee_principal_id = dp.principal_id
) t on t.principal_name = r.name
where u.name = @p_login_name;' + char(10);
exec sp_executesql @cmdtext, n'@p_login_name nvarchar(32)',@p_login_name=@login_name;
set @cmdtext = n'use ' +quotename(@database_name) + n';' +char(10);
--查看具体对象的授权问题
select @cmdtext +=n'insert into dbo.#user_object_rights
( [database_name] ,
[schema_name] ,
[object_name] ,
[user_name] ,
[permissions_type] ,
[permission_name] ,
[permission_state] ,
[class_desc] ,
[column_name] ,
[state_desc] ,
[grant_stmt] ,
[revoke_stmt]
)
select db_name() as [database_name]
, sys.schemas.name as [schema_name]
, ob.name as [object_name]
, sys.database_principals.name as [user_name]
, dp.type as [permissions_type]
, dp.permission_name as [permission_name]
, dp.state as [permission_state]
, dp.class_desc as [class_desc]
, sc.name as [column_name]
, dp.state_desc as [state_desc]
, dp.state_desc + '' '' + dp.permission_name + '' on [''+ sys.schemas.name + ''].['' + ob.name + ''] to ['' + sys.database_principals.name + ''];'' collate latin1_general_ci_as
as [grant_stmt]
, ''revoke '' + dp.permission_name + '' on [''+ sys.schemas.name + ''].['' + ob.name + ''] from ['' + sys.database_principals.name + ''];'' collate latin1_general_ci_as
as [revoke_stmt]
from sys.database_permissions dp
left outer join sys.objects ob on dp.major_id = ob.object_id
left outer join sys.schemas on ob.schema_id = sys.schemas.schema_id
left outer join sys.database_principals on dp.grantee_principal_id = sys.database_principals.principal_id
left outer join sys.columns sc on ob.object_id = sc.object_id and sc.column_id = dp.minor_id
where sys.database_principals.name =@p_login_name
order by permissions_type;'
--print(@cmdtext);
exec sp_executesql @cmdtext, n'@p_login_name nvarchar(32)',@p_login_name=@login_name;
delete from #databases where database_name=@database_name;
end
select * from tempdb.dbo.#user_db_roles;
select * from tempdb.dbo.#user_object_rights;
if object_id('tempdb.dbo.#databases') is not null
drop table dbo.#databases;
if object_id('tempdb.dbo.#user_db_roles') is not null
drop table dbo.#user_db_roles;
if object_id('tempdb.dbo.#user_object_rights') is not null
drop table dbo.#user_object_rights;
上一篇： Nginx的优点