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
Leave A Comment
You must be logged in to post a comment.