December 1, 2011 at 12:50 pm
Sort of had the same idea. I will back up the one dB that is giving me issues, drop it, and restore it.
I'll do it tonight and post back if it worked.
Thanks All for the suggestions and help.
December 1, 2011 at 3:58 pm
You should be able to use DROP USER or sp_dropuser to get rid of users in the db, whether they are linked to a login or not.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 1, 2011 at 5:40 pm
I have tried to drop the account and it will not do it.
December 1, 2011 at 5:45 pm
I have other user accounts on some servers that do not have domain accounts and these accounts cannot be deleted either.
I have tried all the tricks listed in these blogs and others I have found. Since the users no longer have domain accounts I am unable to do anything with them.
December 2, 2011 at 7:35 am
You can't drop the user account(s)? [not the actual login]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 2, 2011 at 7:46 am
ScottPletcher (12/2/2011)
You can't drop the user account(s)? [not the actual login]
It seems that's the real problem. The user account is different from the login, but has the same sid. However, the user cannot be used BECAUSE the username begins with a slash. Not sure how the slash got before the domain.
Jared
Jared
CE - Microsoft
December 2, 2011 at 7:54 am
Nope, not at all.
I took over as DBA a wile ago and the security was not quite what it should be. I've been fixing ever since.
Another weird thing that is going on - If I query sys.logins I can see all the logings for the system, but if I view the logins through SSMS\security\logins not all of them are there.
The same goes for sys.sysusers.
December 2, 2011 at 7:57 am
Jared,
Exactly!!
A friend (MS SQL guy) said to open a trouble ticket with MSFT because it is very weird and most defiantly shouldn't be happening.
The really bad part is one person still has access, but I can't modify the account.
December 2, 2011 at 8:28 am
The user account can't be used to access data.
But you should be able to drop the user account from the db.
What error message do you get when you try to sp_dropuser the user?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 2, 2011 at 8:29 am
Please include the exact sp_dropuser command you used and the results of SELECT * FROM sys.database_principals as well.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 2, 2011 at 8:29 am
4x4krzy (12/2/2011)
Nope, not at all.I took over as DBA a wile ago and the security was not quite what it should be. I've been fixing ever since.
Another weird thing that is going on - If I query sys.logins I can see all the logings for the system, but if I view the logins through SSMS\security\logins not all of them are there.
The same goes for sys.sysusers.
I wonder if logging in DAC will make a difference with any of the things you are trying. Probably not, but worth a try?
Jared
Jared
CE - Microsoft
December 2, 2011 at 9:05 am
Today when I ran SELECT * FROM sys.database_principals the '\' was gone from the front of the login and this time the sp_dropuser 'domanin\user' did work.
I was able to add him back in and assign him db_datareader as it should be.
Still doesn't show up under the users tab, but does show witht he query to database_principals. As long as it works at this point I'm ok.
There are still other orphaned users with the '\' in front of the domain and I'll try and drop them too a little later.
Thanks everyone for the help.
December 2, 2011 at 10:11 am
Exactly: the orphaned ones will not show up in the GUI, so you can't delete them there, but you can DROP them using the T-SQL commands.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 2, 2011 at 11:29 am
One last thing to think about.
ServerName DatabaseName
sa dbo
Domain\User\Domain\User
SELECT a.name AS 'ServerName'
,b.name AS 'DatabaseName'
FROM master.sys.syslogins a
INNER JOIN xxxxx.sys.sysusers b
ON a.sid = b.sid
order by a.name
My other server didn't fix, but I won't be spending any more time on this one - for now.
Here is an image of the actual result - with the domain and user erased.
The sp_dropuser and all the other things I have tried do not work on this one. This user also has the same issue, still has access to the dB the way it looks.
Thanks
December 2, 2011 at 12:14 pm
FROM master.sys.syslogins a
INNER JOIN SPUTIL.sys.sysusers b
Why do you keep referring back to the "ancient" SQL 2000 views?
MS itself tells you they're not reliable for SQL 2005 and SQL 2008.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply