How to RESTORE USERS PASSWORDS in MS SQL 2K when you restore db from a bkup?

How to RESTORE USERS PASSWORDS in MS SQL 2K when you restore db from a bkup?Please eead up on sp_change_users_login within Books Online (within the =
SQL Server program group).
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:6B76231F-ED2C-458B-88F9-ED10BF5BF5DD@.microsoft.com...
>
|||Hi,
Users password :
All the SQL Server logins and passwords are stored in MASTER database. SO if
you restore a database independently to a new server the
Login names will not be created. To syncronize you have to execute the
system procedure in your newly restored database,
sp_change_users_login (Refer books online for details and parameters)
Thanks
Hari
MCDBA
"ktf" <anonymous@.discussions.microsoft.com> wrote in message
news:6B76231F-ED2C-458B-88F9-ED10BF5BF5DD@.microsoft.com...
>
|||The passwords are encrypted. How am I be able to restore the password? How sp_change_users_login can help me? What steps should I take? Because of corruption caused by the replication software, last time I had to restore from backup on the same box and I
had to give a generic password to all users and that was a security issue. Will remapping logins do it for me? Does the Microsoft have any solution to restore passwords? I believe this is a very important issue. How can Microsoft help me (correct me if I
am wrong!) I know passwords are encrypted and the pattern is changing every time!
Thank you
|||From Books Online:
sp_change_users_login
New Information - SQL Server 2000 SP3.
Maps an existing user in a database to a Microsoft=C2=AE SQL =
Server=E2=84=A2 login.
Syntax
sp_change_users_login [ @.Action =3D ] 'action'=20
[ , [ @.UserNamePattern =3D ] 'user' ]=20
[ , [ @.LoginName =3D ] 'login' ]
[ , [ @.Password =3D ] 'password' ]
Arguments
[@.Action =3D] 'action'
Describes the action to be performed by the procedure. action is =
varchar(10), and can be one of these values.
Value Description=20
Auto_Fix Links a user entry in the sysusers table in the current =
database to a login of the same name in sysxlogins. You should check the =
result from the Auto_Fix statement to confirm that the correct link is =
in fact made. Avoid using Auto_Fix in security-sensitive situations.=20
When using Auto_Fix, you must specify user and password; login =
must be NULL. user must be a valid user in the current database.
=20
Report Lists the users and corresponding security identifiers =
(SID) in the current database that are not linked to any login.=20
user, login, and password must be NULL or not specified.
=20
Update_One Links the specified user in the current database to =
login. login must already exist. user and login must be specified. =
password must be NULL or not specified.=20
[@.UserNamePattern =3D] 'user'
Is the name of a SQL Server user in the current database. user is =
sysname, with a default of NULL.=20
[@.LoginName =3D] 'login'
Is the name of a SQL Server login. login is sysname, with a default of =
NULL.=20
[@.Password =3D] 'password'
Is the password assigned to a new SQL Server login created by Auto_Fix. =
If a matching login already exists, the user and login are mapped and =
password is ignored. If a matching login does not exist, =
sp_change_users_login creates a new SQL Server login and assigns =
password as the password for the new login. password is sysname, with a =
default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
Column name Data type Description=20
UserName sysname Login name.=20
UserSID varbinary(85) Login security identifier.=20
Remarks
Use this procedure to link the security account for a user in the =
current database with a login. If the login for a user has changed, use =
sp_change_users_login to link the user to the new login without losing =
the user's permissions.
sp_change_users_login can be used only for SQL Server logins; it cannot =
be used with Windows logins.
login cannot be sa, and user cannot be the dbo, guest, or =
INFORMATION_SCHEMA users.
sp_change_users_login cannot be executed within a user-defined =
transaction.
Permissions
Any member of the public role can execute sp_change_users_login with the =
Report option. Only members of the sysadmin fixed server role can =
specify the Auto_Fix option. Only members of the sysadmin or db_owner =
roles can specify the Update_One option.
Examples
A. Show a report of the current user to login mappings
This example produces a report of the users in the current database and =
their security identifiers.
EXEC sp_change_users_login 'Report'
B. Change the login for a user
This example changes the link between user Mary in the pubs database and =
the existing login, to the new login NewMary (added with sp_addlogin).
--Add the new login.
USE master
go
EXEC sp_addlogin 'NewMary'
go
--Change the user account to link with the 'NewMary' login.
USE pubs
go
EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'
C. Automatically map a user to a login, creating a new login if =
necessary
This example shows how to use the Auto_Fix option to map an existing =
user to a login with the same name, or create the SQL Server login Mary =
with the password B3r12-36 if the login Mary does not exist.
USE pubs
go
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
go
See Also
sp_addlogin
sp_adduser
sp_helplogins
System Stored Procedures
=C2=A91988-2004 Microsoft Corporation. All Rights Reserved.
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:2F5367FC-6951-416F-9132-FE145823FD55@.microsoft.com...
>=20
> The passwords are encrypted. How am I be able to restore the password? =
How sp_change_users_login can help me? What steps should I take? =
Because of corruption caused by the replication software, last time I =
had to restore from backup on the same box and I had to give a generic =
password to all users and that was a security issue. Will remapping =
logins do it for me? Does the Microsoft have any solution to restore =
passwords? I believe this is a very important issue. How can Microsoft =
help me (correct me if I am wrong!) I know passwords are encrypted and =
the pattern is changing every time! =20
> Thank you
>
|||I had the old one and just updated mine. Thank you
|||But we want to use the same passwords and do not create new ones (that is a security violation) this article applies for when I want to put new passwords not reuse the existing ones. (Correct me if I am wrong)
Thank you,
ktf
|||What do you mean by "that is a security violation?"
This example (from Books Online) allows you to fix Mary's login and set =
the password to 'B3r12-36'. Isn't that what you want to do?
C. Automatically map a user to a login, creating a new login if =
necessary
This example shows how to use the Auto_Fix option to map an existing =
user to a login with the same name, or create the SQL Server login Mary =
with the password B3r12-36 if the login Mary does not exist.
USE pubs
go
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
go
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:9289E8DF-FEA8-4B17-8DFD-05FB2E3DE6A8@.microsoft.com...
> But we want to use the same passwords and do not create new ones (that =
is a security violation) this article applies for when I want to put new =
passwords not reuse the existing ones. (Correct me if I am wrong)
> Thank you,
> ktf
>
|||I meant even me should not know about the users passwords, in event of restoring a database I am not allowed to create new passwords. All users should be able to use their original passwords to login.
|||Can you give them an arbitrary password that they will set the first =
time they log in?
You may also find this helpful:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=3D246133
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:891BB281-C620-4CDE-B08A-A8C526FEFA7E@.microsoft.com...
>=20
> I meant even me should not know about the users passwords, in event of =
restoring a database I am not allowed to create new passwords. All users =
should be able to use their original passwords to login.
>