Database User and Roles Script Extract Needed

  • sunshine-587009

    SSCrazy Eights

    Points: 8812

    I'm looking for a script that extracts the code to create users and granted roles per database. I've found partials of either or, logins, lists, create scripts for logins, but not db users and their roles. Does anyone have a good source for this? Any help appreciated!

    Thank you!

    ¤ §unshine ¤

  • ps.

    One Orange Chip

    Points: 29252

    http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx

    i used this script few days back. this scripts permissions for a single user. you might need to wrap the code in a loop or cursor to extract data for all users.



    Pradeep Singh

  • sunshine-587009

    SSCrazy Eights

    Points: 8812

    Great! Let me see what I can figure out... thank you so much!

    ¤ §unshine ¤

  • raistlinx

    Ten Centuries

    Points: 1345

    I wrote this for a recent project, does this help?

    SELECT 'IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '+ name + ') ' + char(13)

    + 'CREATE USER [' + CONVERT(char(20), name + ']') + ' FOR LOGIN [' + CONVERT(char(20), name + ']') + ' WITH DEFAULT_SCHEMA = dbo'

    FROM sys.database_principals WHERE type IN ('S', 'U') and principal_id > 4

    SELECT 'EXEC sys.sp_addrolemember @rolename = ' + CONVERT(char(25), b.name) + ' , @membername = ' + CONVERT(char(20), c.name)

    from sys.database_role_members a JOIN sys.database_principals b on a.role_principal_id = b.principal_id

    JOIN sys.database_principals c on a.member_principal_id = c.principal_id

    WHERE b.type = 'R' and c.name != 'dbo'

    ORDER by b.name, c.name

  • ps.

    One Orange Chip

    Points: 29252

    Looks nice. however it wont script object level permissions.



    Pradeep Singh

  • S. Kusen

    SSChampion

    Points: 10872

    This one should work- I posted on another forum post a while back but can't find the link. You'd need to run it on each database you are scripting permissions for, but it will get object level perms and role memberships.

    Should get you everything you need.

    Regards,

    Steve

    /*

    This script will script the role members for all roles on the database.

    This is useful for scripting permissions in a development environment before refreshing

    development with a copy of production. This will allow us to easily ensure

    development permissions are not lost during a prod to dev restoration.

    */

    /*********************************************/

    /********* DB CONTEXT STATEMENT *********/

    /*********************************************/

    SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],

    1 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],

    1 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT '' AS [-- SQL STATEMENTS --],

    2 AS [-- RESULT ORDER HOLDER --]

    UNION

    /*********************************************/

    /********* DB ROLE PERMISSIONS *********/

    /*********************************************/

    SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],

    3 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT 'EXEC sp_addrolemember @rolename ='

    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],

    3 AS [-- RESULT ORDER HOLDER --]

    FROM sys.database_role_members AS rm

    WHERE USER_NAME(rm.member_principal_id) IN (

    --get user names on the database

    SELECT [name]

    FROM sys.database_principals

    WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas

    and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

    )

    --ORDER BY rm.role_principal_id ASC

    UNION

    SELECT '' AS [-- SQL STATEMENTS --],

    4 AS [-- RESULT ORDER HOLDER --]

    UNION

    /*********************************************/

    /********* OBJECT LEVEL PERMISSIONS *********/

    /*********************************************/

    SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

    5 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT CASE

    WHEN perm.state <> 'W' THEN perm.state_desc

    ELSE 'GRANT'

    END

    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects

    + CASE

    WHEN cl.column_id IS NULL THEN SPACE(0)

    ELSE '(' + QUOTENAME(cl.name) + ')'

    END

    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

    + CASE

    WHEN perm.state <> 'W' THEN SPACE(0)

    ELSE SPACE(1) + 'WITH GRANT OPTION'

    END

    AS [-- SQL STATEMENTS --],

    5 AS [-- RESULT ORDER HOLDER --]

    FROM

    sys.database_permissions AS perm

    INNER JOIN

    sys.objects AS obj

    ON perm.major_id = obj.[object_id]

    INNER JOIN

    sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

    LEFT JOIN

    sys.columns AS cl

    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

    --WHERE usr.name = @OldUser

    --ORDER BY perm.permission_name ASC, perm.state_desc ASC

    UNION

    SELECT '' AS [-- SQL STATEMENTS --],

    6 AS [-- RESULT ORDER HOLDER --]

    UNION

    /*********************************************/

    /********* DB LEVEL PERMISSIONS *********/

    /*********************************************/

    SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

    7 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT CASE

    WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

    ELSE 'GRANT'

    END

    + SPACE(1) + perm.permission_name --CONNECT, etc

    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>

    + CASE

    WHEN perm.state <> 'W' THEN SPACE(0)

    ELSE SPACE(1) + 'WITH GRANT OPTION'

    END

    AS [-- SQL STATEMENTS --],

    7 AS [-- RESULT ORDER HOLDER --]

    FROM sys.database_permissions AS perm

    INNER JOIN

    sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

    --WHERE usr.name = @OldUser

    WHERE [perm].[major_id] = 0

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

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

    ORDER BY [-- RESULT ORDER HOLDER --]

  • ps.

    One Orange Chip

    Points: 29252

    you can submit the script in the scripts section so that it is easier to find out later on this forum.



    Pradeep Singh

  • S. Kusen

    SSChampion

    Points: 10872

    We were thinking the same way! - I submitted it immediately after I re-posted.

    Thanks!

    Steve

  • MysteryJimbo

    SSC-Insane

    Points: 24203

    Idera also do a free tool if you are able to install it.

    Idera Permissions 2.0[/url]

  • logicinside22

    SSCrazy Eights

    Points: 9617

    Hi

    I am looking for same script to generate script for User DB roles . Can you share if you have it now?

    Or if anyone have it can share ?

    Thanks

    Aim to inspire rather than to teach.
    SQL Server DBA

  • S. Kusen

    SSChampion

    Points: 10872

    logicinside22 (3/28/2013)


    Hi

    I am looking for same script to generate script for User DB roles . Can you share if you have it now?

    Or if anyone have it can share ?

    Thanks

    I think the script I have posted here [/url] should work. I just created an empty DB, added a table, added a new role, and issued a "grant select on dbo.table1 to test_role" and then executed the script.

    The output I got back was under object level permissions for that role:

    -- [-- OBJECT LEVEL PERMISSIONS --] --

    GRANT SELECT ON [dbo].[table1] TO [test_role1]

    Check that out and let me know if something is missing there.

    Cheers,

    Steve

  • pruthvirajgowda 8776

    Mr or Mrs. 500

    Points: 561

    this will script out everything

    http://dbasqlhelp.blogspot.in/2015/11/extract-all-database-permissions-before.html

    remove the last insert which will fix orphan users

  • pruthvirajgowda 8776

    Mr or Mrs. 500

    Points: 561

    https://gallery.technet.microsoft.com/Extract-Database-dfa53d5a

    that might help you

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

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