澳门金沙vipSQL Server查看login所授予的具体权限问题

在SQL
Server数据库中什么查看贰个登入名(login卡塔尔(قطر‎的现实权限呢,如若接收SSMS的UI分界面查看登入名的切实权限的话,顾客数据库比相当多的话,要梳理完它具有的权柄,操作又耗时又艰辛,个人极其崇尚轻便、高效的主意,嫌恶那多少个急需一大波手工业操作的UI分界面操作方法,哪怕正是本子,假若不能够一次化解,手工多操作几遍(举个例子,切换数据库卡塔尔,都是不可选择的。近期蒙受这些供给,就到家了须臾间在此以前的台本get_login_rights_script.sql,输入登入名参数,将以此登陆名所具有的服务器剧中人物、数据库剧中人物、以至所授予具体指标的有关权限制行驶使脚本查询出来,脚本分享如下:

在SQL Server中,经常状态下,User的创导都以基于master
数据库中的Login,该Login的来自有二种:基于Windows 域顾客,Windows
域客商组,或SQL Server 验证。

--==================================================================================================================-- 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 1Database 'xxxx' does not exist. Make sure that the name is entered correctly.-------------------------------------------------------------------------------------------------------------------*******************************************************************************************************************/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));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 #databasesSELECT database_id , nameFROM sys.databasesWHERE 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.denyloginFROM 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.nameWHERE l.name=@login_nameORDER BY u.name,g.name;WHILE 1= 1BEGIN 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; 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;ENDSELECT * FROM tempdb.dbo.#user_db_roles;SELECT * FROM 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;

只顾,数据库是近日的数据库,固然用于能源管理(Resource
Governor)中的分类函数(Classifier
Function
),那么,要潜心分类函数所在的数据库,假设分类函数创制于master数据库,那么is_member函数的当前数据库便是master,检查的结果是现阶段客商是或不是归于当前数据库的组或剧中人物。

总结

潜心:只好在 master 和 tempdb
以外的数据库中实施禁用GUEST帐号的操作,那是因为大多客户都需求以GUEST身份访谈master
和 tempdb系统数据库,GUEST客户在master 和 tempdb数据库的权杖被严谨节制。

上述所述是作者给大家介绍的SQL
Server查看login所赋予的具体权限难点,希望对大家具有助于,倘若我们有此外疑问请给自家留言,笔者会及时过来我们的。在这里也极度谢谢大家对剧本之家网址的帮助!借使您感觉本文对您有帮扶,接待转发,烦请注解出处,谢谢!

Principals (Database
Engine)

在SQL
Server的哈密模型中,模拟(IMPESportageSONATE )权限的安全目的是User或Login,被付与者(grantee
)有权力模拟特定顾客的河池上下文推行一定的操作。

EXECUTE AS Clause
(Transact-SQL).aspx)

  • 登录名(Login)是Server级其他Principal,功能域是成套数据库引擎。若要连接
    SQL Server
    实例上的一定数据库,登入名必得映射到数据库客户。数据库内的权限是向数据库顾客并非登陆名给予的。
  • 数据库顾客(User)是数据库品级的Principal,功效域是数据库,登入名必需映射到数据库客商手艺连选用数据库。二个记名名能够用作差异客商映射到不相同的数据库,但在每种数据库中不能不充作一个客商进行映射。

举例,user1给与模拟user2的权柄,当user2的平安上下文有丰裕的权能,而user1未有的时候,通过权限模拟,user1能够在user2的权位上下文中施行查询央求:

前段时间数据库中,检查当前的顾客是否归于特定的Security Group:

REVOKE CONNECT FROM GUEST;

1,创建SQL Server 登录

万一创制Windows域登入,映射到三个Windows域账户,登入名必须接受Windows域格式: [<domainName>\<login_name>].

经过推行EXECUTE AS
命令模拟顾客的权力,客户user1就运营在user2的平安上下文中,举个例子,user1在登入数据库之后,模拟user2的权能:

CREATE USER user_name 
FOR LOGIN login_name 
WITH DEFAULT_SCHEMA = schema_name 

参照文档:

参数解释:

 当三个Login登入到SQL
Server之后,若无在数据库中映射相应的客商,那么该Login能够应用guest顾客登录数据库。guest顾客私下认可存在各种数据库中,暗中同意意况下,guest顾客是禁止使用的,能够由此命令启用guest客户。

SQL
Server客商GUEST的粗略介绍

2,创建Windows域登录

授予权限的思绪,能够用一句话来总结,那正是:赋予 Principal 操作
Securable 的 Permission,在大繁多文档中,把 Principal
翻译为平安珍视,Securable翻译为平安指标,Permission翻译为权力,付与权限的子句细分为三部分:Permission,Securable

Principal。最令人疑心的是Principal和Securable,这四个概念不常候会相互转变。

 

依听大人证明类型,Login分为SQL
Server登陆和Windows域登陆,在成立登陆时,能够设置暗中认可数据库(Default
Database),钦定Login登入的私下认可数据库,默许值是master数据库。

GRANT CONNECT TO GUEST
exec master..xp_logininfo 
    @acctname = 'domain\group',
    @option = 'members'

Login和User最大旨的分别是Login用于调整顾客是或不是有权力登录SQL
Server实例,User用于客商调整是不是有权力访问数据库:

Principal是被付与权限的实业,包括login、user、role等,Securable是Principal操作的靶子,能够是table、view、SP、函数等,也可以是login、user、role等对象;一时Principal会作为Securable,被Principal操纵,约等于说,Principal
是空虚意义上的固原主体,能够向安全中心授予权限,Principal分为Server
level和Database
level,最根本的多个Principal是Login和User,他们的分别是:

SQL
Server在各种数据库中都停放七个特其余顾客:dboguest,在每种数据库中都有那三个顾客(User):

二,创立数据库客商(User)

select is_member('domain\group')