Script DB Level Permissions

  • AmarettoSlim (1/24/2013)


    S. Kusen, thank you for this helpful script!

    Glad you got some good use out of it!

    Cheers.

  • I had an older version of this script that I had inherited from my predecessor, stripped of any author information, and was just about to edit it so that it would script out schema permissions as well, but decided to ask google about it first. Lo and behold, I found 2.1, and saved myself a bit of work. The original script has already been more than useful, so I just wanted to say thanks!

  • I have to say that this script is great and it works amazing.

    I have a suggestion about database permissions on SQL 2012. At our current location, we change database containment type to Partial. SQL logins are created at the database level as compared to server level. So when I script permissions out, the scripts will create SQL login without the password but cannot apply it since it cannot find the SQL login. Is there any option to add this?

    Thanks!

  • Do you mean to say you need something like this? Try executing this piece (in text mode) on your database, it will generate create user scripts only for those users without a login.

    SET NOCOUNT ON

    DECLARE @name_holder VARCHAR(255)

    DECLARE my_cursor CURSOR FOR

    SELECT name

    FROM sys.database_principals

    WHERE Datalength(sid) >= 28

    AND sid NOT IN (SELECT sid

    FROM sys.server_principals)

    AND type = 'S'

    AND principal_id > 4

    OPEN my_cursor

    FETCH next FROM my_cursor INTO @name_holder

    WHILE ( @@FETCH_STATUS <> -1 )

    BEGIN

    SELECT

    'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '

    + Space(1) + '''' + name + '''' + ') BEGIN CREATE USER '

    + Space(1) + Quotename([name])

    + ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '

    + Quotename([default_schema_name]) + Space(1)

    + 'END; '

    FROM sys.database_principals AS rm

    WHERE [type] IN ( 'U', 'S', 'G' )

    AND name = @name_holder

    FETCH next FROM my_cursor INTO @name_holder

    END

    CLOSE my_cursor

    DEALLOCATE my_cursor

  • aruopna (1/24/2014)


    Do you mean to say you need something like this? Try executing this piece (in text mode) on your database, it will generate create user scripts only for those users without a login.

    SET NOCOUNT ON

    DECLARE @name_holder VARCHAR(255)

    DECLARE my_cursor CURSOR FOR

    SELECT name

    FROM sys.database_principals

    WHERE Datalength(sid) >= 28

    AND sid NOT IN (SELECT sid

    FROM sys.server_principals)

    AND type = 'S'

    AND principal_id > 4

    OPEN my_cursor

    FETCH next FROM my_cursor INTO @name_holder

    WHILE ( @@FETCH_STATUS <> -1 )

    BEGIN

    SELECT

    'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '

    + Space(1) + '''' + name + '''' + ') BEGIN CREATE USER '

    + Space(1) + Quotename([name])

    + ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '

    + Quotename([default_schema_name]) + Space(1)

    + 'END; '

    FROM sys.database_principals AS rm

    WHERE [type] IN ( 'U', 'S', 'G' )

    AND name = @name_holder

    FETCH next FROM my_cursor INTO @name_holder

    END

    CLOSE my_cursor

    DEALLOCATE my_cursor

    Thanks for this. I'll evaluate adding it to the script. I didn't adjust the script for contained DB users, obvioiusly. I'll see what I can do for getting an update out for that.

    Appreciate you asking, Srizwanh, as well.

    Steve

  • In reviewing this for a while, I think the only way to go is to specify creating the user without a login, rather than setting a default password. I am unable to find a way to get at the DB user's password hash to script it out. In sp_help_revlogin, the LOGINPROPERTY function is used to get the PasswordHash property of the login, but obviously the contained DB users with passwords are not logins, and I cannot locate any DMV's or properties to grab that hash.

    As such, if you have contained DB's, please see the below code to adjust. I will look at getting this update posted. To note, on the sys.database_principals view, the authentication_type of 2 is "DATABASE" in authentication types.

    I believe this is the only fix to be adjusted as of now.

    Thanks all for checking the script out.

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

    3 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT

    CASE WHEN rm.authentication_type IN (2, 0) /* 2=contained database user with password, 0 =user without login; create users without logins*/ THEN ('IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ')

    ELSE ('IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ')

    END AS [-- SQL STATEMENTS --],

    4 AS [-- RESULT ORDER HOLDER --]

    FROMsys.database_principals AS rm

    WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

  • Hi Susan,

    Thank you for looking into it. I will test it and get back to you with results.

  • SELECT'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],

    The above part wroks fine if a Login and username have a same name, but in case if a user mapped to a login has a different name it script would does not make sense.

    Can you please suggest how to handle the scenario where user name mapped to login are not of same name.

  • Hi virgo,

    You can change the code over to the following that uses suser_sname(sid) instead of [name]. I'll update the posted script accordingly. Thanks for finding that issue.

    + ' FOR LOGIN ' + QUOTENAME([name]) +

    + ' FOR LOGIN ' + QUOTENAME(suser_sname([sid])) +

    SELECT'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME(suser_sname([sid])) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],

    4 AS [-- RESULT ORDER HOLDER --]

    FROMsys.database_principals AS rm

    WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

    I tested it out and seems to work for what you need.

    Regards,

    Steve

  • Thank you for creating/sharing this script! I'm a new DBA and I spend hours restoring from production to test and haven't been able to figure out how to script this. I've talked with a log of people and they have no idea. Thanks again.

    Marcus

  • mhorner 67968 (11/24/2014)


    Thank you for creating/sharing this script! I'm a new DBA and I spend hours restoring from production to test and haven't been able to figure out how to script this. I've talked with a log of people and they have no idea. Thanks again.

    Marcus

    You're welcome, glad you found it useful!

  • Steve,

    Script is very good and helps in copying db permissions specifically if you are overwriting db from different environment and with different db permissions.

    Script output is very clean.

    It fails to script any user defined DB roles and thus any users related to it.

    Subhash

  • subhash.raut-762682 (1/14/2015)


    Steve,

    Script is very good and helps in copying db permissions specifically if you are overwriting db from different environment and with different db permissions.

    Script output is very clean.

    It fails to script any user defined DB roles and thus any users related to it.

    Subhash

    Hi subhash.raut-762682,

    I figured out what was missing. If you update lines 75 and 158 to have the ('G', 'S', 'U') updated to ('G', 'S', 'U', 'R'), then it should work.

    The object-level permissions were working (ie grant select on dbo.table_1 to [your_database_role_name], but the permissions assigned to the database role at the database layer were being missed.

    i.e. this permission would not be scripted out previously, but will be scripted out after changing those where clauses on the lines I specified:

    GRANT CONNECT TO [your_database_role_name]

    Thanks for pointing that out and helping me improve the script.

    Cheers,

    Steve

  • Hi Kusen,

    First, Thanks for the excellent script and keep updating with the feedback is enourmous.

    I just tested the script by adding 'R' in two stmts (as you mentioned in one of the post) but still I think it is not creating the User DB Roles as expected.

    I would like to see these roles should be scripted as below. I do not have these roles in Production as DEV but ultimately after restoring/overwriting the existing DEV database with a copy of Production, I need to re-create these roles and then add required members into that and the object level permissions.

    Example: CREATE ROLE [DevUsersOnly] AUTHORIZATION [dbo]

    Thanks,

    Mubeen

  • Script is really good and it helps a lot.

    Does this script resolves the issues with Orphan Users?

    If not it would be good if we include that portion in the script as well....

Viewing 15 posts - 16 through 30 (of 58 total)

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