SQL Server查看login所授予的具体权限问题
在SQLServer数据库中如何查看一个登录名(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:你要查看权限的登录名(需要输入替换的参数)
********************************************************************************************************************
ModifiedDateModifiedUserVersionModifiedReason
********************************************************************************************************************
2018-08-03潇湘隐者V01.00.00新建该脚本。
2019-04-04潇湘隐者V01.01.00Fix掉一个bug，某个表只允许更新某个字段，但是这里显示更新整个表。
2019-09-25潇湘隐者V01.02.00解决只能查看某个用户数据库，不能查看所有数据库的权限问题。
2019-09-25潇湘隐者V01.03.00解决数据库名包含中划线[-],出现下面错误问题
-------------------------------------------------------------------------------------------------------------------
Msg911,Level16,State1,Line1
Database'xxxx'doesnotexist.Makesurethatthenameisenteredcorrectly.
-------------------------------------------------------------------------------------------------------------------
*******************************************************************************************************************/
DECLARE@login_nameNVARCHAR(32)='test1';
DECLARE@database_nameNVARCHAR(64);
DECLARE@cmdTextNVARCHAR(MAX);
IFOBJECT_ID('TempDB.dbo.#databases')ISNOTNULL
DROPTABLEdbo.#databases;
CREATETABLE#databases
(
database_idINT,
database_namesysname
);
IFOBJECT_ID('tempdb.dbo.#user_db_roles')ISNOTNULL
DROPTABLEdbo.#user_db_roles;
CREATETABLEdbo.#user_db_roles
(
[DB_NAME]NVARCHAR(64)
,[USER_NAME]NVARCHAR(64)
,[ROLE_NAME]NVARCHAR(64)
);
IFOBJECT_ID('tempdb.dbo.#user_object_rights')ISNOTNULL
DROPTABLEdbo.#user_object_rights;
CREATETABLEdbo.#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)
)
INSERTINTO#databases
SELECTdatabase_id,
name
FROMsys.databases
WHEREnameNOTIN('model')ANDstate=0;--state_desc=ONLINE
--登录名授予的服务器角色
SELECTUserName=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
FROMsys.server_role_membersm
INNERJOINsys.server_principalsgONg.principal_id=m.role_principal_id
INNERJOINsys.server_principalsuONu.principal_id=m.member_principal_id
INNERJOINsys.sysloginslONu.name=l.name
WHEREl.name=@login_name
ORDERBYu.name,g.name;
WHILE1=1
BEGIN
SELECTTOP1@database_name=database_name
FROM#databases
ORDERBYdatabase_id;
IF@@ROWCOUNT=0
BREAK;
SET@cmdText=N'USE'+QUOTENAME(@database_name)+N';'+CHAR(10)
--登录名授予的数据库角色
SELECT@cmdText+=N'INSERTINTO#user_db_roles
SELECTDB_NAME()AS[DB_NAME]
,M.NAMEAS[USER_NAME]
,R.NAMEAS[ROLE_NAME]
FROMsys.DATABASE_ROLE_MEMBERSRM
INNERJOINsys.DATABASE_PRINCIPALSRONRM.ROLE_PRINCIPAL_ID=R.PRINCIPAL_ID
INNERJOINsys.DATABASE_PRINCIPALSMONRM.MEMBER_PRINCIPAL_ID=M.PRINCIPAL_ID
WHEREM.NAME=@p_login_name'+CHAR(10);
EXECSP_EXECUTESQL@cmdText,N'@p_login_nameNVARCHAR(32)',@p_login_name=@login_name;
SET@cmdText=N'USE'+QUOTENAME(@database_name)+N';'+CHAR(10);
--查看具体对象的授权问题
SELECT@cmdText+=N'INSERTINTOdbo.#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]
)
SELECTDB_NAME()AS[DATABASE_NAME]
,SYS.SCHEMAS.NAMEAS[SCHEMA_NAME]
,ob.NAMEAS[OBJECT_NAME]
,SYS.DATABASE_PRINCIPALS.NAMEAS[USER_NAME]
,dp.TYPEAS[PERMISSIONS_TYPE]
,dp.PERMISSION_NAMEAS[PERMISSION_NAME]
,dp.STATEAS[PERMISSION_STATE]
,dp.CLASS_DESCAS[CLASS_DESC]
,sc.nameAS[COLUMN_NAME]
,dp.STATE_DESCAS[STATE_DESC]
,dp.STATE_DESC+''''+dp.PERMISSION_NAME+''ON[''+SYS.SCHEMAS.NAME+''].[''+ob.NAME+'']TO[''+SYS.DATABASE_PRINCIPALS.NAME+''];''COLLATELATIN1_GENERAL_CI_AS
AS[GRANT_STMT]
,''REVOKE''+dp.PERMISSION_NAME+''ON[''+SYS.SCHEMAS.NAME+''].[''+ob.NAME+'']FROM[''+SYS.DATABASE_PRINCIPALS.NAME+''];''COLLATELATIN1_GENERAL_CI_AS
AS[REVOKE_STMT]
FROMSYS.DATABASE_PERMISSIONSdp
LEFTOUTERJOINSYS.OBJECTSobONdp.MAJOR_ID=ob.OBJECT_ID
LEFTOUTERJOINSYS.SCHEMASONob.SCHEMA_ID=SYS.SCHEMAS.SCHEMA_ID
LEFTOUTERJOINSYS.DATABASE_PRINCIPALSONdp.GRANTEE_PRINCIPAL_ID=SYS.DATABASE_PRINCIPALS.PRINCIPAL_ID
LEFTOUTERJOINSYS.columnsscONob.object_id=sc.object_idANDsc.column_id=dp.minor_id
WHERESYS.DATABASE_PRINCIPALS.NAME=@p_login_name
ORDERBYPERMISSIONS_TYPE;'
PRINT(@cmdText);
EXECSP_EXECUTESQL@cmdText,N'@p_login_nameNVARCHAR(32)',@p_login_name=@login_name;
DELETEFROM#databasesWHEREdatabase_name=@database_name;
END
SELECT*FROMtempdb.dbo.#user_db_roles;
SELECT*FROMdbo.#user_object_rights;
IFOBJECT_ID('TempDB.dbo.#databases')ISNOTNULL
DROPTABLEdbo.#databases;
IFOBJECT_ID('tempdb.dbo.#user_db_roles')ISNOTNULL
DROPTABLEdbo.#user_db_roles;
IFOBJECT_ID('tempdb.dbo.#user_object_rights')ISNOTNULL
DROPTABLEdbo.#user_object_rights;
总结
以上所述是小编给大家介绍的SQLServer查看login所授予的具体权限问题,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！