数据库访问权限 [英] Database Access Rights

问题描述

我想找到谁给了数据库中某些用户的只读权限。何时获得访问权限。有可能找到它吗？如果是，请提供任何示例查询

version：sqlserver 2016

---

loving dotnet

解决方案

如果还不太晚且默认跟踪尚未滚动，则可以运行以下查询。以下脚本查询默认跟踪，并获取有关谁授予访问权限以及何时访问的结果。 

来源：  http://www.sqlservercentral.com/articles/Permissions/130850/

  - -add / remove根据需要删除过滤器/事件
 
 DECLARE @tracefile VARCHAR（500）
  - 获取默认跟踪文件的路径
 SELECT @tracefile = CAST（value AS VARCHAR（ 500））
 FROM :: fn_trace_getinfo（DEFAULT）
 WHERE traceid = 1 
 AND property = 2 
 
  - 从默认跟踪中获取安全性更改
 SELECT * 
 FROM :: fn_trace_gettable（@ tracefile，DEFAULT）trcdata  -  DEFAULT表示将读取所有跟踪文件
 INNER JOIN sys.trace_events evt ON trcdata.EventClass = evt.trace_event_id 
 WHERE trcdata.EventClass IN（102,103,104,105,106,108,109,110,111）
 ORDER BY trcdata.StartTime 
 --trcdata.DatabaseID 
 --trcdata.TargetLoginName 

Hi,
I am tying to find who have given read only access to some user in the database. when the access given. Is it possible to find it? If yes any sample query please 

version : sqlserver 2016

---

loving dotnet

解决方案

If it's not too late and the default trace hasn't rolled over yet, you can run the following query. The following script queries the default trace and gets you the results about who granted the access and when. 

Source: http://www.sqlservercentral.com/articles/Permissions/130850/

--add/remove filters/events as needed

DECLARE @tracefile VARCHAR(500)
-- Get path of default trace file
SELECT @tracefile = CAST(value AS VARCHAR(500))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
AND property = 2

-- Get security changes from the default trace
SELECT *
 FROM ::fn_trace_gettable(@tracefile, DEFAULT) trcdata -- DEFAULT means all trace files will be read
 INNER JOIN sys.trace_events evt ON trcdata.EventClass = evt.trace_event_id
 WHERE trcdata.EventClass IN (102, 103, 104, 105, 106, 108, 109, 110, 111)
 ORDER BY trcdata.StartTime
                 --trcdata.DatabaseID
                 --trcdata.TargetLoginName

这篇关于数据库访问权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！