Page 3 of 3 FirstFirst 123
Results 31 to 35 of 35

Thread: Restoring database on a different Server does not allow users

  1. #31
    Thank you very much! Guest

    Restoring database on a different Server does not allow users (reply)




    ------------
    Thank you very much! at 8/27/99 7:38:19 PM




    ------------
    Craig at 8/27/99 1:13:07 PM

    What is your question?


    ------------
    Sri Kasam at 8/26/99 2:43:58 PM

    Hi,
    When you restore a database from one server to another, there are a couple of things to take care of.

    1. Standard SQL Server logins -> If these don't exist already or if they have mismatched sids from database..sysusers, a system stored procedure sp_change_users_login will take care of it

    2. Integrated logins : a) Domain Users/Groups b) Local Users/Groups

    In this case there are three possible solutions.. i) You can create the logins if they don't already exist and then Update database..sysusers with the sid of the login you just created

    This is not a recommended approach as I don't want to update system tables, especially as this is such a frequent problem and there should be a better approach.

    ii) You can do sp_dropuser, sp_addlogin and then sp_adduser for the login that you just created... This will not work if there are objects owned by this user

    iii) You can just create the logins for Domain Users/Groups and their sids will automatically match to the ones in sysusers as they are global sids This will definitely not solve my problem with local users/groups Also, how do I find from sysusers, which NT user/group is Local or Global??

    Any insight would be greatly appreciated!!

    Thanks!

    Sincerely , Sri Kasam (MCSE)






  2. #32
    Thank you very much! Guest

    Restoring database on a different Server does not allow users (reply)




    ------------
    Thank you very much! at 8/27/99 7:38:19 PM




    ------------
    Craig at 8/27/99 1:13:07 PM

    What is your question?


    ------------
    Sri Kasam at 8/26/99 2:43:58 PM

    Hi,
    When you restore a database from one server to another, there are a couple of things to take care of.

    1. Standard SQL Server logins -> If these don't exist already or if they have mismatched sids from database..sysusers, a system stored procedure sp_change_users_login will take care of it

    2. Integrated logins : a) Domain Users/Groups b) Local Users/Groups

    In this case there are three possible solutions.. i) You can create the logins if they don't already exist and then Update database..sysusers with the sid of the login you just created

    This is not a recommended approach as I don't want to update system tables, especially as this is such a frequent problem and there should be a better approach.

    ii) You can do sp_dropuser, sp_addlogin and then sp_adduser for the login that you just created... This will not work if there are objects owned by this user

    iii) You can just create the logins for Domain Users/Groups and their sids will automatically match to the ones in sysusers as they are global sids This will definitely not solve my problem with local users/groups Also, how do I find from sysusers, which NT user/group is Local or Global??

    Any insight would be greatly appreciated!!

    Thanks!

    Sincerely , Sri Kasam (MCSE)






  3. #33
    Thank you very much! Guest

    Restoring database on a different Server does not allow users (reply)




    ------------
    Thank you very much! at 8/27/99 7:38:19 PM




    ------------
    Craig at 8/27/99 1:13:07 PM

    What is your question?


    ------------
    Sri Kasam at 8/26/99 2:43:58 PM

    Hi,
    When you restore a database from one server to another, there are a couple of things to take care of.

    1. Standard SQL Server logins -> If these don't exist already or if they have mismatched sids from database..sysusers, a system stored procedure sp_change_users_login will take care of it

    2. Integrated logins : a) Domain Users/Groups b) Local Users/Groups

    In this case there are three possible solutions.. i) You can create the logins if they don't already exist and then Update database..sysusers with the sid of the login you just created

    This is not a recommended approach as I don't want to update system tables, especially as this is such a frequent problem and there should be a better approach.

    ii) You can do sp_dropuser, sp_addlogin and then sp_adduser for the login that you just created... This will not work if there are objects owned by this user

    iii) You can just create the logins for Domain Users/Groups and their sids will automatically match to the ones in sysusers as they are global sids This will definitely not solve my problem with local users/groups Also, how do I find from sysusers, which NT user/group is Local or Global??

    Any insight would be greatly appreciated!!

    Thanks!

    Sincerely , Sri Kasam (MCSE)






  4. #34
    Thank you very much! Guest

    Restoring database on a different Server does not allow users (reply)




    ------------
    Thank you very much! at 8/27/99 7:38:19 PM




    ------------
    Craig at 8/27/99 1:13:07 PM

    What is your question?


    ------------
    Sri Kasam at 8/26/99 2:43:58 PM

    Hi,
    When you restore a database from one server to another, there are a couple of things to take care of.

    1. Standard SQL Server logins -> If these don't exist already or if they have mismatched sids from database..sysusers, a system stored procedure sp_change_users_login will take care of it

    2. Integrated logins : a) Domain Users/Groups b) Local Users/Groups

    In this case there are three possible solutions.. i) You can create the logins if they don't already exist and then Update database..sysusers with the sid of the login you just created

    This is not a recommended approach as I don't want to update system tables, especially as this is such a frequent problem and there should be a better approach.

    ii) You can do sp_dropuser, sp_addlogin and then sp_adduser for the login that you just created... This will not work if there are objects owned by this user

    iii) You can just create the logins for Domain Users/Groups and their sids will automatically match to the ones in sysusers as they are global sids This will definitely not solve my problem with local users/groups Also, how do I find from sysusers, which NT user/group is Local or Global??

    Any insight would be greatly appreciated!!

    Thanks!

    Sincerely , Sri Kasam (MCSE)






  5. #35
    Thank you very much! Guest

    Restoring database on a different Server does not allow users (reply)

    Hi Craig,
    My problem is I want the users in the user database that I just restored
    on to a different servers to access this new database.
    1. There are three types of logins..
    a) Standard logins -> taken care of, by sp_change_users_login sys proc.
    b) Global Users/Groups -> Will be taken care of by creating these on the
    New Server as the sids are got from the PDC/BDC
    c) Local Users/Groups -> These are the problem
    If I don't want to manipulate the system tables master..sysxlogins
    and userdb..sysusers, how else can I get around it..

    Thank you very much!

    Sincerely ,
    Sri Kasam

    ------------
    Craig at 8/27/99 1:13:07 PM

    What is your question?


    ------------
    Sri Kasam at 8/26/99 2:43:58 PM

    Hi,
    When you restore a database from one server to another, there are a couple of things to take care of.

    1. Standard SQL Server logins -> If these don't exist already or if they have mismatched sids from database..sysusers, a system stored procedure sp_change_users_login will take care of it

    2. Integrated logins : a) Domain Users/Groups b) Local Users/Groups

    In this case there are three possible solutions.. i) You can create the logins if they don't already exist and then Update database..sysusers with the sid of the login you just created

    This is not a recommended approach as I don't want to update system tables, especially as this is such a frequent problem and there should be a better approach.

    ii) You can do sp_dropuser, sp_addlogin and then sp_adduser for the login that you just created... This will not work if there are objects owned by this user

    iii) You can just create the logins for Domain Users/Groups and their sids will automatically match to the ones in sysusers as they are global sids This will definitely not solve my problem with local users/groups Also, how do I find from sysusers, which NT user/group is Local or Global??

    Any insight would be greatly appreciated!!

    Thanks!

    Sincerely , Sri Kasam (MCSE)






Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •