Script out permissions - Azure SQL database

Question

Post reply

Script out permissions - Azure SQL database

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
January 26, 2017 at 11:20 am
Go to Answer
#334429

Hi Folks,
Any one has a script to script out user permissions from Azure SQL database? I was not able to find this in any articles/forums.
Many thanks!

Henk

Newbie

Points: 1

May 8, 2019 at 9:36 am

Answer

#3638186

Hey SQL!@w$0M,

I use this script to generate a Matrix of the rights on a database:

DECLARE @permissionlevels TABLE
(
PermissionLevel NVARCHAR(256)
)

INSERT INTO @permissionlevels
SELECT DISTINCT pp.name AS PermissionLevel
FROM sys.database_role_members roles
LEFT JOIN sys.database_principals p
ON roles.member_principal_id = p.principal_id
LEFT JOIN sys.database_principals pp
ON roles.role_principal_id = pp.principal_id

DECLARE @permissionlevel_columns NVARCHAR(1000)
SET @permissionlevel_columns = ''

SELECT @permissionlevel_columns += '[' + PermissionLevel + '],' FROM @permissionlevels

SET @permissionlevel_columns = STUFF(@permissionlevel_columns, LEN(@permissionlevel_columns), 1, '')

DECLARE @sqlstatement NVARCHAR(MAX)

SET @sqlstatement = 'SELECT ServerName '
+ ', DBName '
+ ', UserName '
+ ', TypeOfLogin '
+ ',' + @permissionlevel_columns
+ 'FROM ( '
+ 'SELECT @@servername AS ServerName '
+ ', db_name(db_id()) AS DBName '
+ ', p.name AS UserName '
+ ', p.type_desc AS TypeOfLogin '
+ ', pp.name AS PermissionLevel '
+ ', pp.type_desc AS TypeOfRole '
+ ', ''x'' AS Autorized '
+ 'FROM sys.database_role_members roles '
+ 'LEFT JOIN sys.database_principals p '
+ 'ON roles.member_principal_id = p.principal_id '
+ 'LEFT JOIN sys.database_principals pp '
+ 'ON roles.role_principal_id = pp.principal_id '
+ ') a '
+ 'PIVOT '
+ '( '
+ 'MAX(Autorized) '
+ 'FOR PermissionLevel IN (' +  @permissionlevel_columns + ') '
+ ') AS pv '
+ 'ORDER BY UserName '

EXEC (@sqlstatement)

Viewing 15 posts - 1 through 15 (of 15 total)

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

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 1

SQL!$@w$0ME - Thursday, January 26, 2017 11:20 AM
Hi Folks,
Any one has a script to script out user permissions from Azure SQL database? I was not able to find this in any articles/forums.
Many thanks!

Anyone has any scripts in hand to script out user permissions from azure db? Please share. Many thanks!

Jeff Moden SSC Guru Points: 1004561 More actions · Answer 2

I can't even spell Azure but, hopefully, this will act as a "bump" for your question so that someone might see it and answer.

--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)

BLOB EATER SSChampion Points: 14609 More actions · Answer 3

If you go to bottom of this post you will see some TSQL to run under the database in Azure. Its a starting point I think! It will give you the logins (AD ones too) with permissions assigned to them.

https://blobeater.blog/2017/05/22/ad-authentication-and-azure-sql-database/

thanks

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 4

BLOB EATER - Thursday, June 1, 2017 7:31 AM
If you go to bottom of this post you will see some TSQL to run under the database in Azure. Its a starting point I think! It will give you the logins (AD ones too) with permissions assigned to them.
https://blobeater.blog/2017/05/22/ad-authentication-and-azure-sql-database/
thanks

Thanks, it would be good to have object level permissions scripted out.

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 5

SQL!$@w$0ME - Thursday, January 26, 2017 11:20 AM
Hi Folks,
Any one has a script to script out user permissions from Azure SQL database? I was not able to find this in any articles/forums.
Many thanks!

Any one has a script handy to script out permissions from Azure SQL database?

Thanks!

Jeff Moden SSC Guru Points: 1004561 More actions · Answer 6

SQL!$@w$0ME - Wednesday, August 30, 2017 12:00 PM
SQL!$@w$0ME - Thursday, January 26, 2017 11:20 AM
Hi Folks,
Any one has a script to script out user permissions from Azure SQL database? I was not able to find this in any articles/forums.
Many thanks!
Any one has a script handy to script out permissions from Azure SQL database?
Thanks!

From what I understand, it's the same in Azure as it is in local metal systems. See the following. It has scripts in the examples but I do recommend that you read the entire article.
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-permissions-transact-sql

--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)

BLOB EATER SSChampion Points: 14609 More actions · Answer 7

Jeff is right! I have ran Script B from the link above on Azure SQL Database and I get something like: (snippet)

name type_desc authentication_type_desc state_desc permission_name ObjectName
Jimmy SQL_USER DATABASE GRANT SELECT SalesLT.vGetAllCategories

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 8

BLOB EATER - Thursday, August 31, 2017 8:22 AM
Jeff is right! I have ran Script B from the link above on Azure SQL Database and I get something like: (snippet)
name type_desc authentication_type_desc state_desc permission_name ObjectName
Jimmy SQL_USER DATABASE GRANT SELECT SalesLT.vGetAllCategories

I cannot fully agree with you that accounts/permissions are similar to earlier versions of native SQL Server. How do you script out azure AAD authentication credentials/permissions in sql azure db which does not exist in earlier versions of SQL.
user.name@domain.com(azure) vs DOMAIN\username(native sql)
Thanks!

Jeff Moden SSC Guru Points: 1004561 More actions · Answer 9

Dunno but I'm pretty sure that a decent look in Yabingooglehoo will help. It's what I'd have to do if I were looking for such a thing.

--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)

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 10

Hi Folks,
Any one has a script to script out user permissions from Azure SQL database? I was not able to find this in any articles/forums.
Many thanks!

Andrey SSC Eights! Points: 840 More actions · Answer 11

my 5 cents :

select 
isnull(object_schema_name(major_id,db_id()) +'.'+ object_name(major_id),
case when dp.class_desc='SCHEMA' then 'db schema '+quotename(schema_name(dp.major_id)) else dp.class_desc end ) [object]
,permission_name [permission]
, user_name(dp.grantee_principal_id) [GrantedTo]
, user_name(dp.grantor_principal_id) [GrantedBy]
, o.is_ms_shipped
, o.type_desc
,dp.state_desc
from sys.database_permissions dp
left join sys.objects o on dp.major_id=o.object_id 
order by 1
option (recompile)

This reply was modified 5 years, 11 months ago by Andrey. Reason: comments are removed from the code

tung858 SSC Veteran Points: 241 More actions · Answer 12

select rp.name as database_role, mp.name as database_user
from sys.database_role_members drm
  join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
  join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
order by rp.name

-- object permissions by role
SELECT DISTINCT rp.name, 
                ObjectType = rp.type_desc, 
                PermissionType = pm.class_desc, 
                pm.permission_name, 
                pm.state_desc, 
                ObjectType = CASE 
                               WHEN obj.type_desc IS NULL 
                                     OR obj.type_desc = 'SYSTEM_TABLE' THEN 
                               pm.class_desc 
                               ELSE obj.type_desc 
                             END, 
                s.Name as SchemaName,
                [ObjectName] = Isnull(ss.name, Object_name(pm.major_id)) 
FROM   sys.database_principals rp 
       INNER JOIN sys.database_permissions pm 
               ON pm.grantee_principal_id = rp.principal_id 
       LEFT JOIN sys.schemas ss 
              ON pm.major_id = ss.schema_id 
       LEFT JOIN sys.objects obj 
              ON pm.[major_id] = obj.[object_id] 
       LEFT JOIN sys.schemas s
              ON s.schema_id = obj.schema_id
--WHERE  rp.type_desc = 'DATABASE_ROLE' 
--       AND pm.class_desc <> 'DATABASE' 
ORDER  BY rp.name, 
          rp.type_desc, 
          pm.class_desc 


select schema_name(schema_id) as schemanames,
user_name(s.principal_id) as usernames
from sys.schemas As s

Tung Dang
Azure and SQL Server DBA Contractor / Consultant
DataZip

James Rossi Grasshopper Points: 21 More actions · Answer 13

Enjoy, this query is made for Azure SQL. Please modify it accordingly

DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'thinkbrainbox@alphaonedata.com'

SET NOCOUNT ON
DECLARE
    @errStatement [varchar](8000),
    @msgStatement [varchar](8000),
    @DatabaseUserID [smallint],
    @ServerUserName [sysname],
    @RoleName [varchar](8000),
    @ObjectID [int],
    @ObjectName [varchar](261)

SELECT @DatabaseUserID = [sysusers].[uid]
FROM [dbo].[sysusers]
WHERE [sysusers].[name] = @DatabaseUserName

IF @DatabaseUserID IS NULL
    BEGIN
        SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
        'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
    RAISERROR(@errStatement, 16, 1)
    END
ELSE
    BEGIN
        SET @msgStatement = '--Security creation script for user ' + @DatabaseUserName + CHAR(13) +
        '--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
        '--Created By: ' + SUSER_NAME() + CHAR(13) +
        '--Add User To Database' + CHAR(13) +
        'CREATE USER [' + @DatabaseUserName + '] '+ ( select case when type_desc in ('EXTERNAL GROUP','EXTERNAL_USER') then  'FROM EXTERNAL PROVIDER'
                                                    when type_desc in ('SQL USER') then  'WITH PASSWORD = ' END
                                                        from sys.database_principals where name = @DatabaseUserName) + CHAR(13) +

        '--Add User To Roles'
        PRINT @msgStatement

        DECLARE _sysusers CURSOR LOCAL FORWARD_ONLY READ_ONLY
            FOR
                SELECT [name]
                FROM [dbo].[sysusers]
                WHERE [uid] IN (SELECT [groupuid] FROM [dbo].[sysmembers] WHERE [memberuid] = @DatabaseUserID )

        OPEN _sysusers
    
        FETCH NEXT FROM _sysusers INTO @RoleName
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
                CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
                CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''

            PRINT @msgStatement
    
        FETCH NEXT FROM _sysusers INTO @RoleName
    END

    SET @msgStatement = CHAR(13) +


    '--Set Object Specific Permissions'
    PRINT @msgStatement

    DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
        SELECT DISTINCT([sysobjects].[id]), '[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
        FROM [dbo].[sysprotects]
            INNER JOIN [dbo].[sysobjects] ON [sysprotects].[id] = [sysobjects].[id]
        WHERE [sysprotects].[uid] = @DatabaseUserID

    OPEN _sysobjects

    FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName

    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @msgStatement = ''
            IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
            SET @msgStatement = @msgStatement + 'SELECT,'
            IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
            SET @msgStatement = @msgStatement + 'INSERT,'
            IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
            SET @msgStatement = @msgStatement + 'UPDATE,'
            IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
            SET @msgStatement = @msgStatement + 'DELETE,'
            IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
            SET @msgStatement = @msgStatement + 'EXECUTE,'
            IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
            SET @msgStatement = @msgStatement + 'REFERENCES,'

            IF LEN(@msgStatement) > 0
            BEGIN
                IF RIGHT(@msgStatement, 1) = ','
                SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
                SET @msgStatement = 'GRANT' + CHAR(13) +
                CHAR(9) + @msgStatement + CHAR(13) +
                CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
                CHAR(9) + 'TO ' + @DatabaseUserName
                PRINT @msgStatement
            END

            SET @msgStatement = ''
                IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
                SET @msgStatement = @msgStatement + 'SELECT,'
                IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
                SET @msgStatement = @msgStatement + 'INSERT,'
                IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
                SET @msgStatement = @msgStatement + 'UPDATE,'
                IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
                SET @msgStatement = @msgStatement + 'DELETE,'
                IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
                SET @msgStatement = @msgStatement + 'EXECUTE,'
                IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
                SET @msgStatement = @msgStatement + 'REFERENCES,'

            IF LEN(@msgStatement) > 0
            BEGIN
                IF RIGHT(@msgStatement, 1) = ','
                    SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
                    SET @msgStatement = 'DENY' + CHAR(13) +
                CHAR(9) + @msgStatement + CHAR(13) +
                CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
                CHAR(9) + 'TO ' + @DatabaseUserName
                PRINT @msgStatement
            END

            FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
        END
    
    CLOSE _sysobjects
    DEALLOCATE _sysobjects

    END

*** Please network with me and feel free to IM if you have any more DBA questions and concerns

https://www.linkedin.com/in/james-rossi-9094651b7/

This reply was modified 4 years, 6 months ago by James Rossi.
This reply was modified 4 years, 6 months ago by James Rossi.

james rossi
Senior SQL Server DBA
Senior Software Developer
Alpha One Data
Brain Box SQL Mobile DBA

Script out permissions - Azure SQL database

Cookies on SQLServerCentral