Needed database level permissions script for whole instance

Question

Needed database level permissions script for whole instance

johnnyrmtl

Hall of Fame

Points: 3505
More actions
September 7, 2012 at 12:59 pm

#281238

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Do you have something that does what you want for a single database?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lowell SSC Guru Points: 323504 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

johnnyrmtl Hall of Fame Points: 3505 More actions · Answer 3

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

Lowell SSC Guru Points: 323504 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!