在SQL Server数据库中如何查看一个登录名(登录)的具体权限呢,如果使用ssm的UI界面查看登录名的具体权限的话,用户数据库非常多的话,要梳理完它所有的权限,操作又耗时又麻烦,个人十分崇尚简洁,高效的方法,反感那些需要大量手工操作的UI界面操作方式,哪怕就是脚本,如果不能一次搞的定,手工多操作几次(例如,切换数据库),都是不可接受的。最近遇到这个需求,就完善了一下之前的脚本get_login_rights_script.sql,输入登录名参数,将这个登录名所拥有的服务器角色,数据库角色,以及所授予具体对象的相关权限使用脚本查询出来,脚本分享如下:
——==================================================================================================================猄criptName: get_login_rights_script.sql
——作者:潇湘隐者
——CreateDate: 2015-12-18
——描述:查看某个登录名被授予的数据库对象的权限的脚本(授权脚本和回收权限脚本)
——注意:/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
参数:参数说明
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
@login_name:你要查看权限的登录名(需要输入替换的参数)
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
修改日期修改用户版本修改的原因
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
2018-08-03潇湘隐者V01.00.00新建该脚本。
2019-04-04潇湘隐者V01.01.00解决掉一个bug,某个表只允许更新某个字段,但是这里显示更新整个表。
2019-09-25潇湘隐者V01.02.00解决只能查看某个用户数据库,不能查看所有数据库的权限问题。
2019-09-25潇湘隐者V01.03.00解决数据库名包含中划线(-),出现下面错误问题
-------------------------------------------------------------------------------------------------------------------
911年味精,16级,状态1,1号线
数据库“xxxx”并不存在。确保输入正确的名称。
-------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */声明@login_name NVARCHAR (32)=皌est1”;
声明@database_name NVARCHAR (64);
声明@cmdText NVARCHAR (MAX);
如果OBJECT_ID (“TempDB.dbo。#数据库”)并不是零
删除表dbo。#数据库;
创建表#数据库
（
database_id INT,
database_name sysname
);
如果OBJECT_ID (tempdb.dbo。# user_db_roles)不是零
删除表dbo。# user_db_roles;
创建表dbo。# user_db_roles
（
[DB_NAME] NVARCHAR (64)
,USER_NAME NVARCHAR (64)
,ROLE_NAME NVARCHAR (64)
);
如果OBJECT_ID (tempdb.dbo。# user_object_rights)不是零
删除表dbo。# user_object_rights;
创建表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)
)
插入#数据库
选择database_id,
的名字
从sys.databases
名字不是(模型)和国家=0;——state_desc=在线
——登录名授予的服务器角色
选择用户名=u.name,
ServerRole=g.name,
类型=u.type,
Type_Desc=u.Type_Desc,
Create_Date=u.create_date,
Modify_Date=u.modify_date,
DenyLogin=l.denylogin
从系统。server_role_members米
内连接系统。server_principals g g。principal_id=m.role_principal_id
内连接系统。server_principals u在u。principal_id=m.member_principal_id
内连接系统。syslogins u.name=l.name l
l.name=@login_name
ORDER BY u.name g.name;
而1=1
开始
选择1 @database_name=database_name
#数据库
由database_id秩序;
如果@@ROWCOUNT=0
打破;
设置@cmdText=N 'USE ' + QUOTENAME (@database_name) + N ', ' + CHAR (10)
——登录名授予的数据库角色
选择@cmdText +=N 'INSERT # user_db_roles
选择DB_NAME () [DB_NAME]
,M。名称(USER_NAME)
,R。名称(ROLE_NAME)
从系统。DATABASE_ROLE_MEMBERS RM
内连接系统。RM DATABASE_PRINCIPALS R。ROLE_PRINCIPAL_ID=R.PRINCIPAL_ID
内连接系统。RM DATABASE_PRINCIPALS M。MEMBER_PRINCIPAL_ID=M.PRINCIPAL_ID
米的地方。NAME=@p_login_name ' + CHAR (10);
EXEC SP_EXECUTESQL @cmdText N ' @p_login_name NVARCHAR (32), @p_login_name=@login_name;
设置@cmdText=N 'USE ' + QUOTENAME (@database_name) + N”;“+ CHAR (10);
——查看具体对象的授权问题
选择@cmdText +=N 'INSERT 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)
)
选择DB_NAME () [DATABASE_NAME]
,SYS.SCHEMAS。名称(SCHEMA_NAME)
,ob.NAME [OBJECT_NAME]
,SYS.DATABASE_PRINCIPALS。名称(USER_NAME)
dp。类型(PERMISSIONS_TYPE)
dp。PERMISSION_NAME作为[PERMISSION_NAME]
dp。国家(PERMISSION_STATE)
dp。CLASS_DESC作为[CLASS_DESC]
,sc.name (COLUMN_NAME)
dp。STATE_DESC作为[STATE_DESC]
dp。STATE_DESC + " " + dp。[" + SYS.SCHEMAS PERMISSION_NAME +”。null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
