I got following error when I was trying to delete user ‘test’ with ‘SA’ login. I was little surprised but then I tried to delete with the windows authenticated systemadmin account. Once again I got the same error.
Msg 15151, Level 16, State 1, Line 3
Cannot drop the login ‘test’, because it does not exist or you do not have permission.
The reason I was surprised that I was systemadmin and I should be allowed to delete the login. I am including the script which I used to delete the account here.
IF EXISTSÂ (SELECT * FROM sys.server_principals WHERE name = N'Test')
DROP LOGINÂ [Test]
Fix/Workaround/Solution:
I finally found out that error was misleading. I had another session open in SQL Server Management Studio with username ‘test’ and it was preventing me to drop the user. Once I closed that open session I was successfully able to delete the user using above script.
In Normal Case:
Following error usually show up when user is logged in, which I did not get it.
Msg 15434, Level 16, State 1, Line 3
Could not drop login ‘test’ as the user is currently logged in.
However, when I tried to re-create scenario I got above error. I want to share this experience with users and want to know if they have ever faced this scenario.
Reference : Pinal Dave https://blog.sqlauthority.com/ )
13 Comments. Leave new
Thanks
Hey there,
That was very much helpful for me! Thank you a lot!
had the same problem. can’t seem to find a solution to it though. the phantom user is still logged on! It’s definitely another SQL2008 bug!
Good man, thnx for your sharing. cheers
Awesome, again as always, thank you very much. This was more helpful than you know!
Dear Pinal,
Thanks for your sharing ,
Msg 15434, Level 16, State 1, Line 3
Could not drop login ‘test’ as the user is currently logged in.
in this error that you’ve mentioned above, I share this solution :
1.
select * from sys.dm_exec_sessions WHERE login_name = ‘test’
2.
KILL session_id
3.
DROP LOGIN [Test]
Sincerely yours,
Saeed
Right here is the perfect blog for anybody
who really wants to find out about this topic. You realize a whole lot
its almost tough to argue with you (not that I personally will
need to…HaHa). You certainly put a new spin on a topic that has been
discussed for years. Great stuff, just great!
I know this is an older post but I am currently having the Msg 15151, Level 16, State 1, Line 1 error when trying to drop the user. Here’s a link to my question:
What is the output of
select IS_SRVROLEMEMBER(‘sysadmin’)
Also try “Run as Administrator” for SSMS.
The output of that query is 1 . I have also tried “Run as Administrator” to delete the user to no avail. Thanks for your reply!
1. Have you also checked sys.server_principals for that SID?
2. Have you tried Drop Login as I feel its a login not a user.
Hi Pinal
I am removing logins (individual) from test machines but I keep on getting the same error message “…is currently logged in.” Do you know if there is any way to disconnect/terminate the session to the login? When I run “exec sp_who2 active”, I do not see any connection to the server at all except myself. Do you know if there’s a way to get around this?
Thanks,
Dan.
I just queried the sys.dm_exec_sessions DMV and dropped the login.
Thanks,
D.