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, December 9, 2014 1:26 PM
Points: 37, Visits: 466
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: Yesterday @ 5:57 PM
Points: 22, Visits: 91
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
Posted Thursday, September 18, 2014 9:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 8:22 AM
Points: 565, Visits: 1,474
terry.home (11/18/2013)
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)



I had the same issue (using 2008 R2). See the post by ken.trock earlier in the discussion for the solution.
Post #1614978
Posted Thursday, September 18, 2014 1:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:02 PM
Points: 51, Visits: 230
What a surpprise! Your script even support those funny long name of Sharepoint database.

Execellent job!

Thanks,

Ray Wang



Post #1615065
Posted Thursday, September 25, 2014 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 22, 2014 1:45 PM
Points: 5, Visits: 39
Hi Folks!

Nice script! The only problem I see it's when the user (o role) have permission on a schema instead of a table. In that case the script will show that the user have permission on a system table (like sysrowsets) because the mayor_id value is 5, and that correspond to the sys.schemas view (schema_id) and not the sys.sysobjects (object_id).

Regards.

JP.
Post #1619879
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse