{"id":466,"date":"2021-11-16T03:06:29","date_gmt":"2021-11-16T03:06:29","guid":{"rendered":"http:\/\/s1095387061.online-home.ca\/?p=466"},"modified":"2026-11-08T09:40:59","modified_gmt":"2026-11-08T15:40:59","slug":"mssqltips-tip-63","status":"publish","type":"post","link":"https:\/\/databaserefresh.com\/blog\/mssqltips-tip-63\/","title":{"rendered":"MSSQLTips: Tip #63 &#8211; DDL Trigger Example in SQL Server for Login Deletion"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Dropping SQL Server logins without dropping associated the database users creates orphaned database users. By default, the\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/drop-login-transact-sql?view=sql-server-ver15\" target=\"_blank\" rel=\"noreferrer noopener\">DROP LOGIN<\/a>\u00a0statement in SQL Server only removes logins, but not database(s) users that are linked to these logins. The best practice is to delete all databases users that are mapped to this login first and then delete the login itself. When logins are deleted, and users remain orphaned, DBAs have a cleanup task to perform from time to time. This is to make sure that an orphaned user is not misused (to prevent a potential security issue). How can we make sure that we don\u2019t forget to delete database users before dropping logins?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Please read the latest MSSQLTips post: &#8220;<a href=\"https:\/\/www.mssqltips.com\/sqlservertip\/7046\/ddl-trigger-example-in-sql-server-login-deletion\/\" target=\"_blank\" rel=\"noopener\" title=\"DDL Trigger Example in SQL Server for Login Deletion\">DDL Trigger Example in SQL Server for Login Deletion<\/a>&#8220;.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Check out all of my tips here: <a href=\"http:\/\/www.mssqltips.com\/sqlserverauthor\/94\/svetlana-golovko\/\" target=\"_blank\" rel=\"noreferrer noopener\">http:\/\/www.mssqltips.com\/sqlserverauthor\/94\/svetlana-golovko\/<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dropping SQL Server logins without dropping associated the database users creates orphaned database users. By default, the\u00a0DROP LOGIN\u00a0statement in SQL Server only removes logins, but not database(s) users that are linked to these logins. The best practice is to delete &hellip; <a href=\"https:\/\/databaserefresh.com\/blog\/mssqltips-tip-63\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":true,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[40,3,30],"tags":[],"class_list":["post-466","post","type-post","status-publish","format-standard","hentry","category-security","category-sql_server","category-tips-sql_server"],"_links":{"self":[{"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/posts\/466","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/comments?post=466"}],"version-history":[{"count":4,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/posts\/466\/revisions"}],"predecessor-version":[{"id":495,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/posts\/466\/revisions\/495"}],"wp:attachment":[{"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/media?parent=466"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/categories?post=466"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/tags?post=466"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}