SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


report needed for auditors...


report needed for auditors...

Author
Message
Gonzo
Gonzo
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 814

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.





Yelena Varshal
Yelena Varshal
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4582 Visits: 595

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 Varshal

Antares686
Antares686
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11460 Visits: 780

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.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search