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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 restore database sql 2005 to new server
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

koenhuys
Starting Member

Belgium
3 Posts

Posted - 12/09/2014 :  14:49:00  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 12/09/2014 :  14:50:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
38200 Posts

Posted - 12/09/2014 :  14:51:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Belgium
3 Posts

Posted - 12/10/2014 :  03:37:59  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 12/10/2014 :  12:44:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Belgium
3 Posts

Posted - 12/11/2014 :  03:24:59  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 12/11/2014 :  12:36:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000