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 ««12

List all permission in all databases Expand / Collapse
Author
Message
Posted Wednesday, May 18, 2011 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:54 AM
Points: 35, Visits: 431
Also must be SP2 or higher. That is when OBJECT_SCHEMA_NAME was introduced.
Post #1111099
Posted Thursday, September 22, 2011 8:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
Hi Alejandro

What is the purpose of

select id, xtype from master.sys.sysobjects

is sys.objects not sufficient ?

Also would it better to change the join order i.e. if we are after permissions should we be using database_permissions as the base table.This will prevent nulls being returned where database roles have no explicitly defined permissions ?
We could just use where Permission is not null I guess.

Lastly how can we get schema names to appear under the object name column ? I am seeing the following permissions ;
ALTER
CONTROL
DELETE
EXECUTE
INSERT
REFERENCES
SELECT
TAKE OWNERSHIP
UPDATE
VIEW CHANGE TRACKING
VIEW DEFINITION

with no object name. Clearly it applies to a schema but it would be useful to get the schema name.

Cheers
Preet
Post #1179439
Posted Monday, November 18, 2013 2:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:57 PM
Points: 15, Visits: 59
Would it be possible to amend this to also work on 2008 R2?

Also, seem to get an error when it reaches an offline database (so could be modified to ignore offline databases)

Terry
Post #1515136
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse