database schema and permissions

Question

database schema and permissions

lan2022nguyen

SSC-Addicted

Points: 459
More actions
April 7, 2023 at 12:03 pm

#4170507

Hi - trying to find a t-sql to list all db schemas and their permissions. unable to find anything helpful on google. thanks

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

I've not ever had to do this before. I started at sys.schemas and worked by way up to the other two sys views but, like I said, I've not done this before and I'm not sure that it's 100% correct.


 SELECT  Schema_Name     = schm.name
        ,User_Name       = prin.name
        ,User_Type       = prin.type_desc
        ,permission_name = ISNULL(perm.permission_name,'***NONE***')
        ,state_desc      = ISNULL(perm.state_desc     ,'***NONE***')
   FROM      sys.schemas              schm 
   LEFT JOIN sys.database_principals  prin ON prin.principal_id = schm.principal_id
   LEFT JOIN sys.database_permissions perm ON perm.major_id     = schm.schema_id
  --WHERE perm.class_desc = 'SCHEMA' --Uncomment for only schemas that have permissions on them
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

will this give you what you need? https://www.sqlservercentral.com/scripts/script-db-level-permissions-v4-3

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 3

Jeff Moden wrote:

I've not ever had to do this before. I started at sys.schemas and worked by way up to the other two sys views but, like I said, I've not done this before and I'm not sure that it's 100% correct.

 SELECT  Schema_Name     = schm.name
        ,User_Name       = prin.name
        ,User_Type       = prin.type_desc
        ,permission_name = ISNULL(perm.permission_name,'***NONE***')
        ,state_desc      = ISNULL(perm.state_desc     ,'***NONE***')
   FROM      sys.schemas              schm 
   LEFT JOIN sys.database_principals  prin ON prin.principal_id = schm.principal_id
   LEFT JOIN sys.database_permissions perm ON perm.major_id     = schm.schema_id
  --WHERE perm.class_desc = 'SCHEMA' --Uncomment for only schemas that have permissions on them
;

thank you so much. it worked!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

@lan2022nguyen ,

Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 5

In my environment, i have a very similar need, but much more robust.

I need to script out the current permissions from a development database
restore a database from prod,
mask/null/modify prod data for PII reasons
modify specific values in the db, so they don't point at production resources like urls and settings
restore those permissions from the first step on the refreshed database

schema level permissions are just a small slice, but here's an example

SELECT QUOTENAME(dp2.name),
               'Section 260 Schemas',
               dp2.name,
               REPLACE(dp.state_desc, 'GRANT_WITH_GRANT_OPTION', 'GRANT')COLLATE SQL_Latin1_General_CP1_CI_AS + ' '
               + dp.permission_name + ' ON ' + dp.class_desc + '::' + QUOTENAME(sch.name) + ' TO '
               + QUOTENAME(dp2.name) + CASE
                                           WHEN CONVERT(VARCHAR(128), dp.state_desc) = 'GRANT_WITH_GRANT_OPTION' THEN
                                               ' WITH GRANT OPTION'
                                           ELSE
                                               ' '
                                       END + ' AS ' + QUOTENAME(dp3.name)
        FROM sys.database_permissions dp --select * from sys.database_permissions where class = 3
            INNER JOIN sys.schemas sch --select * from sys.schemas
                ON dp.grantor_principal_id = sch.principal_id
                   AND dp.[major_id] = sch.[schema_id]
            INNER JOIN sys.database_principals dp2
                ON dp.grantee_principal_id = dp2.principal_id
            INNER JOIN sys.database_principals dp3
                ON dp.grantor_principal_id = dp3.principal_id
        WHERE dp.class = 3 --dp.major_id BETWEEN 1 AND 8
        ORDER BY dp2.name;

2023-04-15_10-34-55

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!