How do I generate a table of permissions granted t

问题:

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 :)

回答1:

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

This will work in SQL server 2000/2005/2008

回答2:

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'