question

SteveHood79 avatar image
SteveHood79 asked

Drop Unused / Orphaned Users

I'm looking to delete all unused user accounts on a server to keep things cleaned up with a specific focus on deleting accounts immediately after dropping a database. Because of that, I made it the table #DBList instead of using all databases, which, if the where clause populating that table was uncommented, would simulate which users would be unused if I dropped all the databases that started with H. However, I feel this code is incomplete. On a SQL 2008 instance it will return "DROP LOGIN [NT SERVICE\ClusSvc]", which doesn't make me feel comfortable. I could write in "AND name NOT LIKE 'NT Service%'", but specifically avoiding a name instead of linking it to a value somewhere leaves room for error. In this case, it's not something where you could probably shrug it off. Also, if anyone can point out any other flaws in this process, I would be very greatful. I'd rather have it pointed out by you than a user as you'd probably word it more politely. I do realize that my code picking up credentials is probably not needed, but I'd rather have a stray account left behind then an obscure problem later. Thank you for taking the time to look at this! Steve IF object_id('tempdb..#Orphans') IS NOT NULL BEGIN DROP TABLE #Orphans END IF object_id('tempdb..#DBList') IS NOT NULL BEGIN DROP TABLE #DBList END CREATE TABLE #Orphans ( sid VarBinary(85) ) CREATE TABLE #DBList ( DatabaseName sysname ) --DBs to look for username in (typically all) INSERT INTO #DBList SELECT name FROM sys.databases --WHERE name not like 'H%' --Assume everything is an orphan until proven to be used INSERT INTO #Orphans SELECT sid FROM sys.server_principals WHERE type IN ('S', 'U', 'G') --Not an orphan if in a server role DELETE #Orphans FROM #Orphans INNER JOIN sys.server_principals sp ON #Orphans.sid = sp.sid INNER JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id --Not an orphan if granted a server permission DELETE #Orphans FROM #Orphans INNER JOIN sys.server_principals sp ON #Orphans.sid = sp.sid INNER JOIN sys.server_permissions sperm ON sp.principal_id = sperm.grantee_principal_id WHERE sperm.permission_name NOT IN ('CONNECT SQL') --Not an orphan if used as a credential DELETE #Orphans FROM #Orphans INNER JOIN sys.server_principals sp ON #Orphans.sid = sp.sid INNER JOIN sys.credentials c ON sp.name = c.credential_identity --Not an orphan if used as a linked login DELETE #Orphans FROM #Orphans INNER JOIN sys.server_principals sp ON #Orphans.sid = sp.sid INNER JOIN sys.linked_logins ll ON sp.principal_id = ll.local_principal_id --Not an orphan if exists in a database EXEC sp_msforeachdb 'USE [?] DELETE #Orphans FROM #Orphans INNER JOIN sys.database_principals dbp ON dbp.sid = #Orphans.sid WHERE db_name() IN (SELECT DatabaseName FROM #DBList) ' SELECT Command = 'DROP LOGIN [' + sp.name + ']' FROM #Orphans o INNER JOIN sys.server_principals sp on o.sid = sp.sid
permissionsuserdrop
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
First of all, I must add that in controlled environments both CREATING and DROPPING Logins needs some documented approval before proceeding. For extreme caution, the logins are disabled to sort out any anomaly as it is easier to revert instead of removing all the permission OR Dropping the user. Now, in your case, you are post handling it. It would have been much easier if you handle it before dropping the database. All you need to do is to insert **only** those server principals which are mapped to the database users. Then, you can filter out them as you are doing it as above. But again, I would say try to get such process documented and with certain approval. This could lead to a lengthy process but the benefits would be overwhelming.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.