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.
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'
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.
It doesn't give system roles but you can get that from one of the SQL built in SPs.