43

I have created the user sagar in SQL Server 2012 database and mapped it to the XYZ database. After some time I deleted the XYZ database and now I don't need the existing user sagar also. But when I try to delete the user from SQL Srever Management Studio. I am getting the following exception

Could not drop login 'sagar' as the user is currently logged in. error:15434

Mat
  • 202,337
  • 40
  • 393
  • 406
Sagar Pudi
  • 4,634
  • 3
  • 32
  • 51

2 Answers2

97

User can be deleted after killing the session by identifying the session_id of the user.

SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'sagar'

KILL 51  --51 is session_id here, you may get different id

Now you can delete the login simply by executing the below query (or) by using sql server management studio options.

DROP LOGIN 'sagar'
Sagar Pudi
  • 4,634
  • 3
  • 32
  • 51
  • 4
    [Here](http://stackoverflow.com/questions/4965260/drop-sql-login-even-while-logged-in) is you can find complete code to kill all sessions – Mardok Jun 23 '16 at 11:18
4

How to delete yourself as a Login. E.g. You may want to do this when the hostname changed so that Sql Server records your Login under the old hosthame.

Login to SSMS. Select the 'sa' account and change the password to something you will remember.

Close down SSMS, start it up again but login as 'sa' this time. Select the Login you wish to delete, right click for Delete.

You will probably be told that Login owns one or more databases.

Login 'hostname\The Login' owns one or more database(s).
Change the owner of the databases before dropping the logon. [MS Sql Server Error 15174]

To list the owner of every database:

select suser_sname(owner_sid) from sys.databases

To change the owner of every database to 'sa'

EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'' '

<-- This sp_MSforeachdb will probably fail for one or two, as it did for me. In my case because that database was single user. Changing the rogue database to multi-user fixed that. Then I just ran EXEC sp_MSforeachdb ... again.

To check, run

select suser_sname(owner_sid) from sys.databases

again to ensure the Login is gone

Now delete that login.

user1040323
  • 481
  • 4
  • 11