October 19, 2004 at 2:41 pm
What I need is a report or spreadsheet that I can hand over to auditors that shows every permission that every user has on every object in every database.
Does anyone have a comprehensive script/report that would meet these objectives...
I have several servers with many databases on each one, so to keep doing this by cut and paste is a real pain...
Even if there was a third party product that would generate such a report, that would work, I just haven't found one.
October 19, 2004 at 3:05 pm
Run a query that will use sp_MSforeachDB stored procedure that will execute SQL statement for each database. The statement that you want to execute will be 'Select * from syspermissions' I was oK running this on master, but my workstation's memory was not enough when I tried to select &* from syspermissions on the production database. Moreover, I would recommend to join syspermissions with sysusers and sysobjects because there are only id numbers in syspermissions and auditors would like to see real names. Otherwise the syntax is:
EXEC sp_MSforeachdb 'select * from syspermission'
Regards,Yelena Varsha
October 20, 2004 at 6:16 am
Try this script it will tell you not only what the users have permissions on but what user defined roles they may be getting it thru.
http://www.sqlservercentral.com/scripts/contributions/268.asp
It doesn't give system roles but you can get that from one of the SQL built in SPs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply