May 17, 2005 at 3:22 pm
I am very new to SQL server 2005. I want to copy all "sql logins" from a sql 2000 server and move it to this new server. I am not sure what to do. Is there a way to script sql logins with password and apply it to SQL server 2005?
DTS function doesn't seem to be working yet, so I am not sure if I could copy logins.
I did found a way to script NT logins to the new server.
mom
May 18, 2005 at 12:41 am
Please post an update here if you give it a try.
May 18, 2005 at 12:41 am
I think the process described in KB http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q246133 should still work. I am referring to the second alternative, where you create a procedure in master that outputs T-SQL commands (EXEC sp_addlogin ..) that you can copy and execute in the destination server.
The sp_addlogin procedure still exists in SQL Server 2005, however it is only for backwards compatibility. The recommended way of doing it is using CREATE LOGIN. You might be able to tweak the script from the reference I linked above to output CREATE LOGIN statements instead, but I am not sure and do not have the possibility to look into it right now.
May 20, 2005 at 11:14 am
After several day of playing with my though here is what I have decided to do. I copy the script from this article: http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q246133 and then modify it to fit how I want sql 2005 login set up.
For example: I learn that when we create a login, that login was set up to expired and I don't want any application to failed because of expired password. (very good security, but need more time and user co-operation to implement it.)
Because SQL 2005 does not comes with PUBS database, I have created a new database call Default_login. The reason being that I don't want user to have their default database= master.
Because I chose to create a new default database, I also need to physically add the user to that new default_login database in order for the user to be able to login to the server.
My group also don't like the idea of having a login default to any A (user database) because as we all know when we drop that A database, it would affects other user who has their default database set to that A database.
I did not modify sp_hexadecimal , but I modified sp_help_revlogin. Here is what my code look like.
mom
-----
use master
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 @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
--additional code from mom
SET @tmpstr = 'ALTER LOGIN ' + @name + ' WITH Default_Database = Default_Login, CHECK_POLICY = OFF ,CHECK_EXPIRATION = OFF;'
PRINT @tmpstr
SET @tmpstr = 'Use Default_Login'
PRINT @tmpstr
SET @tmpstr = 'CREATE USER ' + @name + ' FOR LOGIN '+ @name + ' WITH DEFAULT_SCHEMA=[dbo];'
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
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
--additional code from mom
SET @tmpstr = 'ALTER LOGIN ' + @name + ' WITH Default_Database = Default_Login, CHECK_POLICY = OFF ,CHECK_EXPIRATION = OFF;'
PRINT @tmpstr
SET @tmpstr = 'Use Default_Login'
PRINT @tmpstr
SET @tmpstr = 'CREATE USER ' + @name + ' FOR LOGIN '+ @name + ' WITH DEFAULT_SCHEMA=[dbo];'
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
-----
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply