SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Moving a database and Its user
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

steve_r18
Yak Posting Veteran

Canada
59 Posts

Posted - 01/25/2012 :  11:59:58  Show Profile  Reply with Quote
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

USA
37287 Posts

Posted - 01/25/2012 :  13:12:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

steve_r18
Yak Posting Veteran

Canada
59 Posts

Posted - 01/25/2012 :  13:15:58  Show Profile  Reply with Quote
Sorry how can I accomplish this again? Is it just modifying the ID in a user table?

Steve
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 01/25/2012 :  13:21:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 01/25/2012 :  13:21:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

steve_r18
Yak Posting Veteran

Canada
59 Posts

Posted - 01/25/2012 :  13:43:30  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 01/25/2012 :  13:45:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

steve_r18
Yak Posting Veteran

Canada
59 Posts

Posted - 01/25/2012 :  13:45:46  Show Profile  Reply with Quote
EXEC sp_addlogin @loginame = 'ERA',
@defdb = 'master',
@deflanguage = 'us_english',
@encryptopt = 'skip_encryption',
@passwd =NULL,
@sid =0x5FDD21DAE5A58141B5639DCB3D48183C

Steve
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 01/25/2012 :  13:46:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

steve_r18
Yak Posting Veteran

Canada
59 Posts

Posted - 01/25/2012 :  13:48:35  Show Profile  Reply with Quote
See above.

Steve
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 01/25/2012 :  13:52:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

steve_r18
Yak Posting Veteran

Canada
59 Posts

Posted - 01/25/2012 :  13:56:16  Show Profile  Reply with Quote
Is there a SP that hold a value for password complexity and turning it off?

Steve
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 01/25/2012 :  14:03:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 01/25/2012 :  14:04:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

steve_r18
Yak Posting Veteran

Canada
59 Posts

Posted - 01/25/2012 :  14:22:12  Show Profile  Reply with Quote
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
Go to Top of Page

steve_r18
Yak Posting Veteran

Canada
59 Posts

Posted - 01/25/2012 :  14:23:04  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 01/25/2012 :  14:54:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.14 seconds. Powered By: Snitz Forums 2000