T-SQL "Drop Login" doesn't work

[Catadmin]

All,

Need some help here. I can do this through SSMS, but for some reason can't do it through T-SQL and would rather use T-SQL.

Restored a database to a QC server. I have an orphaned domain login which belongs only to the Prod server. I need to drop this login and add the QC login. I can add the QC login just fine, but can't drop the Prod login without going through SSMS. What am I doing wrong?

Drop Schema [MyDomain\ProdLogin]
GO

sp_droprolemember 'db_owner', 'ProdLogin'
GO

Drop Login [MyDomain\ProdLogin]
GO

It's the last statement that won't work. I've tried it with and without the "MyDomain\" (because the login is listed in the database without the domain name) and I've tried it without the [], but in single quotes both ways. I've even tried sp_revokeLogin. None of them work. I keep getting the following error:

Msg 15151, Level 16, State 1, Line 1
Cannot drop the login 'MyDomain\PRODLogin', because it does not exist or you do not have permission.

I'm SysAdmin on the server and can drop this orphaned login just fine from SSMS. I just can't seem to code it in T-SQL.

Can someone please advise as to what I'm doing wrong? I'm using SQL Server 2005.

Thanks,


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

Try sp_droplogin.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Thanks, SQLBill, but that one isn't working either.

I think it has to do with the fact that the login is orphaned, but I don't know why it'll allow me to do it through the Object Browser and not through the code.

Any other thoughts?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

What if you ran

sp_change_users_login 'update_one', 'login, 'login'

Then dropped it.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

It may be a case of Enterprise Manager doing some 'magic' behind the scenes. Can you go to the database where this login exists and check it's properties? It might be a schema owner and you can't drop a login while it owns a schema.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

does SSMS provide an option to script this? If yes script it out and look at the script

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

Aha! Found the problem and it was a weird one.

And no, it wasn't forgetting to run the sp_change_users_login. I run that with every restore I do.

Oddly enough, it turns out the orphaned login had been used in a client script (C# .Net or VB .Net) as an Impersonate. The client hadn't run a "revoke" statement, but the session had ended, yet the permission retained itself in the syspermissions table. We had to manually revoke the permission to be able to drop the login. So, as long as we include that check in our code, we should be able to script it as a job.

Of course, when I suggested the developers fix the code so the Impersonate was revoked when they were done, I was told not to worry about it because this was a SQL bug... @sigh Tell me I'm not the only one who has to deal with lazy dev people... @=/



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"