How to list a user's security/rights?

[PTW]

We are being audited. One of the requests was a some sort of evidence that specific users have only certain levels of access to several databases.

Short of taking about 50 screenshots per user in Enterprise Manager, is there any way I can get a list or report to indicate:

1. Server Roles (System Admin, Security Admin, etc)
2. Database Access Permissions (Public, db_owner, db_datareader, etc)
3. Permission on individual objects (Select, delete, execute, etc)

Thanks!

 

[Catadmin]

sp_HelpRoleMember, sp_helpLogins, sp_helprotect, sp_helpsrvrolemember are a few off the top of my head. Look these up in Books Online.

Hope this helps.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[Catadmin]

Sorry, I should have explained more clearly. I'm looking at Stored Procedures, which can be run in Query Analyzer. You can then cut-n-paste the results to Excel or Word, or you should be able to save them to a file.

Look up SP_ in BOL (Books Online) to see a list of all the system stored procedures. Look up the ones I listed above for more details on how they work and what they show. Still haven't found one that shows object permissions, but this should get you started.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[Catadmin]

Found it. Well, not a stored Procedure, but I found what you want. Look in Books Online, Information Schema Views.

The first one you'll want is INFORMATION_SCHEMA.COLUMN_PRIVILEGES, then INFORMATION_SCHEMA.TABLE_PRIVILEGES. Make sure you read BOL so you know how to read and use these properly.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[PTW]

Thank-you very much! I had found info for the sp_HelpLogins, but the INFORMATION_SCHEMA info is more of what I need.