February 25, 2008 at 9:38 am
is there any script or method where I Can transfer all the logins from a sqlsever2000 database to sqlserver2005 database other than what MS offers on their support site.
February 25, 2008 at 9:47 am
You can use the Transfer Logins task in DTS to move your logins from SQL Server 2000 to a 2005 instance.
February 25, 2008 at 3:27 pm
Even though it says it is used for sql 2000, I used the sp_helprevlogin and sp_hexadecimal to go from 2000 to 2005.
http://support.microsoft.com/default.aspx/kb/246133/
¤ §unshine ¤
February 26, 2008 at 11:25 am
I tried using tha scripts on MS supprt site(http://support.microsoft.com/default.aspx?scid=kb;en-us;246133#XSLTH3137121122120121120120)
but I gives an error though i have sysadmin previlage.
Msg 3704, Level 16, State 1, Line 2
User does not have permission to perform this operation on procedure 'sp_hexadecimal'.
Msg 262, Level 14, State 1, Procedure sp_hexadecimal, Line 26
CREATE PROCEDURE permission denied in database 'master'.
Msg 262, Level 14, State 1, Procedure sp_help_revlogin_2000_to_2005, Line 185
CREATE PROCEDURE permission denied in database 'master'.
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'sp_help_revlogin_2000_to_2005'.
February 26, 2008 at 1:54 pm
See attached.
February 27, 2008 at 7:45 am
You have to have permissions to create objects in master or try logging in as sa...
¤ §unshine ¤
February 27, 2008 at 8:50 am
You can transfer logins using DTS to you sql server 2005. Also you can use the script in the link given below
http://support.microsoft.com/default.aspx/kb/246133/
to transfer logins and passwords to the other server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 11:14 am
The attached script is Microsoft's. Run it against the old server. It will output new script to add the logins to the new server. Copy and paste, or output it to a file.
February 28, 2008 at 8:48 am
There is a version of sp_help_revlogin to go from 2000 to 2005. See the following article, Method 2.
http://support.microsoft.com/kb/246133/
Steve
March 4, 2008 at 6:37 am
i have one try it it works for me.
please note that i hanvt created it, a freind give it to me.
/************************************************************************************************
*
*Purpose:To capture all the SQL Server logins with the binary password regardless of SQL version.
*Script is compatiable with SQL 7 / SQL 2000 / SQL 2005. Need to save the results
*to a text file and run within a new query window.
*
*Created Date:Inital - 01/13/2006
*
*Revsions:01/15/2006 - Made some formatting changes to the output text.
*01/16/2006 - Made syntax change to account for Binary version of SQL Server.
*01/16/2006 - Change version SQL version check because of syntax differences between
* SQL 2000 and 2005 with @@version.
*04/10/2007 - Made change to deal with user names that begin with numbers instead of characters.
*
************************************************************************************************/
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 @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
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
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
DROP PROCEDURE seeMigrateSQLLogins
GO
create procedure seeMigrateSQLLogins @login_name sysname = NULL
as
declare
@name char(50),
@binpwd varbinary (256),
@txtpwd sysname,
@tmpstr varchar (256),
@SID_varbinary varbinary(85),
@SID_string varchar(256),
@Is_Policy bit,
@Is_Exp bit,
@type char(1),
@Pol char(3),
@Exp char(3)
set nocount on
create table #logins (
[name] nvarchar(128) NOT NULL,
[sid] varbinary(85) NOT NULL,
[type] char(1) NOT NULL,
[is_policy_checked] bit default 0,
[is_expiration_checked] bit default 0,
[password_hash] varbinary(256) )
insert #logins (name, sid, type)
select name, sid, type from sys.server_principals where
(type_desc = 'SQL_LOGIN' or type_desc = 'WINDOWS_LOGIN') and name <> 'sa' and name <> 'NT AUTHORITY\SYSTEM'
update a set a.is_policy_checked = b.is_policy_checked, a.is_expiration_checked = b.is_expiration_checked, a.password_hash = b.password_hash
from #logins a, sys.sql_Logins b
where a.sid = b.sid
set nocount off
IF (@login_name IS NULL) --Not a single user, get the list
DECLARE seelogin_curs CURSOR FOR
SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
WHERE name <> 'sa'
ELSE
DECLARE seelogin_curs CURSOR FOR
SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
WHERE name = @login_name
OPEN seelogin_curs
FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE seelogin_curs
DEALLOCATE seelogin_curs
END
SET @tmpstr = '/* seeMigrateSQLLogins - For SQL Server 2005 Only '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF @type = 'S'
BEGIN
PRINT '/* SQL Login ******************/'
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF @Is_Policy = 1 Begin set @Pol = 'ON' End ELSE Begin set @Pol = 'OFF' END
IF @Is_Exp = 1 Begin set @Exp = 'ON' End ELSE Begin set @Exp = 'OFF' END
SET @tmpstr = 'Create Login [' + rtrim(@name) + '] WITH PASSWORD = ' + @txtpwd + ' hashed, sid = ' + @SID_string + ', CHECK_POLICY = ' + @Pol + ', CHECK_EXPIRATION = ' + @Exp
PRINT @tmpstr
PRINT ''
END
Else
BEGIN
PRINT '/* SQL Login ******************/'
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
SET @tmpstr = 'Create Login [' + rtrim(@name) + '] FROM WINDOWS; '
PRINT @tmpstr
PRINT ''
END
FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
END
CLOSE seelogin_curs
DEALLOCATE seelogin_curs
drop table #logins
GO
declare
@version char(5)
--Get the current version of SQL Server running
select @version = substring(@@version,29,4)
if @version = '9.00'
Begin
exec seeMigrateSQLLogins
End
Else
begin
exec sp_help_revlogin
End
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
DROP PROCEDURE seeMigrateSQLLogins
GO
/* End Script */
..>>..
MobashA
March 5, 2008 at 5:33 am
These scripts create the script for logins and passwords, but is there a script that also would script out the logins and their roles? Same with users and their roles in databases. Even if you use SSMS to script a login or user, roles are not scripted. And is there an easy way to script specific privs for a user within a database (i.e., grant select on a table)? Thanks!
March 5, 2008 at 10:55 am
kdv (3/5/2008)
These scripts create the script for logins and passwords, but is there a script that also would script out the logins and their roles? Same with users and their roles in databases. Even if you use SSMS to script a login or user, roles are not scripted. And is there an easy way to script specific privs for a user within a database (i.e., grant select on a table)? Thanks!
Seen this script in this forum and it does the same thing you wanted. Give it a try if this helps.
DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'put your username here'
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName +
' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' +
DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user '
+ @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) +
REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 196
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 224
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 26
AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 193
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 195
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 197
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 196
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 224
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects]
WHERE [id] = @ObjectID
AND [uid] = @DatabaseUserID
AND [action] = 26
AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END
SQL DBA.
March 5, 2008 at 11:30 am
If you are restoring a 2000 database to 2005 or detaching it and reattaching it, the users and roles will be contained in the database.
For some of the system roles, here is an example for sysadmin:
select 'exec sp_addsrvrolemember "' + rtrim(name) +
'", "sysadmin";'
from master.dbo.syslogins
where name <> 'sa' and sysadmin = 1;
The results will be something like:
exec sp_addsrvrolemember "msdb_jobs", "sysadmin";
exec sp_addsrvrolemember "DOMAINNAME\sblock", "sysadmin";
Steve
March 11, 2008 at 1:10 pm
I tried working with this script
http://support.microsoft.com/kb/246133/
looks like everything got trasnfered but when I try to login with some id, its not getting in, is there a chance that it broke while trasfering.
March 11, 2008 at 1:23 pm
Mike Levan (3/11/2008)
I tried working with this scripthttp://support.microsoft.com/kb/246133/
looks like everything got trasnfered but when I try to login with some id, its not getting in, is there a chance that it broke while trasfering.
Did the new logins get created with the same SID's from the previous server? More than likely you have orphaned users (database user ID's that do not correspond to your new server ID).
Try running this:
sp_msforeachdb
'USE ?
DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ''update_one'', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END CLOSE fixusers
DEALLOCATE fixusers
'
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply