{"id":3162,"date":"2022-04-10T07:50:10","date_gmt":"2022-04-10T00:50:10","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3162"},"modified":"2022-04-10T07:50:41","modified_gmt":"2022-04-10T00:50:41","slug":"sql-server-drop-login","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-administration\/sql-server-drop-login\/","title":{"rendered":"SQL Server DROP LOGIN"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server DROP LOGIN statement to delete a login from the database server.<\/p>\n\n\n\n

Introduction to the SQL Server DROP LOGIN statement #<\/a><\/h2>\n\n\n\n

The DROP LOGIN<\/code> statement allows you to delete a SQL Server login account. Here’s the syntax of the DROP LOGIN<\/code> statement:<\/p>\n\n\n

DROP<\/span> LOGIN login_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the login name that you want to delete after the DROP LOGIN<\/code> keywords.<\/p>\n\n\n\n
Note that you cannot drop a login while it’s signed in. And if a login owns a securable, server-level object, or SQL Server Agent job, you also cannot drop that login.<\/p>\n\n\n\n
If a login account is mapped to database users and you drop a login, these database users will become orphaned users.<\/p>\n\n\n\n
 SQL Server DROP LOGIN statement example #<\/a><\/h2>\n\n\n\n
We’ll use the BikeStores<\/code> database in the following DROP LOGIN<\/code> statement examples.<\/p>\n\n\n\n
1) Simple DROP LOGIN statement example #<\/a><\/h3>\n\n\n\n
First, create a new login<\/a> called jack<\/code> with a password:<\/p>\n\n\n
CREATE<\/span> LOGIN jack\nWITH<\/span> PASSWORD<\/span> = 'iOvT84xn.'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, drop the login jack<\/code> using the DROP LOGIN<\/code> statement:<\/p>\n\n\n
DROP<\/span> LOGIN jack;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement was completed successfully because the login jack<\/code> has no dependency. Typically, you use this scenario when you create a login with the wrong intended name.<\/p>\n\n\n\n
2) Using the DROP LOGIN statement to remove a login that maps to a database user #<\/a><\/h3>\n\n\n\n
First, create a new login called joe<\/code> with a password:<\/p>\n\n\n
CREATE<\/span> LOGIN joe\nWITH<\/span> PASSWORD<\/span> = 'NBgs23we$'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new user for the login joe<\/code>:<\/p>\n\n\n
CREATE<\/span> USER<\/span> joe\nFOR<\/span> LOGIN joe;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, drop the login joe<\/code> using the DROP LOGIN<\/code> statement:<\/p>\n\n\n
DROP<\/span> LOGIN joe;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The user joe<\/code> becomes orphaned.<\/p>\n\n\n\n
To get all orphaned users in the current database server, you use the following query:<\/p>\n\n\n
SELECT<\/span>\n  dp.type_desc,\n  dp.sid,\n  dp.name AS<\/span> user_name\nFROM<\/span> sys.database_principals AS<\/span> dp\nLEFT<\/span> JOIN<\/span> sys.server_principals AS<\/span> sp\n  ON<\/span> dp.sid = sp.sid\nWHERE<\/span> sp.sid IS<\/span> NULL<\/span>\nAND<\/span> dp.authentication_type_desc = 'INSTANCE'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
3) Resolve an orphaned user #<\/a><\/h3>\n\n\n\n
To resolve an orphaned user, you can recreate a missing login with the SID<\/code> of the database user. For example, the following statement creates a new login ocean<\/code> with the SID of the user joe<\/code>:<\/p>\n\n\n
CREATE<\/span> LOGIN ocean\nWITH<\/span> PASSWORD<\/span> = 'bNHXUYT321#'<\/span>,\n\t SID<\/span> = 0xC48461C284AE024EB42149BFDBCD18A8<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Now, the user joe<\/code> can log in to the database server.<\/p>\n\n\n\n
If you already have a login and want to map it with the orphaned user, you can use the ALTER USER<\/code> statement. For example:<\/p>\n\n\n
ALTER<\/span> USER<\/span> orphaned_user \nWITH<\/span> LOGIN = login_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
  • Use the DROP LOGIN<\/code> statement to delete a login account from the SQL Server.<\/li>
  • The DROP LOGIN<\/code> cannot delete a login account that is logged in or owns a securable, server-level object, or SQL Server Agent job.<\/li>
  • Drop a login that maps to database users will make these database users orphaned.<\/li><\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t