Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 restore database sql 2005 to new server

Author  Topic 

koenhuys
Starting Member

3 Posts

Posted - 2014-12-09 : 14:49:00
I restored a database to a new server but now I see with the user of my database that the login is empty and grey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-09 : 14:50:47
You can use this script to generate the logins on the old server:
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0

Copy the output from the old server, paste into new server and run. But make sure you delete any logins that shouldn't be transferred.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-09 : 14:51:34
By using this script, or a similar script, you are grabbing the login name, password AND sid. If you don't grab the sid, then you'd have to unorphan the accounts.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

koenhuys
Starting Member

3 Posts

Posted - 2014-12-10 : 03:37:59
Thanks for the assist but at the end you are telling me the result just has to be copy and past into a query and run it
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-10 : 12:44:20
Yes. Run the script on the source server. Copy the output to a new query window that is connected to the destination server. Run it. The logins should now be created on the destination server. Please note that you'll need to edit the script as there'll be logins you don't want to copy over.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

koenhuys
Starting Member

3 Posts

Posted - 2014-12-11 : 03:24:59
Dear sir, thanks for the assist

but I found it with the below query and this works

USE [database];
Go
sp_change_users_login @Action= 'update_one', @UserNamePattern=[user],
@LoginName=[loginname];
GO
have a nice day
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-11 : 12:36:00
That works too but is not the preferred method since it has to fix the sids. That's the "unorphan" thing I mentioned.

And no "sir" here.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -