Script out permissions - Azure SQL database

  • SQL!$@w$0ME

    SSChampion

    Points: 12345

    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!

  • SQL!$@w$0ME

    SSChampion

    Points: 12345

    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: 997158

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • BLOB EATER

    SSChampion

    Points: 14614

    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: 12345

    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: 12345

    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: 997158

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • BLOB EATER

    SSChampion

    Points: 14614

    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: 12345

    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: 997158

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • SQL!$@w$0ME

    SSChampion

    Points: 12345

    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

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

    Right there with Babe

    Points: 723

    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 1 year, 4 months ago by  Andrey. Reason: comments are removed from the code
  • tung858

    SSC Veteran

    Points: 234

    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
    Senior SQL Server DBA
    Calpine Energy Solutions
    Brain Box SQL Mobile DBA

  • James Rossi

    Grasshopper

    Points: 21

    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 6 days, 18 hours ago by  James Rossi.
    • This reply was modified 6 days, 18 hours ago by  James Rossi.

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

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

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