locked
Sql Server 2008R2 Data Migration to cloud. RRS feed

  • Question

  • Hi All,

    We are doing cloud migration for our sql server instance from cluster setup. We did normal database refresh(took a backup and restored to cloud server). After refresh application users came back saying they can access the database but can not have the assigned privileges.

    Per application users there used to be more than 1500 db_owners for a single database, when i queried I found only 3 db_owners, I gave db_owner accesses to the users requested by business, Still other privileges are unknown. 

    What could be the reason for this we just did a normal restore but we ended up missing roles and permissions.

    sql server - 2008 R2 Sp1


    Best Regards Moug

    Sunday, June 8, 2014 11:45 PM

Answers

All replies

  • Hi,

    I doubt you have restored the system database with insufficient data.

    If so, I suggest you rebuild the master database (no restore) in a fresh instance and see how it works..

    Thanks.


    Tracy Cai
    TechNet Community Support

    Tuesday, June 10, 2014 5:43 AM
  • Any update as asked by Tracy?

    Also good link to check for logins and permission is mentioned below:

    http://www.kendalvandyke.com/2009/01/scripting-server-permissions-and-role.html


    Santosh Singh

    • Marked as answer by Moug45 Tuesday, June 10, 2014 9:30 PM
    Tuesday, June 10, 2014 6:06 PM
  • Thanks Tracy for your response. I restored the database but missed the server level permissions as they are stored in master.. So missed all the server level Sysadmins. Now I worked on it.

    Best Regards Moug

    Tuesday, June 10, 2014 9:28 PM
  • Thanks Santhosh, This is exactly what I missed. ignored all server level permissions.

    Anyways I have designed my own script to extract only the server level permissions and execute it new cloud server.

    SELECT 'EXEC sp_addsrvrolemember @loginame =' 
    + SPACE(1) + QUOTENAME(SUSER_NAME(a.member_principal_id), '''') 
    + ', @rolename =' + SPACE(1) + QUOTENAME(SUSER_NAME(a.role_principal_id), '''') AS 
    '--Role Memberships'FROM sys.server_role_members AS a 


    Best Regards Moug

    Tuesday, June 10, 2014 9:30 PM
  • Nice one!!

    Glad you liked that link.


    Santosh Singh

    Wednesday, June 11, 2014 4:35 AM