May 21, 2008 at 1:48 pm
If I move the logins from one sql server to another sql server, will user permissions be moved along with the login information, if not how do I move it? Any help would be gr888 [:D]
May 21, 2008 at 2:25 pm
Is this kind of what you are looking for? http://support.microsoft.com/kb/246133/
This is mostly for server level permissions. I believe (but have not tested) that if you do this first, then move the databases, the db user level permissions will be linked up as well. If not, you'll need to run sp_change_users_login to link them up.
Good Luck!
Chad
May 22, 2008 at 9:56 am
If I move the logins from one sql server to another sql server, will user permissions be moved along with the login information, if not how do I move it? Any help would be gr888 []
Is this kind of what you are looking for? http://support.microsoft.com/kb/246133/
This is mostly for server level permissions. I believe (but have not tested) that if you do this first, then move the databases, the db user level permissions will be linked up as well. If not, you'll need to run sp_change_users_login to link them up.
Good Luck!
Chad
thanks chad,
so are you saying that once I move logins, then permissions would be moved automatically but I only have to link them using sp_change_users_login?? and if that is the case how would I know what all to be linked and what to be linked??
May 22, 2008 at 10:32 am
User permissions are stored in the database, so they are not copied when you copy logins from instance to instance. You can either script the users and permission from the original database and create them in the destination database or you can backup the original database and restore it at the destination.
If you use the method described in the link Chad posted, there probably won't be many orphaned users. You should still run sp_change_users_login with the report option to be sure.
Greg
May 22, 2008 at 11:40 am
It would be great i f you can run microsoft stored proc for move the logins from one server/instance to another.
After moving this you need to manually map the logins with DB's. Otherwise it won't work as it worked in first server/instance.
SP_REVLOGIN
SP_HEXADECIMAL
Itwill work!!!!
Manoj
MCP, MCTS (GDBA/EDA)
May 22, 2008 at 11:49 am
Hey Greg,
Can you tell me how to script users and pemissions??
May 22, 2008 at 11:51 am
thanks and can you tell me the procedure as how it can be done, also how to script permissions?
May 22, 2008 at 1:45 pm
Just run both the permission. It will give you some result sets. Actually that result set having to create logins with password in encryption format. You just need to copy that script and run it to new DB in the server.
Refresh your new DB and you may able to see all login from old DB to New DB.
That's what it will work.
MCP, MCTS (GDBA/EDA)
May 23, 2008 at 8:36 am
Run this script against each DB on the server, it will synch up any orphaned users:
USE [DB Name Here]
GO
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE (issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null)
AND (name != 'dbo')
ORDER BY name
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynched'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
PS If you cut and paste the text from a previous post, then highlight the text you just pasted, then double-click on '[quote][/quote]' link in the IFCode shortcuts box to the left, it appears like this - as a quote.
May 23, 2008 at 8:53 am
Simon this would be for DB level. Which I mentione that is for Server level.
Here you for for script....
---------------------------------First Scriopt-------------------------
--SP_Revlogin
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (255)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (255)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @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, ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary, ' + @txtpwd + ')'
PRINT @tmpstr
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @encryptopt = '
END
ELSE BEGIN
-- Null password
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @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 @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
-------------------This is second script------------------------------
--SP_Hexadecimal
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_hexadecimal]
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(255)
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
---------------------------------------------------------------------
Once you run this script on the server, it will give you some sorts of results sets and then you just need to copy that result sets and paste it into other server(Target server)
It would directly create and remap the logins.
MCP, MCTS (GDBA/EDA)
May 23, 2008 at 9:27 am
Unfortunately, Manoj's code references sysxlogins, which doesn't exist in 2005. Go here instead to get the original MS version: http://support.microsoft.com/kb/918992.
Once you have compiled these procs, execute 'sp_help_revlogin', the resultset is a script that you can use to recreate the server logins, including the existing passwords for those logins (the password values are encrypted, so you can't use this as a way to reverse engineer passwords).
This script only addresses server logins, database level permissions are NOT addressed in this script (or Manoj's version).
May 23, 2008 at 9:37 am
Thanks a ton manoj and simon, dat was a lot of help
May 23, 2008 at 9:45 am
I guess Manoj and Simon got here before me, but I'm glad you got the info you needed.
Greg
May 23, 2008 at 9:51 am
Greg, will let you know 1st when I neeed somthin nextt time 😀
May 23, 2008 at 10:09 am
For SQL 2005 you need to follow this steps.
• You can transfer logins and passwords from SQL Server 7.0 and 2000 to SQL Server 2005.
• You assign logins to roles.
To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:
1. Run the following script on the source SQL Server.
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
2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.
MCP, MCTS (GDBA/EDA)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply