January 7, 2009 at 7:54 am
Can somebody help me out in writing the query to get list of users who have readonly permissions on a table
for example
on a Table A , X,Y, Z Has only readonly permissions and B,C has Read/Write Permissions
how do i get the Result list in T-SQL saying table name and who all have permission on the table
January 7, 2009 at 9:12 am
This will get you started. The query works as-is in SQL 2000. In SQL 2005, sysprotects, sysobjects and sysusers are views in the sys schema rather than tables.
select O.name as 'Table',
U.name as 'User',
case P.action
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 224 then 'EXECUTE'
else ' '
end as 'Permission'
from sysprotects P
join sysobjects O on P.id = O.id
join sysusers U on P.uid = U.uid and xtype = 'U'
group by O.name,U.name, P.action
order by O.name
Greg
January 7, 2009 at 10:24 pm
You will also want to look for permissions inherited through database role membership.
Users who have been added to built in database roles like db_datareader and db_datawriter will have permissions on tables. There may also have been other roles created to manage security. You can run sp_helprolemember to get more information.
Depending on how critical it is to get a complete picture of the user permissions, you might want to refine your question and repost it in the security forum. You might get more replies there.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply