restore database sql 2005 to new server

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/

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/

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

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/

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

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources