Migrate SQL 2000 Logins to SQL 2005
-
What is the best way to migrate SQL 2000 "SQL Logins" to SQL 2005?
Should we script out the Login info first (using the REVLOGIN proc) then run this script on the target SQL 2005 SQL Server?
We are experiencing some weird errors using this apporach involving the schema, owner, etc.. Any detailed approach is appreciated
BT -
I think you need to separate the issues of transferring SQL logins from the issues of database users.
If you send me your email address, I will send you our proc for transferring SQL logins.
When you grant use of a database to a login, SQL2005 will create a Schema object with the same name as the database login, and this becomes the default schema for that user.
You can then change the default schema for the user (e.g. to DBO), and if required delete the superfluous Schema object. Unfortunately it is not possible with SSMS to grant database access to a user and specify a specific schema name that user should use.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Ed - much appreciated.. you can eMail the script at "webmaster @ skippackridge.com" (I seperated my eMail address here to avoid spam!! Please parse back together)
thx in advance
BT -
Microsoft's sp_help_revlogin will work. The KB article says how to move logins from 2000 to 2005.
-SQLBill
-
I successfully used REVLOGIN to generate the LOGINS script (from the source) and apllied to the target. Our issue resides with the fact that each SQL Login on our new, target SQL 2005 server has a schema w/ the owner = to the schema name - not dbo (eg. Schema Name=Order_Reader --- Schema Owner=Order_Reader)
We have to manually edit both the "Database Schema Names" (1 at a time) and the SQL LOGINS (1 at a time) and on the properties for each, change the Schema Owner to "dbo".
In search of a faster way to accomplish this for all our servers being migrated to SQL 2005...
thx
BT -
Once you have got your Logins known to SQL, you can detach your user databases from SQL 2000 and attach to SQL 2005 (or restore a backup to SQL 2005) and all the access will work as before. This just leaves the system databases needing special handling.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
- As said by SQLBILL about Microsoft's sp_help_revlogin and the KB. It has helped us. We were able to script out the SQL logins only but we had to manually create the windows logins . But latter I thought of trying the following option :->
ALL Tasks ---> Generate SQL Scripts (Options Tab) ---> Script SQL Server logins (Windows NT and SQL Server logins)
Try this and do let us know if this helped.
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
-
Quote: We have to manually edit both the "Database Schema Names" (1 at a time) and the SQL LOGINS (1 at a time) and on the properties for each, change the Schema Owner to "dbo".
I used this script to script out Drop Schema statements Alter Schema statemenst to change all default schemas in a database to "dbo".
set nocount on
select
'use '+DB_NAME() AS [Current Database]
select
'drop schema ['+ name+']' from sys.schemas where schema_id between 5 and 16000
select
'alter user ['+name+'] with default_schema = dbo'
from sys.sysusers
where status > 4
set nocount off
Greg
Greg
-
The is a kb article that MS has given to script logins from 200 to 2005. It has a procedure that scripts the logins and using that script the can be created in 2005 server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
hi refer ,
http://support.microsoft.com/kb/246133/ ---this is wat sugesh was mentioning
[font="Verdana"]- Deepak[/font]
-
you can also modify sp_rev_logins to generate the "DEFAULT_SCHEMA" automatically to "dbo".
It will save you a "ton" of work
* Noel -
Oh and sp_rev_logins does *NOT* check for windows_policy so if you have a password that won't pass your windows policy the script will fail unless you configure the output to disable the check_policy option.
Just another thing to keep in mind
* Noel -
This script will cycle thru each 2005 DB and "auto-fix" each database User's SID to match their SQL Server level SQL Login.
DECLARE @sql nVarchar (100), @DB VarChar (50), @SQL1 nVarchar (100), @user-id Varchar (50)
DECLARE DBs CURSOR FOR SELECT [name] FROM sys.databases WHERE Database_id > 4
OPEN DBs
FETCH NEXT FROM DBs INTO @DB
WHILE @@FETCH_status=0
BEGIN
SET @sql = 'Use '+ @DB
EXEC sp_EXECuteSQL @sql
DECLARE FLogins CURSOR FOR SELECT [name] FROM sys.server_principals WHERE Type = 'S'
OPEN Flogins
FETCH NEXT FROM Flogins INTO @user-id
WHILE @@FETCH_status=0
BEGIN
SET @SQL1 = 'sp_change_users_login '+ '''Auto_Fix''' + ', '+ @user-id
EXEC sp_EXECuteSQL @SQL1
FETCH NEXT FROM Flogins INTO @user-id
END
CLOSE Flogins
DEALLOCATE FLogins
FETCH NEXT FROM DBs INTO @DB
END
CLOSE DBs
DEALLOCATE DBs
BT -
Thanks for login orphans i had one for sql 2000 was looking for one with 2005
-
Hi,
I just want to add to this topic. I'm sorry for a very delayed reply. I was browsing through and i found this forum discussion on SQL logins migration.
The DTS or SSIS Login migration utilities are helpful, but more often than not, the mapping is unsuccessful. This results in a lot of orphan users getting created. I tried the DTS and SSIS packages, but received terrible feedback with people stating that they were still unable to login. I then discovered the mis-map. The following is the script that will create a stored procedure called 'sp_hexadecimal' and 'sp_help_revlogin_2000_to_2005' under the stored procedures section of the source instance. The final portion of the script automatically gets executed with the instruction "exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1'.
Please make sure that you run this script in the "Query Analyzer". Once done, a login creation script is generated in the Results window. Copy the results and paste the script in the destination SQL 2005 instance.
Note: If this script is executed twice in the destination SQL 2005 instance, it will display that the "SID is in use". There are some logins that already exist on the destination instance. Those will not be created and will display an error that the login already exists.
Please let me know if the above information helped.
The script is as follows:
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_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005
@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0
AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (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 STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
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 ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'
WHILE @@fetch_status = 0
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 = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END
SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GO
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply