How do I generate a table of permissions granted to database tables for a database user?

Question

Hi All,

I have a SQL Server 2000 database with around a couple of hundred tables. There are several SQL user accounts that can access this database but each one has different permissions granted on tables in the DB.

How do I create a script to give me a report of the permissions granted to a particular user. i.e. to generate something like:

Table      SELECT   DELETE   UPDATE    INSERT    ALTER
-----      ------   ------   ------    ------    -----
Invoices   Yes      No       Yes       Yes       No
Orders     Yes      Yes      Yes       Yes       No
Customers  Yes      No       Yes       Yes       No

and so on. It's Friday, my SQL-fu is low today and I have a million other things to do before getting finished here today and if someone had a handy script to do this already then I would be eternally grateful :)

Appreciated.
Kev

Answer 1

I am not sure if this procedure works for SQL 2000 or not, it doesn't get the exact format, but it might get you part of the way there, can you can pivot it out to get your format.

sys.sp_helprotect @Username = 'myUser'

Answer 2

Select TABLE_NAME, PRIVILEGE_TYPE from INFORMATION_SCHEMA.TABLE_PRIVILEGES where GRANTEE = @username

--this will work in sql server 2000/2005/2008