December 1, 2011 at 10:05 am
I found that there are 4 users in one of my databases that are orphaned, 3 do not have domain accounts and 1 does. I cannot delete or update any of them.
The one user that has a domain account has dbo permissions and shouldn't so I am unable to get this fixed.
I'm not sure how the "\" got in front of the 1 user with a domain account.
Things that didn't work:
I have tried to create a user with the same name and assign it the SID - didn't work
sp_dropalias
EXEC sp_change_users_login 'Update_One', ''\Domain\UserName', ''\Domain\UserName'
EXEC dBName..sp_changedbowner 'sa' (this worked, but didn't help delete or update any of the accounts)
The user shows in this query as '\Domain\UserName'
select * from dbo.sysusers
where isaliased = 1
I have searched and tried quite a few different things, but nothing has worked so far.
Any suggestions, help, or fix would be greatly appreciated.
Thanks
December 1, 2011 at 10:06 am
try using [] around the login in question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 1, 2011 at 10:09 am
Assuming this is a Active Directory user you shouldnt need to do this at all.
All logins are based on SID's and the with AD users this comes straight out of the domain controller and should never change.
So my query is, why are you doing this?
December 1, 2011 at 10:10 am
I've tried that and double quotes, single.
I've tried stuff I knew wouldn't work.
December 1, 2011 at 10:13 am
Well I am trying to get the 1 user that has a domain account to show up so I can change the permissions on it. Yes we are using AD, but the user does not show up under users.
He is in other databases just fine. It is just on dB that is not correct.
December 1, 2011 at 10:55 am
4x4krzy (12/1/2011)
Well I am trying to get the 1 user that has a domain account to show up so I can change the permissions on it. Yes we are using AD, but the user does not show up under users.He is in other databases just fine. It is just on dB that is not correct.
sp_change_users_login 'auto_fix','username'
Do this to fix orphaned users, then do what you want with them.
Jared
Jared
CE - Microsoft
December 1, 2011 at 11:04 am
Because the account is showing up as \Domain\UserName that does not work
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 already tried that one too. I also tried to do it with and without the '\' in quotes, [] ""
I asked one of the server ops guys to get the AD SID for me and the AD SID is not the same as the one in SQL.
December 1, 2011 at 11:12 am
You ran this on the database in question or on Master?
Jared
Jared
CE - Microsoft
December 1, 2011 at 11:16 am
The dB in question.
If I run this in the dB in question the user shows up. I get the 3 result sets.
SELECT * FROM sys.sysusers;
EXEC sp_helpuser;
If I run it in Master there are only 2 result sets and the user is not there. (it's expected)
December 1, 2011 at 11:19 am
4x4krzy (12/1/2011)
The dB in question.If I run this in the dB in question the user shows up. I get the 3 result sets.
SELECT * FROM sys.sysusers;
EXEC sp_helpuser;
If I run it in Master there are only 2 result sets and the user is not there. (it's expected)
I meant that you need to run the sp_change_users_login on the db in question... I think...
Jared
Jared
CE - Microsoft
December 1, 2011 at 11:19 am
4x4krzy (12/1/2011)
I asked one of the server ops guys to get the AD SID for me and the AD SID is not the same as the one in SQL.
I'd have to disagree with you on those lines somewhere, if the SID didnt match the user wouldnt have access.
4x4krzy (12/1/2011)
Yes we are using AD, but the user does not show up under users.He is in other databases just fine. It is just on dB that is not correct.
If all you have is a database without the user you need to create the user. Trying to fix a user/login which isnt orphaned in the method you are trying wont work, end of.
This will create a user in the database and link it to the existing login.
use [databasename]
GO
CREATE USER [DOMAIN\Username] FOR LOGIN [DOMAIN\Username]
GO
December 1, 2011 at 11:21 am
Oh - yes I did run it from the dB in question.
December 1, 2011 at 11:33 am
Have you tried simply right clicking on the user on that database and deleting them?
Jared
Jared
CE - Microsoft
December 1, 2011 at 12:08 pm
Jared,
I have tried to run that fix in the dB, but it didn't work.
I also ran this
SELECT a.name AS 'Server Name'
, a.sid AS 'Server ID'
, b.name AS 'Database Name'
, b.sid AS 'Database ID'
FROM master.sys.syslogins a
INNER JOIN SPUTIL.sys.sysusers b
ON a.sid = b.sid
WHERE a.name like '%xxxxxxo%'
and the user does come up with the SIDs the same for server and dB.
Here are the results
ServerName DatabaseName
Domain\User \Domain\User
The Database Name has the "\" in front of the user and that is causing me the issue.
December 1, 2011 at 12:41 pm
4x4krzy (12/1/2011)
Jared,I have tried to run that fix in the dB, but it didn't work.
I also ran this
SELECT a.name AS 'Server Name'
, a.sid AS 'Server ID'
, b.name AS 'Database Name'
, b.sid AS 'Database ID'
FROM master.sys.syslogins a
INNER JOIN SPUTIL.sys.sysusers b
ON a.sid = b.sid
WHERE a.name like '%xxxxxxo%'
and the user does come up with the SIDs the same for server and dB.
Here are the results
ServerName DatabaseName
Domain\User \Domain\User
The Database Name has the "\" in front of the user and that is causing me the issue.
Well... The only thing I can think of is to bckup all databases including system, restore in a test env., verify issue can be replicated, login using a DAC, change the name in that one database, and see if it fixes it. Otherwise, I have no idea what to do...
Jared
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply