sql server 管理日志可以删除吗(SQL Server查看login所授予的具体权限问题)
类别:数据库 浏览量:1462
时间:2021-10-14 00:47:21 sql server 管理日志可以删除吗
SQL Server查看login所授予的具体权限问题在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. ------------------------------------------------------------------------------------------------------------------- *******************************************************************************************************************/ 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 #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; 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 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;
总结
以上所述是小编给大家介绍的SQL Server查看login所授予的具体权限问题,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对开心学习网网站的支持!
如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!
您可能感兴趣
- sql join速度慢(SQL Server 使用join all优化 or 查询速度)
- mysqltimestamp如何比较(为什么MySQL 使用timestamp可以无视时区问题.)
- mysql常见错误分析(分析MySQL抛出异常的几种常见解决方式)
- mysql order by id查询变慢(MySQL不使用order by实现排名的三种思路总结)
- 启动sqlserver代理服务失败(SQL Server代理服务无法启动怎么办)
- vs连接sql语句(vs code连接sql server数据库步骤及遇到的问题小结)
- sqlserver连接服务器怎么解决(Sql server端口未打开连接不上的解决方案)
- SQL Server将数据导出到SQL脚本文件
- c#中SqlBulkCopy的使用
- mysqlnull所占空间(详解mysql三值逻辑与NULL)
- sqlserver2012海量数据查询(SQL Server 2012 多表连接查询功能实例代码)
- sqlserver查询信息表(SQL Server中Table字典数据的查询SQL示例代码)
- sqlserver复制表结构及数据到新表(SQL SERVER 表与表之间 字段一对多sql语句写法)
- sqlserver日志被删除(sql server日志处理不当造成的隐患详解)
- sql语句groupby分组后怎么显示(SQL group by去重复且按照其他字段排序的操作)
- dockermysql配置详解(Docker 部署Mysql 服务和Redis 服务的方法)
- 赵丽颖第一部当女主的戏,主角配角个个都是实力演员(赵丽颖第一部当女主的戏)
- 乾隆为何这么喜爱白塔原因是什么(乾隆为何这么喜爱白塔原因是什么)
- 逐渐消失的东北八大怪现象,进步的社会里我们遗失的是什么(逐渐消失的东北八大怪现象)
- 今日大雪,大雪养生这样做,一年都有好气色(大雪养生这样做)
- 粗盐是什么 粗盐的功效与作用(粗盐的功效与作用)
- 会 吃人 的客机 从天堂到地狱只需5分钟,图-104如何做到(会吃人的客机)
热门推荐
- mysql 性能调优技巧(4 款 MySQL 调优工具,公司大神都在用!)
- laravel数据表配置(laravel实现按月或天或小时统计mysql数据的方法)
- lnmp常见问题(LAMP和LNMP,你更愿意选择谁,为什么?)
- dedecms增加导航内容(Dedecms搜索时 当然关键词和栏目名相同时 搜索结果为空的问题的解决方法)
- pythonflask怎么设置(python flask安装和命令详解)
- sql server 2019 资源不足不可信(SQL Server异常代码处理的深入讲解)
- vue时间转换(vue如何动态实时的显示时间浅析)
- extjs中treepanel属性和方法
- 如何用tomcat部署web项目(一文搞懂如何部署启动多个tomcat并移植项目)
- MySQL中ROUND四舍五入函数需要注意的地方
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9