Moving a database and Its user

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

General SQL Server Forums

New to SQL Server Administration

Moving a database and Its user

Author

Topic

steve_r18
Yak Posting Veteran

59 Posts

Posted - 2012-01-25 : 11:59:58

When I restore a db to a new server the users are still attached (security). Is there a way to assign SQL security with a script with existing permission sets on that database or is it better to just remove the user, do the backup and move then re add the user to both the DB and SQL?

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 13:12:05

The permissions come across with the restore. So you would just create the logins and then sync them up with the users inside the database (unorphan accounts) or you'd create them with the same sid. So there is no need to re-create permissions. You just need to link the login with the user.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

steve_r18
Yak Posting Veteran

59 Posts

Posted - 2012-01-25 : 13:15:58

Sorry how can I accomplish this again? Is it just modifying the ID in a user table?

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 13:21:17

Run the below command on the source server and copy its output into a new query window on the new 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

Before running the output on the new server, edit out the ones you don't care about. There'll likely be a bunch of system stuff you don't want. Once the script is ready with the ones you want, hit F5 to execute it. This will set the sids to the same sid on the source server, so there is no need to unorphan the accounts. I prefer this method greatly.

Once you do this, you are all set. Permissions are good to go.

Now if you already had the logins on the new server, remove them so that this process can work. You will only ever have to do this once. If you ever need to do subsequent restores here, you don't need to do these steps again.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 13:21:32

By the way, this script is from here: http://weblogs.sqlteam.com/tarad/archive/2008/06/24/How-to-transfer-SQL-logins-between-SQL-Server-2005-instances.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

steve_r18
Yak Posting Veteran

59 Posts

Posted - 2012-01-25 : 13:43:30

I'm getting "Invalid value given for parameter PASSWORD. Specify a valid parameter value."

by default NULL is there, however I know the associated password with this account. When I enter it it doesn't seem to like the syntax.

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 13:45:13

Show me the output. Sometimes you have to edit the rows returned down.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

steve_r18
Yak Posting Veteran

59 Posts

Posted - 2012-01-25 : 13:45:46

EXEC sp_addlogin @loginame = 'ERA',
@defdb = 'master',
@deflanguage = 'us_english',
@encryptopt = 'skip_encryption',
@passwd =NULL,
@sid =0x5FDD21DAE5A58141B5639DCB3D48183C

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 13:46:07

You can email it to me if you'd like or post it here. The passwords are encrypted.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

steve_r18
Yak Posting Veteran

59 Posts

Posted - 2012-01-25 : 13:48:35

See above.

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 13:52:40

Interesting, I've never seen it do that before. Add the password like this since you know what it is:

EXEC sp_addlogin @loginame = 'ERA',
@defdb = 'master',
@deflanguage = 'us_english',
@passwd = 'PasswordGoesHere',
@sid =0x5FDD21DAE5A58141B5639DCB3D48183C

Because we are going to type it in clear text, we have to remove the encrypt parameter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

steve_r18
Yak Posting Veteran

59 Posts

Posted - 2012-01-25 : 13:56:16

Is there a SP that hold a value for password complexity and turning it off?

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 14:03:11

As far as I know, it's done at the Active Directory level and SQL inherits it. This is an area I'm not well versed in.

Now we can override it if we use ALTER LOGIN instead of sp_addlogin. You would specify OFF for CHECK_POLICY.

But this is getting a bit too involved, instead just create the login manually. Let me get you the unorphan script to run.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 14:04:36

After you create the login manually on the new server, run this script for the restored database on the new server:



USE DatabaseNameGoesHere
GO

DECLARE @SQL VARCHAR(100)

DECLARE curSQL CURSOR FOR
	SELECT 'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + name + ''', ''' + name + ''''
	FROM sysusers
	WHERE issqluser = 1 AND name NOT IN ('INFORMATION_SCHEMA', 'sys', 'guest', 'dbo')

OPEN curSQL

FETCH curSQL INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC (@SQL)
	FETCH curSQL INTO @SQL
END

CLOSE curSQL
DEALLOCATE curSQL

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

steve_r18
Yak Posting Veteran

59 Posts

Posted - 2012-01-25 : 14:22:12

Ok so I used the original command, used a more 'complex' password'. It was successfull, I then changed the requirements for this user and it updated with no issues.

Steve

steve_r18
Yak Posting Veteran

59 Posts

Posted - 2012-01-25 : 14:23:04

You really are an SQL Goddess. Many thanks Tara. It's people like you who give us 'little guys' hope at dipping deeper into SQL. Thanks again!

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 14:54:59

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources