SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users

Question

Related Question: SQL Server script to delete accounts no longer in Active Directory

Whilst the above question points out the rather handy sp_validatelogins procedure, i'd like to extend this procedure to then drop the logins, search all databases & drop any related users.

Id also like to drop all SQL logins that have not been logged in for over X months - and again, drop any related users across all databases.

Before I go writing all this from scratch - I cant help but feel I'd be re-inventing the wheel - someone must surly have written a script to do this already but i'm not having much luck finding one!

If no one has a script up their sleeve to do all this, scripts to do parts of them would be just as good - I've no problem merging them into a single script.

Answer 1

I don't have a full script for the task. But maybe this script could be a start.

It returns a list of logins that don't have permissions granted. Using sp_msforeachdb it searches on all databases using sys.database_principals and sys.database_role_members. You could insert the result in a temp table and then do whatever you need to do with that list.

I'm sure I found this script some time ago somewhere and made some small modifications to fit my needs. But can't find where I found the original one. So sorry if no link provided to the author(s).

SET NOCOUNT ON
CREATE TABLE #all_users (db VARCHAR(70),
 SID VARBINARY(85),
 stat VARCHAR(50))
EXEC MASTER.sys.sp_msforeachdb
 'INSERT INTO #all_users 
 SELECT ''?'', CONVERT(varbinary(85), sid) , 
 CASE WHEN r.role_principal_id IS NULL AND p.major_id IS NULL 
 THEN ''no_db_permissions'' ELSE ''db_user'' END
 FROM [?].sys.database_principals u LEFT JOIN [?].sys.database_permissions p 
 ON u.principal_id = p.grantee_principal_id 
 AND p.permission_name <> ''CONNECT''
 LEFT JOIN [?].sys.database_role_members r 
 ON u.principal_id = r.member_principal_id
 WHERE u.SID IS NOT NULL AND u.type_desc <> ''DATABASE_ROLE'''
IF EXISTS 
 (SELECT l.name
 FROM sys.server_principals l
 LEFT JOIN sys.server_permissions p
 ON l.principal_id = p.grantee_principal_id
 AND p.permission_name <> 'CONNECT SQL'
 LEFT JOIN sys.server_role_members r
 ON l.principal_id = r.member_principal_id
 LEFT JOIN #all_users u
 ON l.sid = u.sid
 WHERE r.role_principal_id IS NULL
 AND l.type_desc <> 'SERVER_ROLE'
 AND p.major_id IS NULL
 )
BEGIN
 SELECT DISTINCT l.name LoginName,
 l.type_desc,
 l.is_disabled,
 ISNULL(u.stat + ', but is user in ' + u.db + ' DB', 'no_db_users') 
 db_perms,
 CASE 
 WHEN p.major_id IS NULL AND r.role_principal_id IS NULL THEN 
 'no_srv_permissions'
 ELSE 'na'
 END srv_perms
 FROM sys.server_principals l
 LEFT JOIN sys.server_permissions p
 ON l.principal_id = p.grantee_principal_id
 AND p.permission_name <> 'CONNECT SQL'
 LEFT JOIN sys.server_role_members r
 ON l.principal_id = r.member_principal_id
 LEFT JOIN #all_users u
 ON l.sid = u.sid
 WHERE l.type_desc <> 'SERVER_ROLE'
 AND ((u.db IS NULL
 AND p.major_id IS NULL
 AND r.role_principal_id IS NULL
 )
 OR (u.stat = 'no_db_permissions'
 AND p.major_id IS NULL
 AND r.role_principal_id IS NULL
 ))
 ORDER BY 1, 4
END
DROP TABLE #all_users

Sample result:

LoginName type_desc is_disabled db_perms srv_perms
app_agentjobreader SQL_LOGIN 0 no_db_permissions, but is user in mydb DB no_srv_permissions
app_web_general SQL_LOGIN 0 no_db_permissions, but is user in mydb_second DB no_srv_permissions
app_web_maker SQL_LOGIN 0 no_db_permissions, but is user in mydb_third DB no_srv_permissions

• Kin Shah: stop using the undocumented ms_foreachDB. There is A more reliable and more flexible sp_MSforeachdb