May 6, 2003 at 1:10 pm
I had a question pertaining to orphaned logins. I am a DBA working on SQL 7. Whenever I restore a DB somewhere I manually fix the users by deleting the users from the sysusers table of that DB and then re-creating the login again. How does SQL 2000 handle this? Are the logins correctly mapped to the proper UIDS in SQL 2000 or would we need still need to fix them like in SQL 7
May 6, 2003 at 2:16 pm
Look at sp_change_users_login, you can fix much easier this way and is the same for 7 and 2000.
May 6, 2003 at 2:53 pm
It's only an issue with SQL logins, NT logins don't have the problem. If you're backing up from one system and restoring to another, save yourself a lot of headache by making the ID's match on both servers, then when you restore everything works.
Andy
May 6, 2003 at 3:31 pm
I saw sp_change_users_login in SQL 6.5 BOL as well. Are there any pitfalls in using this with SQL 6.5?!
I know using SQL 6.5 (now) itself is a pitfall.. Some of the server I handle are still 6.5 because of the business requirements.... Client comes first... huh!!!
.
May 6, 2003 at 3:52 pm
Not sure, I wouldn't think so, however my SQL 6.5 reference that came with 6.5 does not even mention and nor does 6.5 Unleashed so I cannot be sure without testing.
May 7, 2003 at 12:29 am
I had a same problem with SQL 2000 when I restored one of my DBs in different server which had same logins because of UIDS mismatch. But this can be easily solved with sp_change_users_login 'Update_One', 'user', 'login'.
Dinesh
mcp mcse mcsd mcdba
May 8, 2003 at 6:49 am
I move databases aaround quite a bit, so I created a little script to do this for all of the logins in the database.
Mike
DECLARE @sLogin VARCHAR(50)
DECLARE cursor_LoginList CURSOR FOR
SELECT NAME
FROMsysusers
WHERE status = 2
AND NAME <> 'dbo'
AND NAME IN (Select name from master.dbo.syslogins)
OPEN cursor_LoginList
FETCH NEXT FROM cursor_LoginList INTO @sLogin
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
PRINT 'Fixing Logins in Current Database for ' + @sLogin
EXEC ('sp_change_users_login Update_One ,' + @sLogin + ',' + @sLogin + '')
FETCH NEXT FROM cursor_LoginList INTO @sLogin
END
Deallocate cursor_LoginList
May 8, 2003 at 10:21 am
If possible I always use domain accounts/ groups then the SIDs are the same always and the mapping is automatic whatever server the db is restored to. If I have to use standard logins I create the login with the same sid and password as on the other server and it re-maps automatically. You can't always drop the user if the user owns objects...
--run on old server
SELECT CONVERT(VARBINARY(256), password) as 'password',sid
FROM syslogins
WHERE name = 'loginname'
-- restore databases on new server then add password and sid info from the previous query and run on new server
EXEC sp_addlogin 'loginname', encryptedpassword, defaultdatabasename, language, encryptedsid,
@encryptopt = 'skip_encryption'
-- all the login/ user connection on all databases will be restored and the passwords should be the same.
May 11, 2003 at 10:16 pm
Instead of writing looping scripts for fixing logins, why not just use the auto_fix parameter? (See BOL)
Ian
May 12, 2003 at 7:19 am
From BOL:
When using Auto_Fix, you must specify user and password
I do not know my users passwords and it would take a long time to do all of the logins one by one even if I did.
Mike
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply