Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

report needed for auditors... Expand / Collapse
Author
Message
Posted Tuesday, October 19, 2004 2:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 10:30 AM
Points: 16, Visits: 578

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.




Post #142473
Posted Tuesday, October 19, 2004 3:05 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:53 AM
Points: 3,475, Visits: 584

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

Post #142478
Posted Wednesday, October 20, 2004 6:16 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, November 14, 2014 10:06 AM
Points: 8,370, Visits: 745

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. 




Post #142546
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse