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

Needed database level permissions script for whole instance Expand / Collapse
Author
Message
Posted Friday, September 7, 2012 12:59 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:56 AM
Points: 75, Visits: 343
Hello,

I cannot seem to find a script like this which would produce the users and their DATABASE LEVEL permissions like for the whole instance
ALTER ANY ASSEMBLY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
ALTER ANY CONTRACT
....

Many thanks
Post #1356193
Posted Friday, September 7, 2012 2:03 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Do you have something that does what you want for a single database?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1356232
Posted Friday, September 7, 2012 2:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 12,903, Visits: 31,972
I have this saved in my snippets that seems to do a good job of generating explicit permissions;
does this produce the results you are after for a single database?
select sys.schemas.name 'Schema'
, sys.objects.name Object
, sys.database_principals.name username
, sys.database_permissions.type permissions_type
, sys.database_permissions.permission_name
, sys.database_permissions.state permission_state
, sys.database_permissions.state_desc
, state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
--WHERE sys.database_principals.name = 'your_user_or_role'
order by 1, 2, 3, 5



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1356239
Posted Monday, September 10, 2012 11:14 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:56 AM
Points: 75, Visits: 343
Thanks for that and it does indeed work on a sole database at a time.

I would need it to run from master and get all databases
Post #1356902
Posted Monday, September 10, 2012 11:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 12,903, Visits: 31,972
johnnyrmtl (9/10/2012)
Thanks for that and it does indeed work on a sole database at a time.

I would need it to run from master and get all databases


you would jsut wrap it with exec sp_msForEachDb and paramterize the databases right? sys.objects becomes ?.sys.objects, etc etc to get teh database context.
i know it's easy for me to do, but did you try that yet? learning to adapt the code you are going to use is going to be invaluable to you. you are the one who has to maintain the code after you've copied it from here.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1356911
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse