Post reply

Restore Permissions

  • July 1, 2009 at 7:05 am

    #202010

    I have a client that wishes to restore databases on sql, he owns the data so im ok with that. But what permissions should i give. Fot them to use the GUI they need sysadmin to allow them access to view files. it does restore using code with db_creator role. however the database they want to restore comes from a different server with different username/login, which means that when it is restore the user no longer has access to the database, How can i get it so he can gain accees to the restored database

    thanks

  • July 1, 2009 at 7:30 am

    #1018379

    Andrew Collins (7/1/2009)

    I have a client that wishes to restore databases on sql, he owns the data so im ok with that. But what permissions should i give. Fot them to use the GUI they need sysadmin to allow them access to view files. it does restore using code with db_creator role. however the database they want to restore comes from a different server with different username/login, which means that when it is restore the user no longer has access to the database, How can i get it so he can gain accees to the restored database

    thanks

    You can find out orphaned users by running

    sp_change_users_login 'report'

    This will give you a list of users for whom the login id is missing.

    You can create those login IDs.

    Or better, if the source database server exists, you can copy the logins to the new servers.


    Pradeep Singh

  • July 15, 2009 at 12:57 am

    #1024831

    This may be irrelevant as i'm a novice in SQL.

    But i assume you are using sql logins? are the servers set up for mixed mode authentication?

    in my understanding this wouldn't occur (orphaned users) if you use a domain account on each server.

    If you use sql logins, i suppose you could create users with the same SID on both SQL servers, and the databases security options would still apply?

    feel free to tell me how wrong i am 😀

  • August 13, 2009 at 4:05 pm

    #1039025

    when ever a database is restored on new server the users get transferred with the database and their security options. however they are orpahned from their logins because of different sid. instaed of dropping the user and recreating it one can fix the users the following way

    Thsi will give orpahned users in current database

    USE DATABASENAME.

    EXEC sp_change_users_login 'Report'

    This command if login already exists on new server

    EXEC sp_change_users_login 'UPDATE_ONE','USERNAME','LOGINNAME'

    This command if login does not exists on the new server

    EXEC sp_change_users_login 'Auto_Fix', 'USERNAME', 'LOGINNAME', 'PASSWORD'

Viewing 4 posts - 1 through 3 (of 3 total)

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