Find out who has Revoked Database and Server level Permissions in SQL Server

Hi Mates,

Today in one of the what's app groups I found a question saying how to find out who has revoked Database level permissions.

It is very easy to figure out who has done that. In fact the same can be used even to find out server level permissions

In my case I have created a user by name Vamsy & given db_executor permissions on Adventure-works Database.

And I revoked the permissions by logging in as sysadmin & we can find it from default traces provided they are not over written. Below is the Query & the image for your reference.

select TextData,ApplicationName,LoginName,SPID,StartTime,DatabaseName,RoleName
from fn_trace_gettable('D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\log_15.trc',default)
where TextData is not null
order by StartTime desc