Script DB Level Permissions v4.5

  • SQL!$@w$0ME

    SSChampion

    Points: 12343

    Can you provide a script to script out permissions for Azure SQL database, Thanks!

  • T. Bradley

    Newbie

    Points: 2

    Can you add code to output Roles that are assigned permissions at the Database level?  It looks like I'm only seeing users in the output.  example:  I create a role named db_executor then assign that role at the db level to execute but it didn't show up in your output.

    I expected to see something like this:

    -- [--DB LEVEL PERMISSIONS --] --

    IF DATABASE_PRINCIPAL_ID('db_executor') IS NOT NULL GRANT EXECUTE TO [db_executor]

    Thanks! This is a great script.

  • T. Bradley

    Newbie

    Points: 2

    I think this is the fix but please verify.  I added type of 'R' to your where statement in the select under DB LEVEL PERMISSIONS.

    WHERE [perm].[major_id] = 0

    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas

    AND [usr].[type] IN ('G', 'S', 'U', 'R') -- S = SQL user, U = Windows user, G = Windows group, R=Role  -- Added R for R types

  • S. Kusen

    SSChampion

    Points: 10849

    Comments posted to this topic are about the item Script DB Level Permissions v4.5

  • Raju. K

    Newbie

    Points: 1

    Hi Kusen,

    Your script is awesome and very use full. But I need request one thing here. can we get below things when we execute this script?

    if not can add these things in your script?

    script out all any certificates in the database,

    Script out any users that are protected by a cert

    script out all user defined schemas,

    script out Application roles from the database,

    script out any nested role permissions,

    Thanks,

    Raju K.

    Raju K.

  • groove_timer

    SSC Journeyman

    Points: 80

    Great Script! Thanks for making this available.

    May I ask if there are plans to incorporate it into a stored procedure to allow e.g. the passing in of specific databases?

    Thanks

    Craig

Viewing 6 posts - 16 through 21 (of 21 total)

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