October 3, 2006 at 12:04 pm
Hi folks !
I'm planning to reinstall our sql 2k server, windows 2k server with windows 2k3 server, sql srv 2005 developer edition.
I have actually no experience with new ver. of sql server, therefore I need your advices.
1. I have to move users logins with their passwords.
2. Install data files on separate disk (raid5), not on system disk.
At the install process I saw any option to set data folder into any other location, how can I place all data files on separate disk? (I mean system databases)
I tryed new SSIS function in sql 2005, and used tool for transfer logins from sql 2000 - dts package, but this action ended always with error. (each time with another result)
3. what is the difference between database compatibleness level 80 and 90 ? We are using application wrote in powerbuilder, but in cmpt level 90 apllication still ends with error result.
thanx for your time and advices !
October 3, 2006 at 12:36 pm
Hi -
You can transfer logins with the procedure sp_help_revlogin, which you will find in a MS KB article; just search microsoft.com for that text, or for "moving SQL Server databases".
The compatibility level makes the database "appear" to the client software as if it were the old version of SQL server. 80 is SQL Server 2000 and 90 is SQL Server 2005. If you want few or no changes to be necessary in the client, then you will probably need to run the 80 compatibility mode. You do lose some features of 2005 that way, but it is more likely to work.
Location of user DB files can be handled in the restore-from-backup process, directing the data and log files for each resored DB wherever you want. In T-SQL, that's RESTORE ... WITH MOVE ...; in SSMS, it's on a panel of the Restore dialog box.
HTH
October 3, 2006 at 12:58 pm
thanx,
hmmm actually I now what cmpt. level 80 and 90 does mean, but what I'd like to know is, what is the basical difference between those levels. I would like to find out why is this level 90 generating errors in application, and what should I have to change (reprogram) to solve this incompatibility issue.
October 5, 2006 at 7:12 am
I don't know what I am actually doing wrong, I tryed to transfer logins using stored procedure sp_help_revlogin >
http://support.microsoft.com/kb/246133/EN-US/
but when I run generated skript for creating users account this error message arise:
Msg. 15021, level 16, state 1, line 1
Invalid value given for parameter PASSWORD
October 5, 2006 at 7:44 am
This is the script I use to script out SQL Server id/passwords. I save this off for disaster recovery purposes AND to move logins between SQL Servers. The only requirment is that on the target server that the default databases be there. You can simply create a blank db until you can restore the real db.
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
October 5, 2006 at 11:42 am
Thanks Markus, I will try your script tomorrow.
I found out that this error causes only user's accounts without passwords (blank passwords). I think that this issue is a blame of new sql srv. 2005 security policy. It don't accept creating null passwords.
I hope, that this stupid password policy may be disabled !
October 6, 2006 at 6:29 am
Markus, your scipt did not work ! The result was the same. This script do not solve the problem with null password.
I used original script from page:
http://support.microsoft.com/kb/246133/EN-US/
and I made a little modification, so now the user who has set his password in sql2000, will get the same in sql server 2005 and user, who has "null" password, will get in sql 2005 password: heslo
If you wish, you can change this default password by creating new user account with new password in sql 2000. Then paste and copy his hash code into section "Intelsoft". All null password users will get your default psw.
script:
----- Begin Script, Create sp_help_revlogin procedure -----
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(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
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 @default_pwd varchar(256)
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
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 ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
/* Intelsoft */ ---------------------------------------------------------------------------------------------
-- here you can change default hash password:
SET @default_pwd = 'SET @pwd = CONVERT (varbinary(256), 0x0100043ACB5DAE5FE581CBEA89DE16689FE1646FC5624C3480DE4E185E4CB353715B99D1A1CE5E027D71DE961181)'
PRINT @default_pwd
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
/* Intelsoft */ ---------------------------------------------------------------------------------------------
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
run with:
EXEC master..sp_help_revlogin
October 9, 2006 at 6:58 am
Hum... I guess I have never had a null password.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply