SQL SERVER – Fix : Msg 15151, Level 16, State 1, Line 3 Cannot drop the login ‘test’, because it does not exist or you do not have permission

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.

Solarwinds

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/ )

Solarwinds
, ,
Previous Post
SQL SERVER – Add Any User to SysAdmin Role – Add Users to System Roles
Next Post
SQL SERVER – 2008 – Certification Path Complete Download PDF

Related Posts

13 Comments. Leave new

  • Thanks

    Reply
  • Hey there,

    That was very much helpful for me! Thank you a lot!

    Reply
  • 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!

    Reply
  • Good man, thnx for your sharing. cheers

    Reply
  • Awesome, again as always, thank you very much. This was more helpful than you know!

    Reply
  • 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

    Reply
  • 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!

    Reply
  • 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:

    Reply
    • What is the output of
      select IS_SRVROLEMEMBER(‘sysadmin’)

      Also try “Run as Administrator” for SSMS.

      Reply
      • 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.

    Reply
  • I just queried the sys.dm_exec_sessions DMV and dropped the login.

    Thanks,
    D.

    Reply

Leave a Reply

Menu