| Author |
 Topic  |
|
|
steve_r18
Yak Posting Veteran
Canada
59 Posts |
 Posted - 01/25/2012 : 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
USA
35007 Posts |
|
|
steve_r18
Yak Posting Veteran
Canada
59 Posts |
Posted - 01/25/2012 : 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
USA
35007 Posts |
Posted - 01/25/2012 : 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
USA
35007 Posts |
|
|
steve_r18
Yak Posting Veteran
Canada
59 Posts |
Posted - 01/25/2012 : 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
USA
35007 Posts |
|
|
steve_r18
Yak Posting Veteran
Canada
59 Posts |
Posted - 01/25/2012 : 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
USA
35007 Posts |
|
|
steve_r18
Yak Posting Veteran
Canada
59 Posts |
Posted - 01/25/2012 : 13:48:35
|
See above.
Steve |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
steve_r18
Yak Posting Veteran
Canada
59 Posts |
Posted - 01/25/2012 : 13:56:16
|
Is there a SP that hold a value for password complexity and turning it off?
Steve |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 01/25/2012 : 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
Canada
59 Posts |
Posted - 01/25/2012 : 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
Canada
59 Posts |
Posted - 01/25/2012 : 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
USA
35007 Posts |
|
| |
Topic |
|
Moving a database and Its user
