Cannot fix orphaned records - SQL Server Forums

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

SQL Server Administration (2008)

Cannot fix orphaned records

Author

Topic

taersious
Starting Member

2 Posts

Posted - 2010-08-31 : 17:55:45

This query returns columns with blank Logins.

Select u.name As [Name]
       ,u.principal_id As [ID]
       ,isnull(suser_sname(u.sid) ,N'') As [Login]
  From sys.database_principals As u
Where u.type In ('U' ,'S' ,'G' ,'C' ,'K');

I do not have access to the original server where the SIDS for these users are. How can I fix this? I cannot drop user or schema for users with blank logins because they own the tables, and I cannot use the command

EXEC sp_change_users_login 'Auto_Fix','<user>'

EXEC sp_change_users_login 'Auto_Fix','<user>',NULL,'<pwd>'

EXEC sp_change_users_login 'update_one', <user>, <user>

as none of them fix the issue.

quote:
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0.

I have also tried the 'sp_hexadecimal' method, but since I do not have access to the original server, I cannot utilize this method.

Thanks ahead of time for any suggestions.

taersious
Starting Member

2 Posts

Posted - 2010-09-01 : 11:11:55

One additional note: I just noticed that this customer had set the dbo account to have the login of the name for which I am trying to correct! In other words, when I view the dbo security account at the database level, dbo has a login name set to <user> that I am trying to correct Orphan issue for! We have their database attached to our server and are unable to do anything other than ultimately remove the <user> account, leaviing the Schema of the same name. Now we cannot recreate the <user> because the name already is assigned to the login of another account. Also, this account was created as a Windows account, but at the server level, we use the <user> account as SQL account. Work-around: use dbo (sa) account for everything, and create DOMAIN accounts on the database for application access.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources