Script out permissions - Azure SQL database

  • SQL!$@w$0ME

    SSChampion

    Points: 12343

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

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

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • BLOB EATER

    SSChampion

    Points: 14564

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

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

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

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • BLOB EATER

    SSChampion

    Points: 14564

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

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

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • SQL!$@w$0ME

    SSChampion

    Points: 12343

    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

    SSChasing Mays

    Points: 644

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

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

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