|
Home > Archive > MS SQL Server > January 2006 > user accounts after restore in SQL 2000
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
user accounts after restore in SQL 2000
|
|
|
| We have 2 SQL servers: PROD and TEST. Obviously, the TEST is a test
environment for PROD. Both of them identical and have same user accounts with
same permissions. When there is a need to backup PROD and restore it to TEST
are there any manipulations with user accounts need to be done?
My superior says that after restoring database in TEST, user accounts have
to be deleted by opening Database/Users (quote: not from Security/Logins),
then they have to be deleted from Security/Logins, and finally, they have to
be recreated from Database/Users/New Database user and same permissions as
before applied. He says that after restoring a database, user accounts "don't
get linked to the database properly".
I think that there is nothing to worry about after restoration since those
accounts already existed on TEST, so they all should work just fine.
Can someone knowledgeable give some "scientific" explanation to this?
| |
| Tom Moreau 2006-01-19, 8:23 pm |
| Check out sp_change_users_logi
n in the BOL.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Vasya" <Vasya@discussions.microsoft.com> wrote in message
news:34FBBCBF-1D87-4D06-AB3A- 6F0CF9654A61@microso
ft.com...
We have 2 SQL servers: PROD and TEST. Obviously, the TEST is a test
environment for PROD. Both of them identical and have same user accounts
with
same permissions. When there is a need to backup PROD and restore it to TEST
are there any manipulations with user accounts need to be done?
My superior says that after restoring database in TEST, user accounts have
to be deleted by opening Database/Users (quote: not from Security/Logins),
then they have to be deleted from Security/Logins, and finally, they have to
be recreated from Database/Users/New Database user and same permissions as
before applied. He says that after restoring a database, user accounts
"don't
get linked to the database properly".
I think that there is nothing to worry about after restoration since those
accounts already existed on TEST, so they all should work just fine.
Can someone knowledgeable give some "scientific" explanation to this?
| |
|
| I did. The key word is "MAY".
If you restore a database on a different instance of SQL Server than the one
on which the backup was created, you MAY need to run sp_change_users_logi
n to
update user login information. For more information, see
sp_change_users_logi
n.
May or may not. The instance is different, but it has absolutely same
account as the server on which the backup was created... After restoring, I
can see that the account is there with correct permissions... Will it work?
| |
| Geoff N. Hiten 2006-01-20, 3:23 am |
| "Account" doesn't really have a meaning within SQL Server. You have logins,
which allow you to connect to the server, and users, which control
permissions within a database. Users and Logins are linked via Security
Identifiers (SIDs). When you restore a database to a new environment, the
users and corresponding permissions permissions exist as they were on the
source server. The users are no longer linked to particular logins, even if
the logins and users have the same names. This is intentional to avoid
accidentally granting access to a restored database. sp_change_users_logi
ns
allows you to link an existing database user to a server login and is
specifically intended for use in cross-server restores
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
..
"Vasya" <Vasya@discussions.microsoft.com> wrote in message
news:4098ED48-5B7C-4AA0-A244- 89AB3EE547ED@microso
ft.com...
>I did. The key word is "MAY".
>
> If you restore a database on a different instance of SQL Server than the
> one
> on which the backup was created, you MAY need to run sp_change_users_logi
n
> to
> update user login information. For more information, see
> sp_change_users_logi
n.
>
> May or may not. The instance is different, but it has absolutely same
> account as the server on which the backup was created... After restoring,
> I
> can see that the account is there with correct permissions... Will it
> work?
|
|
|
|
|