SQL Server Logon Problem

[U079310]

Problem with SQL logon in a new instance of SQL Server where we installed GP. Have 2 instances of SQL server, A and B. We take a db backup from A and restore into instance B.

Next we launch GP which is pointed to instance B. The SA user can logon without any problem. However, all other users get the msg from GP that "There is not a valid logon on the server for the userid and password you entered ..."

It seems like the user record and logon that we restored into B from instance A are not being recognized. Shouldn't this work ok ?

 

[beleyrs]

No, you need to synchronize the users back to the master database on your server. SA is inherent to the server so it will always be ok.

Example, you have a login with username "joesmith".

joesmith either does exist in your destination server, or does not. if he does not, you need to add him. then you need to synchronize him with your database.

Example of add:
-- Login: joesmith
SET @pwd = CONVERT (varbinary(256), 0x213121402A315B3D545B55235F523956)
EXEC master..sp_addlogin 'joesmith', @pwd, @sid = 0x1121F8DC325CD61189020002B364D8D6, @encryptopt = 'skip_encryption'

You can get a list of these logins by executing a stored procedure called sp_help_revlogin on your souce database. when you execute the procedure (from MASTER), it outputs all ofyour logins in the format above.

Example of synchronization (run against your database in query analyzer):
exec sp_change_users_login 'update_one','joesmith','joesmith'

This will un-orphan the user.

Basically, when your restore, the users become orphans if they don't already exist in the master database, so you have to add them, and relink them.

Good luck!

 

[VictoriaYudin]

There are also scripts, specific to GP, to copy logins from one server to another. Take a look at KB 878449:

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;878449.