Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to see if there are any changes on the permissions granted for a specific table.

For example, I don't want to have the login 'John' have the SELECT permission on the table Invoices.

If this permission is somehow GRANTed to John, I want to know about it. I'd like to know who has granted him the permission and when.

I'm using SQL Server 2012 Enterprise Edition.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

Since you're using SQL 2012, you can leverage SQL Server Auditing, which was introduced with SQL 2008. SQL Server Auditing can be used to track a multitude of actions within SQL Server, include database object permission GRANT/REVOKE/DENY actions. You'll be able to see the security principal that executed the statement, when the statement was run, and what that exact statement was. To create your audit, you will need to perform the following:

Create an Audit

The Audit defines the over-arching logging for all the audits. To create an Audit, you can use the object explorer to browse under the instance to Security->Audits. Right click on Audits and select "New Audit". Fill out the next dialog (the important bit here is where you store the audit file, the rest you can use defaults on). After you create it, you will need to right click on the Audit in the object browser and enable it.

Create an Audit Specification

The Specification defines what action you want to track, using any combination of actions/groups to define the Specification. There are many different actions, both on the server and database level, that can be tracked. To create a Specification for database permission changes, browse under the database you wish to track in to Security->Database Audit Specifications. Right click and fill out the dialog. Select the Audit you created above to write to. Under Audit Action Type, select SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP from the drop down. This action allows you to track permissions changes on schema objects. Once you have this, click 'Ok' to create the specification. Then, in the object browser, enable it.

Viewing the audit

Once this is all complete, the audit is running and will capture and GRANT/REVOKE/DENY actions in that database. To view the audit logs, browse to the audit under the instance in Security->Audits. Right click on the audit and select 'View Audit Logs' and you will be able to see all the audit actions that have occurred in your database since you started the auditing.

As an alternative, your auditing can be enabled using T-SQL with the following commands:

--Create Audit
USE [master];

CREATE SERVER AUDIT [PermissionTracking]
TO FILE 
(   FILEPATH = N'C:\DBData'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
);

--Enable Audit
ALTER SERVER AUDIT [PermissionTracking] WITH (STATE=ON);

--Create Database Audit Specification
USE [foo];

CREATE DATABASE AUDIT SPECIFICATION [FooDBPermissions]
FOR SERVER AUDIT [PermissionTracking]
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE=ON);
share|improve this answer
add comment

You can run the following to get a listing of permissions that have been granted.

select permission_name, 
       state_desc, 
       type_desc, 
       U.name GranteeName, 
       U2.name GrantorName, 
       OBJECT_NAME(major_id) 
  from sys.database_permissions P 
       JOIN sys.tables T ON P.major_id = T.object_id 
       JOIN sysusers U ON U.uid = P.grantee_principal_id
       JOIN sysusers U2 ON U2.uid = P.grantor_principal_id
share|improve this answer
    
Lumpy, thanks, but I get the Grantor name to be 'dbo', and I would like to see a SQL login instead –  user21163 Mar 12 '13 at 13:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.