SQL Server 2008 – Retrieving a Users’ Privileges for all tables in a databse

I recently answered a question on StackOverflow.com by a user who needed to retrieve a list of a given users Privileges for each Table in a given Database.

After some trial an error I ended up with the following T-SQL Script;

declare @Proc nvarchar(50)

declare @RowCnt int

declare @MaxRows int

declare @ExecSql nvarchar(255)

select @RowCnt = 1

select @Proc = 'SELECT * from fn_my_permissions'

declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , TableName varchar(50))

insert into @Import (TableName) select name from sys.Tables

declare @Output table (entity_name varchar(50), subentity_name varchar(50), permission_name varchar(50))

select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows

begin

    select @ExecSql = @Proc + '(N''' + TableName + ''', N''OBJECT'') where subentity_name = ''''' from @Import where rownum = @RowCnt

    insert into @Output exec sp_executesql @ExecSql

    Select @RowCnt = @RowCnt + 1

end

select * from @Output

This will give the Permissions for the current user. If you want to find the permissions for a given user then use the following script;

EXECUTE AS LOGIN = N'username'

GO

declare @Proc nvarchar(50)

declare @RowCnt int

declare @MaxRows int

declare @ExecSql nvarchar(255)

select @RowCnt = 1

select @Proc = 'SELECT * from fn_my_permissions'

declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , TableName varchar(50))

insert into @Import (TableName) select name from sys.Tables

declare @Output table (entity_name varchar(50), subentity_name varchar(50), permission_name varchar(50))

select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows

begin

    select @ExecSql = @Proc + '(N''' + TableName + ''', N''OBJECT'') where subentity_name = ''''' from @Import where rownum = @RowCnt

    insert into @Output exec sp_executesql @ExecSql

    Select @RowCnt = @RowCnt + 1

end

select * from @Output

GO

REVERT

GO

These scripts are based on;

fn_my_permissions procedure:

This returns a list of permissions for the current user, for the given table in a database. Here we are executing the following code for each table in the database;

SELECT * from fn_my_permissions(N'tablename', N'OBJECT') where subentity_name = ''

More information can be found at http://msdn.microsoft.com/en-us/library/ms176097.aspx and http://books.google.co.uk/books?id=5_AEiJXbyiEC&pg=PA452&lpg=PA452&dq=tsql+HAS_perms_by_name+for+each+table&source=bl&ots=tSArrzcfa4&sig=HmPsF9vEFQC4g2hC8lWvCmJ8qao&hl=en&sa=X&ei=Nrr5UKvkCKLB0gXhtYD4Ag&ved=0CEEQ6AEwAg#v=onepage&q=tsql%20HAS_perms_by_name%20for%20each%20table&f=false and http://stackoverflow.com/a/497368/1305169

The sys.tables collection:

This contains a list of tables for the given database. More information can be found at http://blog.sqlauthority.com/2007/06/26/sql-server-2005-list-all-tables-of-database/

A Simple Loop:

In order to loop through all of the tables, I modified a handy script I found here; http://weblogs.aspnet05.orcsweb.com/jgalloway/archive/2006/04/12/442618.aspx

About the Author: Peter Gallagher

Leave A Comment Cancel reply