November 23, 2015 at 9:15 am
Hello,
On my SQL Server there are many logins which can manage permissions.Is there any way we can check the user/application which revoked particular object level permission?
Thanks in advance
Sree
November 23, 2015 at 1:51 pm
You should be able to query the default trace for this information, as long as you look for it before the trace file that contains the information has been rolled over. There are other options as well:
If you have EE you can create a server audit to capture this information.
You can create a DDL trigger that captures these events
You can create a server-side trace that captures these events. Although I'd just use the default trace and have a job that regularly queries the default trace for the events I'm interested in and then logs them in a table.
Here's a query that gets the events in the default trace:
SELECT DISTINCT
T.path,
TC.name AS category,
TE.trace_event_id,
TE.name AS event_name
FROM
sys.traces T
CROSS APPLY sys.fn_trace_geteventinfo(T.id) AS GEI
JOIN sys.trace_events AS TE
ON GEI.eventid = TE.trace_event_id
JOIN sys.trace_categories AS TC
ON TE.category_id = TC.category_id
WHERE
T.is_default = 1
Order By
TC.name;
Here's a query that reads the trace file(s) for the default trace:
With cteObjectTypes AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = 'ObjectType'
),
cteEventSubClasses AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = 'EventSubClass'
)
SELECT
TE.[name],
I.ApplicationName,
I.BigintData1,
I.ClientProcessID,
I.ColumnPermissions,
I.DatabaseID,
I.DatabaseName,
I.DBUserName,
I.Duration,
I.EndTime,
I.Error,
I.EventSequence,
Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
I.FileName,
I.HostName,
I.IndexID,
I.IntegerData,
I.IsSystem,
I.LineNumber,
I.LoginName,
I.LoginSid,
I.NestLevel,
I.NTDomainName,
I.NTUserName,
I.ObjectID,
I.ObjectID2,
I.ObjectName,
Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
I.OwnerName,
I.ParentName,
I.Permissions,
I.RequestID,
I.RoleName,
I.ServerName,
I.SessionLoginName,
I.Severity,
I.SPID,
I.StartTime,
I.State,
I.Success,
I.TargetLoginName,
I.TargetLoginSid,
I.TargetUserName,
I.TextData,
I.TransactionID,
I.Type,
I.XactSequence
FROM
sys.traces T CROSS Apply
sys.fn_trace_gettable(CASE WHEN PATINDEX('%Log[_]%', path)<> 0
THEN LEFT(path, PATINDEX('%Log[_]%', path) + 2) +
'.trc'
ELSE T.[path]
END, T.max_files) I JOIN
sys.trace_events AS TE ON
I.EventClass = TE.trace_event_id LEFT JOIN
cteEventSubClasses AS ESC ON
TE.trace_event_id = ESC.trace_event_id And
I.EventSubClass = ESC.subclass_value LEFT JOIN
cteObjectTypes AS OT ON
TE.trace_event_id = OT.trace_event_id AND
I.ObjectType = OT.subclass_value
WHERE
T.is_default = 1/* AND
TE.NAME = 'Audit Login GDR Event'*/
This query is tested up through 2012.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 24, 2015 at 1:27 am
Could also check sys.database_permissions and join back to sys.database_principles on the grantor to see which login did it. But if its a group then you will need to check the trace as Jack has already mentioned if it hasn't rolled over already.
November 27, 2015 at 10:13 am
Thanks Jack Corbett:-)The 2nd script helped me to find the user who dropped the permisssion:-):-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply