April 30, 2008 at 11:36 am
[font="Verdana"]Hi;
I have the situation of creating a disaster/recover (DR) database on a second SQL 2000 server, but when we try to run the application we get an error because aparently the user that installed the application is hard-coded and bound to the SQL sid for that user, which eventually involves two schemas.
I have done a work-around process on the DR server changing the ownership of objects for the user with the issue, dropping the user, creating the user, and putting the objects back, this works but the downfall, is time consuming since this user owns 80% of the objects in the database, and of course there are over 1000 tables among other objects like views and stored procedures.
So I am thinking if there is a way to synchronize the user at the SQL servers level rather than at the application, this will allow to restore a database from production to DR and use it right away without the need of tweaking the user.
Let me know your thoughts on this.
Thank you!
J.C. Alexandres
IT Professional[/font]
May 2, 2008 at 10:00 am
Microsoft has this script as a recommendation for transferring logins:
----- 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 @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
----- End Script -----
If the logins already exist on the DR server, there is a stored procedure to "sync" the logins. Check out sp_change_users_login in BOL. Run it like sp_change_users_login 'AUTO_FIX', 'user_name' within the restored database and you should be good to go. HTH.
-- You can't be late until you show up.
May 19, 2008 at 10:10 am
I'll give it a try as soon as the developers don't care enough of that database in case I trash it 😀
I really appreciate your reply!
Regards,
May 19, 2008 at 11:49 am
I did run the script to fix the user, however, the application seems to still have an issue with the ID of the maker of the database, which at the installation of the app, a custom user created the database objects, the application hard codes this sid, and it will not work until the ownership of the objects of this user are changed to another user (sa per example), the user dropped, re-created, and objects re-associated.
I have some screenshots of the error, unfortunately I can't post them here.
May 19, 2008 at 11:57 am
Sorry, I mis-understood the intent or your original post as that will script all your users from one server to another. Did you try: sp_change_users_login 'username', 'AUTO_FIX'? Run this within the database that's been restored and it'll sync the user up with the login. HTH.
-- You can't be late until you show up.
May 19, 2008 at 12:05 pm
Yeah I did that, as in my explanation/reply to your kind reply, that was the only way to get it working, which could be a lot easier if there was a way to identify the objects being changed (ownership) and spool the list to a temp table to then use when the user is recreated and the objects put back under it's ownership. I used to do something like that with Oracle on HP-UX, with the spool argument wich can be used in any kind of process involving any database objects.
I appreciate your help.
July 24, 2008 at 9:32 am
With sp_change_users_login there should be no need to drop the user. All it does is update the SID in sysusers in your database with the corresponding SID from syslogins in the master database for a user. I.e. it makes the login sid = user sid. Only works if the names are the same as written above, you can use the Update_One clause instead of autofix and it will link a user to a login, and you can give the login to link a user to.
July 24, 2008 at 10:01 am
I know the sp_change_users_login script will sync the user (s) that came with the database brough from the original server, in this case production, to the DR server, but what is needed in this case is to sync the sid of the user at the DR server with the sid of the user at the production server, which is what the application "look" to allow the connection.
Thank you for your suggestion anyway!
July 24, 2008 at 10:06 am
ok. Let me see if understand this....
You have a DR server
You restore a database from production to the DR server
The user in this database the application uses does not have a login on the DR server?
If so should be able to just create the login, then use the above mentioned methods. After the first time you do a restore and get it to work, all you should have to do is run the above method to fix it.
Or are you saying that it actually checks the SID and not the login, so that you have to have the exact same SID? If that is the case, to guarantee you have that SID available you need to create that login as soon as the server is installed, or the SID might be used.
July 24, 2008 at 12:33 pm
I also have some reporting databases at the DR server, one is a restore from production, in this database I don't use the application, but I can connect reports to the data using the user with the conflict (after fixing the login), so this user exist at the DR server, now, the database with the issue is a restore from production which it could be enabled right away if the production database will have a problem, this is where the user id need to be the same as at the production server so the application can run. So what I think it will work is to sync the sid of the user in reference at the DR server, with the sid of the same user at production.
I have to add the installers of Microsoft's Dynamics Axapta, did create the database with this specific user as the application was being installed. I could easily change the ownership of the objects from this user, drop and re-create the user, then the application will run to certain extent, but as some of the code points to stored procedures and tables owned by the user in reference, then that's why it is necessary to tweak the user.
March 25, 2009 at 1:32 pm
If I read your initial post right, it sounds like you only need to do this once, since the SID on Prod will not change and once you have it on the DR server you are set. Use the script posted above, by Tosscrosby, to create your login on the DR server then future restores of the DB from prod will not give you any problems, regardless of the owner of the objects.
I would try to make an effort to get the object owner changed to DBO though, since, as you are seeing now, using multiple schemas/owners only causes problems.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply