sql server 7 orphaned users belonging to role
-
Hi,
We recently undertook an exercise whereby we detached c: and d: drives, then attached new c: and d: drives.
Before the drives where detached we took a backup of all databases.
All user .mdf's and .ldf's are on a separate SAN.
When the new drives were attached we reinstalled SQL Server 7. I then restored master from the backup.
At this point, all databases appeared within EM as all .mdf's and .ldf's are in the same location as original setup.
Integrity Checks run - O.K.
I ran a procedure to show Orphaned Users and it returned over 5000!
On investigation all orphaned users belong to a custom database role within each user database.
There is not an equivalent login for the members of the role and there is not an equivalent user for the members of the role.
1. Are members of a role classed as users by default?
2.How do I resolve this issue?
I have attempted to use a procedure sp_fixuses I found on Google without success.
-
1. Don't know but, don't think it pertains in this case.
2. For SQL 2K you would run sp_change_users_login with auto_fix BOL has information or you can find it on-line probably here even..
The root of this problem is as follows: You detached and reattached SQL databases and installed them on a NEW instance of SQL. Don't think of it as SAME SQL server because you re-installed. This caused ALL of the IDs to get out of sync with their respective SIDs in syslogins table. Even though you see Mary in the syslogins table and Mary in your custom database they are currently NOT the same Mary.
Running sp_change_user_login tells SQL that Mary = Mary and everything will be right with the world. Unfortunately, I have not had an opp. to automate this procedure and have only had to use it a handful of times for a handful of logins (1x at a time).
Good Hunting!AJ Ahrens
webmaster@kritter.net -
After further investigation I have discovered that this was an issue before the new build. Thanks for your response and I'm sorry if I have inconvenienced anyone.
t
-
Its no issue at all. That is why we are here
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Orphaned role members that aren't users or logins? Orphaned users from a database copy are usually entries in sysusers that are missing from master..sysxlogins, or have a different sid value. This can be fixed with sp_change_user_login.
It sounds like you have values in sysmembers.memberuid that don't exist in sysusers.uid, and I don't know whether sp_change_user_login can handle that. You may have to enable updates to system tables and delete them manually. Be careful!!
exec sp_MSforeachdb 'if ''?'' not in (''master'',''msdb'')
delete [?]..sysmembers where memberuid not in (select uid from [?]..sysusers)'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply