Sysusers help, a real brain puzzle 1

[bikerboy718]

I have a client database that has a rougue user in its sysusers tabe. The use does not exist in the master database or on the server. I have tried the standard methods of removing the user from the database:
sp_dropuser
sp_droplogin
sp_dropalias

But none of them work.

I even created a dummy user and tried to alter the user and map it to the new dummy user and that does not work either.

And you cannot directly delete or update records from these tables.

Any help is greatly appreciated.

With Great Power Comes Great Responsibility!!!

Michael

 

[gmmastros]

try this...

Use [YourDatabaseName]
go
sp_revokedbaccess 'RougueUserName'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bikerboy718]

It gives me the same error message:

Cannot drop the user '\domain\username', because it does not exist or you do not have permission.

With Great Power Comes Great Responsibility!!!

Michael

 

[gmmastros]

Is this an application role that you are trying to remove?

When you do, "Select * From sysusers", there are a bunch of columns at the end like IsLogin, IsNTName, IsNTGroup, etc... Can you tell me which columns have a value of 1.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bikerboy718]

The following columns have a value of 1

altuid
islogin
isntname
isntuser
isaliased


With Great Power Comes Great Responsibility!!!

Michael

 

[gmmastros]

Well.... I learned something useful today.

I encourage you to read this...

http://www.mssqltips.com/tip.asp?tip=1675

Looks like you'll want to run:

[tt]
sp_dropalias 'RougueUserName'
[/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bikerboy718]

That does not work either. I get the following error message.

No alias exists for the specified user.

With Great Power Comes Great Responsibility!!!

Michael

 

[gmmastros]

Hmmm.... I really thought that would have worked.

Last ditch effort.... remove the hard drive, open it up, find the correct spot on the platters and remove the user with a powerful magnet.

Seriously though. I'm at a loss. Hopefully someone else will be able to help you.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bikerboy718]

I said it was a brain puzzle

With Great Power Comes Great Responsibility!!!

Michael

 

[gmmastros]

Michael, when you ran the command, did you put a backslash at the beginning of the user name?

sp_dropalias '[!]\[/!]domain\username'

or this...

sp_dropalias 'domain\username' -- without the first back slash

I think it's supposed to be the second one, but I would try both just to be safe. Also, I read some things online that suggests you may still have a problem if this user owns any objects in the database.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bikerboy718]

George,

I removed the backslash from the front and it worked. That did the trick. Thanks for all of your help.

With Great Power Comes Great Responsibility!!!

Michael

 

[gmmastros]

I'm glad. Please disregard everything I said earlier about the magnet.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom