欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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;