September 12, 2007 at 1:14 pm
Ok, using SQL 2000 for moving a server, I use to create the users on the new server and then just create a dummy database in preperation for the move of a database. I would then just restore over the top of the dummy database with the production database and I was all set. Since all the users were aleady there and were already assigned to the dummy database that is the same exact name as the production database, the restored database was perfectly in sync and the users's never new it was moved.
Now, with SQL 2005, I do the same thing, or even just restore a new copy of the database over to a test or new server were the login's already exist and are already assigned to the database of the same name and it does not work. The user's connect but can't get to the database, and if the database is there default, they can't connect becuase there is an error connecting to there default database. I have to go in, remove the schema's and users from the database and then go and re-assign them to the database.
Why is this happening, I would think that it should work the same as 2000 did, what has changed that is making me change the way I have done restore's.
September 12, 2007 at 4:43 pm
Robert,
Do you know the sp_change_users_login SP? It'll be your good friend. This way you can fix this problem even without creating the logins in advance. I'm just guessing, but your problem may originates from a security enhancement, and SQL2005 checks the SIDs as well. Since they're autogenerated, there's not too much chance to make them match. And - what a pity - you can't hack it via system tables, because you don't have the option to modify system tables. So try sp_change_users_login 'Auto_Fix', 'username' - maybe you can leave out username, I'm not sure. Check it in BOL .
-- Erik http://blog.rollback.hu
September 12, 2007 at 11:32 pm
Here is what I use to Migrate Databases from 2005 server to 2005 Server. This script will not work on SQL2000. This Code is from the MSDN SQLServer site, but it needed a couple of tweeks
step 1 Run the following two scripts on the Master Database of the Leaving Server, Yes Backup Master before you start and you must be logged on as a system Administrator.
This will not tell you what the password are just what the hashed output is.
Step 2 run EXEC sp_help_revlogin in the SQL Management Studio (Query Analyser)
It should produce output like the following
-- Login: admin.sa
CREATE LOGIN [admin.sa] WITH PASSWORD = 0x01003DA6E9296E4552028046BDC2CC130613BD570C080A5DE959 HASHED, SID = 0x23E00A11882CBC46BDE0C36DD5A121DE, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
-- Login: MYDomain\Fred.Nerk
CREATE LOGIN [MYDomain\Fred.Nerk] FROM WINDOWS
-- Login: MyServer\SQLServer2005MSSQLUser$MyServer$MSSQLSERVER
CREATE LOGIN [MyServer\SQLServer2005MSSQLUser$MyServer$MSSQLSERVER] FROM WINDOWS
Step Three
Cut and paste the resultant scripts -- like above -- on to the Target server.
Step four
restore your database from the leaving server to the target server. The logins will alreay be there with the same SID as the Leaving server.
All done
Hope that helps. remember backup up always and often
CodeOn
--SCRIPT START
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin
FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin
FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked =
CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked =
CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name )
+ ' WITH PASSWORD = ' + @PWD_string
+ ' HASHED, SID = ' + @SID_string
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login has exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
--SCRIPT STOP
September 13, 2007 at 12:01 am
As already mentioned sp_change_users_login is what you need to sync the db-user-sid's to the server user-sid.
To script the SQL2000 users with their passwords use this on your sql2000 side:
select 'exec sp_addlogin ['
+ name
+ '],'
, password
, ', @encryptopt=skip_encryption'
from master..sysxlogins
Run it to generate the addlogin statements, copy / paste the results from sql2000 to your sql2005 and run on your sql2005 bos
To sync the users at SQL2005 database side (each database separately !)
print 'print @@servername + '' / '' + db_name()'
print 'go'
go
declare @username varchar(128)
declare @Musername varchar(128)
declare @IsNtName bit
declare @sql_stmt varchar(500)
declare @ExcludeWindowsAccounts Char(1)
set @ExcludeWindowsAccounts = 'N' -- Y/N for windows accounts wil
--cursor returns with names of each username to be tied to its respective
DECLARE user_cursor CURSOR FOR
SELECT su.name as Name, msu.name as MasterName , su.isntname
FROM sysusers su
left join master.dbo.sysxlogins msu
on upper(su.name) = upper(msu.name)
WHERE su.sid > 0x00
ORDER BY Name
--for each user:
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username NOT IN ('dbo', 'list of names you want to avoid')
BEGIN
if @Musername is null
begin
if @IsNtName = 1
begin
if @ExcludeWindowsAccounts = 'N'
begin
print 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomein**\' + @username + ''')'
print ' begin '
print ' exec sp_grantlogin N''NtDomein**\' + @username + ''''
print ' exec sp_defaultdb N''NtDomein**\' + + @username + ''', N'''+ db_name() + ''''
print ' end'
set @sql_stmt = '--Windows account gehad'
end
else
begin
set @sql_stmt = '--'
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = ''' + @username + ''''
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' + @username + ''''
end
PRINT @sql_stmt
print 'go'
print '--*** Warning: exec stmt commented !!! ***'
--EXECUTE (@sql_stmt)
END
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
END --of table-cursor loop
--clean up
CLOSE user_cursor
DEALLOCATE user_cursor
Print '** end User-synchronisation **'
Copy /paste the results and run for user synchronisation.
Need to be executed on every db of the server !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 13, 2007 at 8:19 am
Thanks for the replies everyone! I used the sp_change_users_login with the following script, because it does require you to put the username you wish to fix with the auto_fix action.
set
nocount on
create
table #tmpuserfix (
UserName
varchar(50),
UserSID
varbinary(85))
insert
into #tmpuserfix execute sp_change_users_login report
declare
@usernametofix varchar(50)
declare
usercur cursor local static
for
select UserName from #tmpuserfix
open
usercur
fetch
next from usercur into @usernametofix
while
@@fetch_status = 0
begin
exec
sp_change_users_login 'auto_fix', @usernametofix
fetch
next from usercur into @usernametofix
end
close
usercur
deallocate
usercur
drop
table #tmpuserfix
September 13, 2007 at 8:23 am
The above did work like a charm because the report action shows the ones that need fixing.
Why does this reply page have such big spacing between lines.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply