Disconnected/semi-orphaned users

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Disconnected/semi-orphaned users

Author

Topic

nwalter
Starting Member

39 Posts

Posted - 2009-02-12 : 13:59:22

I've been having trouble with what appears to be orphaned users in a 2008 database. This is somewhat hard to explain but I will try my best.

We have this database that is somewhat of a shared database and is used by many different applications, some of these applications have existed on other servers but have slowly been moved over to the new server. The catch here is we have had two copies of this database, one existed on the older servers for the older apps and the other existed on this new server and is used by older apps. The data is the same in both places and rarely changes, it's more of an operation/reference database. This database was long ago copied from the original database on the old server, it kept all the data, users, and roles the same. Though until recently the users were indeed orphaned because the associated databases didn't come over at the same time and so the windows accounts were never made at the server level.

So what has been happening is, we have been moving those old apps over to the new server. Typically when you create a new windows login in SQL server and that login has rights to a database the system has recognized that and reconnected the new login to the database logins. But as we've been moving apps this has not been happening and infact it's been impossible for me to create new SQL logins and have them link to the old database users in this database. The logins will always link to their respective databases when they've been created or re-created, just never to this one particular database.

The only fix i've found to this has been to delete the database user and create a new one, re-assigning roles and permissions. Because these are windows accounts the SIDs have always matched up and I never find true orphaned users when running sp_change_users_login. The only other thing i've noticed is that all of these semi-orphaned accounts show a down arrow in SSMS as well as in sysusers hasdbaccess equals 0 instead of 1.

Any idea how I can get these users to work without always recreating them?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 14:10:11

I use this one:

DECLARE @UserName nvarchar(255)
DECLARE Cursor_OrphanedUser cursor for
SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name 
OPEN Cursor_OrphanedUser
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' Synchronization of Logins in Progress'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
END
CLOSE Cursor_OrphanedUser
DEALLOCATE Cursor_OrphanedUser
Go

nwalter
Starting Member

39 Posts

Posted - 2009-02-13 : 13:02:50

Thanks, but this looks to only apply to SQL server logins. My problem occurs only with windows logins, as we do not have very many SQL logins.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 16:26:23

Make Sure Login Exists to map database users:

DECLARE @UserName nvarchar(255)
DECLARE Cursor_OrphanedUser cursor for
SELECT NAME FROM sysusers WHERE issqluser = 0 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name 
OPEN Cursor_OrphanedUser
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' Synchronization of Logins in Progress'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
END
CLOSE Cursor_OrphanedUser
DEALLOCATE Cursor_OrphanedUser
Go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources