none
Refresh DB to Dev from Production and grant permision

    Question

  • Hi I am creating a script refresh DB from production to DEV this is will be routine job every day. My concern referring to DB Users and access permissions are different to dev and prod. Therefore once refresh I need get backup existing DB permission before refresh from prod. Once refresh need to grant back to them same as what had before. There are two scripts one for create users and second grant permission . On create user not getting AD group users list. not sure why.

    Use DB

    SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']' AS '--Database Users Creation--'
    FROM sys.database_principals
    WHERE Type IN (
      'U'
      ,'S'
      )
     AND NAME NOT IN (
      'dbo'
      ,'guest'
      ,'sys'
      ,'INFORMATION_SCHEMA'
      )

    +++++++++++++++++++++++++

    SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '--Add Users to Database Roles--'
    FROM sys.database_principals DBUser
    INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_id
    INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id

    +++++++++++++++++++++

    Any idea on this. Many Thanks


    • Edited by ashwan Wednesday, June 13, 2018 3:37 AM
    Wednesday, June 13, 2018 2:10 AM

All replies

  • http://sqlity.net/en/2584/script-database-permissions/

    SELECT CASE WHEN P.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE P.state_desc END AS cmd_state,
           P.permission_name,
           'ON '+ CASE P.class_desc
               WHEN 'DATABASE' THEN 'DATABASE::'+QUOTENAME(DB_NAME())
               WHEN 'SCHEMA' THEN 'SCHEMA::'+QUOTENAME(S.name)
               WHEN 'OBJECT_OR_COLUMN' THEN 'OBJECT::'+QUOTENAME(OS.name)+'.'+QUOTENAME(O.name)+
                 CASE WHEN P.minor_id <> 0 THEN '('+QUOTENAME(C.name)+')' ELSE '' END
               WHEN 'DATABASE_PRINCIPAL' THEN
                 CASE PR.type_desc 
                   WHEN 'SQL_USER' THEN 'USER'
                   WHEN 'DATABASE_ROLE' THEN 'ROLE'
                   WHEN 'APPLICATION_ROLE' THEN 'APPLICATION ROLE'
                 END +'::'+QUOTENAME(PR.name)
               WHEN 'ASSEMBLY' THEN 'ASSEMBLY::'+QUOTENAME(A.name)
               WHEN 'TYPE' THEN 'TYPE::'+QUOTENAME(TS.name)+'.'+QUOTENAME(T.name)
               WHEN 'XML_SCHEMA_COLLECTION' THEN 'XML SCHEMA COLLECTION::'+QUOTENAME(XSS.name)+'.'+QUOTENAME(XSC.name)
               WHEN 'SERVICE_CONTRACT' THEN 'CONTRACT::'+QUOTENAME(SC.name)
               WHEN 'MESSAGE_TYPE' THEN 'MESSAGE TYPE::'+QUOTENAME(SMT.name)
               WHEN 'REMOTE_SERVICE_BINDING' THEN 'REMOTE SERVICE BINDING::'+QUOTENAME(RSB.name)
               WHEN 'ROUTE' THEN 'ROUTE::'+QUOTENAME(R.name)
               WHEN 'SERVICE' THEN 'SERVICE::'+QUOTENAME(SBS.name)
               WHEN 'FULLTEXT_CATALOG' THEN 'FULLTEXT CATALOG::'+QUOTENAME(FC.name)
               WHEN 'FULLTEXT_STOPLIST' THEN 'FULLTEXT STOPLIST::'+QUOTENAME(FS.name)
               WHEN 'SEARCH_PROPERTY_LIST' THEN 'SEARCH PROPERTY LIST::'+QUOTENAME(RSPL.name)
               WHEN 'SYMMETRIC_KEYS' THEN 'SYMMETRIC KEY::'+QUOTENAME(SK.name)
               WHEN 'CERTIFICATE' THEN 'CERTIFICATE::'+QUOTENAME(CER.name)
               WHEN 'ASYMMETRIC_KEY' THEN 'ASYMMETRIC KEY::'+QUOTENAME(AK.name)
             END COLLATE Latin1_General_100_BIN AS securable,
             'TO '+QUOTENAME(DP.name) AS grantee,
             CASE WHEN P.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 'WITH GRANT OPTION' ELSE '' END AS grant_option,
             'AS '+QUOTENAME(G.name) AS grantor
      FROM sys.database_permissions AS P
      LEFT JOIN sys.schemas AS S
        ON P.major_id = S.schema_id
      LEFT JOIN sys.all_objects AS O
           JOIN sys.schemas AS OS
             ON O.schema_id = OS.schema_id
        ON P.major_id = O.object_id
      LEFT JOIN sys.types AS T
           JOIN sys.schemas AS TS
             ON T.schema_id = TS.schema_id
        ON P.major_id = T.user_type_id
      LEFT JOIN sys.xml_schema_collections AS XSC
           JOIN sys.schemas AS XSS
             ON XSC.schema_id = XSS.schema_id
        ON P.major_id = XSC.xml_collection_id
      LEFT JOIN sys.columns AS C
        ON O.object_id = C.object_id
       AND P.minor_id = C.column_id
      LEFT JOIN sys.database_principals AS PR
        ON P.major_id = PR.principal_id
      LEFT JOIN sys.assemblies AS A
        ON P.major_id = A.assembly_id
      LEFT JOIN sys.service_contracts AS SC
        ON P.major_id = SC.service_contract_id
      LEFT JOIN sys.service_message_types AS SMT
        ON P.major_id = SMT.message_type_id
      LEFT JOIN sys.remote_service_bindings AS RSB
        ON P.major_id = RSB.remote_service_binding_id
      LEFT JOIN sys.services AS SBS
        ON P.major_id = SBS.service_id
      LEFT JOIN sys.routes AS R
        ON P.major_id = R.route_id
      LEFT JOIN sys.fulltext_catalogs AS FC
        ON P.major_id = FC.fulltext_catalog_id
      LEFT JOIN sys.fulltext_stoplists AS FS
        ON P.major_id = FS.stoplist_id
      LEFT JOIN sys.registered_search_property_lists AS RSPL
        ON P.major_id = RSPL.property_list_id
      LEFT JOIN sys.asymmetric_keys AS AK
        ON P.major_id = AK.asymmetric_key_id
      LEFT JOIN sys.certificates AS CER
        ON P.major_id = CER.certificate_id
      LEFT JOIN sys.symmetric_keys AS SK
        ON P.major_id = SK.symmetric_key_id
      JOIN sys.database_principals AS DP
        ON P.grantee_principal_id = DP.principal_id
      JOIN sys.database_principals AS G
        ON P.grantor_principal_id = G.principal_id
     WHERE P.grantee_principal_id IN (USER_ID('TestUser1'), USER_ID('TestUser2'));

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 14, 2018 5:20 AM
    Moderator
  • Hi ashwan,

     

    From your description, My understanding is that you couldn't get AD group users list when executing the first query you provided. If anything is misunderstood, please let me know.

     

    We are currently looking into this issue and will give you an update as soon as possible. Thank you for your understanding and support.

     

    Best Regards,

    Emily Fei


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, June 14, 2018 8:50 AM
  • Hi I am creating a script refresh DB from production to DEV this is will be routine job every day. My concern referring to DB Users and access permissions are different to dev and prod. Therefore once refresh I need get backup existing DB permission before refresh from prod. Once refresh need to grant back to them same as what had before. There are two scripts one for create users and second grant permission . On create user not getting AD group users list. not sure why.


    These are the quick steps that you would perform for the refresh. These cover your question perfectly. I personally follow this procedure and find it pretty robust.

    1. Script out existing DB users, their role membership, and object level permissions from the DEV environment using sp_DBPermissions (Written by Kenneth, I thanked him personally for this great sproc). This should script out AD users creation just fine. Pass the DB name as the parameter during execution.

    2. Restore the PROD backup (FUll or whatever) WITH REPLACE (or, drop the database and restore normally).

    3. Drop all users/permission from the refreshed DB in DEV as it carried over PROD users and permissions using the below script.

    CAUTION: This script removes users from the DB it runs against so ensure that you are in the right DB.

    --script to remove users from a database after prod restore on dev --make sure to capture existing dev permissions before the restore, so they can be applied back USE <RefreshedDB>

    GO create table #User (id int identity, name varchar(250)) insert into #user select name from sys.database_principals where type <> 'r' and len(sid) > 5 declare @sql varchar(1000) declare @Counter int declare @Name varchar(250) set @counter = 1 while @Counter <= (select max(id) from #user) begin set @name = (select name from #user where @counter = id) if exists ( select name from sys.schemas where name = @Name ) begin set @sql = 'ALTER AUTHORIZATION ON SCHEMA::['+@Name+'] TO dbo' exec (@sql) end set @sql = 'drop user [' + @name +']' print @Sql exec (@Sql) set @counter = @counter +1 continue end drop table #User

    4. Finally, run the "create" scripts got from step #1 to re-grant the DEV permissions that existed before the DB was overwritten with PROD.

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, June 14, 2018 11:33 AM
  • Hi ashwan,

     

    It seems your issue is about permission. Based on your create users script, I did the similar test. I created a login user "test" with only public role and user mapping to user databases. When I executed the query, the user didn't return.

    Then I map the "test" user to master database. The login user is listed in the sys table. 

    Could you please check whether you have mapped the login users to master database. If you have any other questions, please let me know.

     

    Best Regards,

    Emily Fe



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, June 19, 2018 3:09 AM