Script to delete a login and associated users from SQL Server
Occasionally, you might need to drop a single login from a SQL Server, however if that login has users in a lot of databases, it may be tedious to delete the associated user account from every database. The script shown below performs the necessary action for you. As-is, the script is configured to run in “debug” mode, where it will simply print out the dynamically-created T-SQL script that would be executed if you’d set the @DebugOnly parameter to 0. You should modify the @ServerPrincipalName to reflect the name of the login you want to delete, and the @DatabasePrincipalName to the name of the associated user that should be removed from any databases where it is present. Typically, both parameters will be set to the same value, however in some cases the login and user are created with slightly different names; perhaps the login is an Active Directory account with the domain name, whereas the user does not include the domain name.
Poor Faceless Suit!
The script dynamically constructs the required DROP LOGIN and DROP USER T-SQL statements such that all users matching @DatabasePrincipalName are dropped, then the login matching @ServerPrincipalName is dropped.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
SET NOCOUNT ON; DECLARE @DebugOnly bit = 1; --switch to 0 to actually drop the login and user(s); 1 displays the generated script. DECLARE @cmd nvarchar(MAX); DECLARE @ServerPrincipalName sysname; DECLARE @DatabasePrincipalName sysname; SET @ServerPrincipalName = N'DeleteMeLogin'; --the name of the LOGIN to be dropped from the SERVER. SET @DatabasePrincipalName = N'DeleteMeUser'; --the name of the USER to be dropped from each DATABASE. SET @cmd = ''; SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N' UNION ALL ' END + N'SELECT ''' + d.name + N''' , sp.name COLLATE SQL_Latin1_General_CP1_CI_AS , dp.name COLLATE SQL_Latin1_General_CP1_CI_AS FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp LEFT JOIN master.sys.server_principals sp ON dp.sid = sp.sid WHERE sp.name = ''' + @ServerPrincipalName + N''' OR dp.name = ''' + @DatabasePrincipalName + N'''' FROM sys.databases d WHERE d.state_desc = N'ONLINE'; SET @cmd = @cmd + N';' DECLARE @Results TABLE ( database_name SYSNAME NOT NULL , server_principal_name SYSNAME NULL , database_principal_name SYSNAME NULL , PRIMARY KEY CLUSTERED (database_name) ); INSERT INTO @Results (database_name, server_principal_name, database_principal_name) EXEC (@cmd); SET @cmd = N''; SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N' ' END + N' USE ' + QUOTENAME(r.database_name) + N'; PRINT (N''Dropping user ' + QUOTENAME(r.database_principal_name) + N' in ' + r.database_name + N'''); SET @msg = N''''; SELECT @msg = @msg + CASE WHEN @msg = N'''' THEN N'''' ELSE CHAR(13) + CHAR(11) END + N''User [' + @DatabasePrincipalName + N'] owns '' + o.type_desc + N'' '' + QUOTENAME(s.name) + N''.'' + QUOTENAME(o.name) + N'' and cannot be dropped'' FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE s.principal_id = (SELECT sp.principal_id FROM sys.database_principals sp WHERE sp.name = N''' + @DatabasePrincipalName + N''') PRINT (@msg); IF @msg <> N'''' BEGIN SET @errCount = @errCount + 1; END ELSE BEGIN IF ( SELECT (COUNT(1)) FROM sys.schemas s WHERE s.principal_id = (SELECT sp.principal_id FROM sys.database_principals sp WHERE sp.name = N''' + r.database_principal_name + N''') ) > 0 BEGIN PRINT (N''User ' + r.database_principal_name + N' owns a schema and cannot be dropped''); SET @errCount = @errCount + 1; END ELSE BEGIN DROP USER ' + QUOTENAME(r.database_principal_name) + '; END; END; ' FROM @Results r; SET @cmd = N'SET NOCOUNT ON; DECLARE @msg nvarchar(MAX); DECLARE @errCount int; SET @errCount = 0; BEGIN TRY ' + CASE WHEN @cmd = N'' THEN N' PRINT (N''User ' + @DatabasePrincipalName + N' does not exist in any database.'');' ELSE @cmd END + N' END TRY BEGIN CATCH PRINT (ERROR_MESSAGE()); SET @errCount = @errCount + 1; END CATCH IF @errCount = 0 BEGIN '; IF ( SELECT COUNT(1) FROM @Results r WHERE r.server_principal_name IS NOT NULL ) > 0 BEGIN SELECT TOP(1) @cmd = @cmd + N' USE master; PRINT (N''Dropping LOGIN ' + QUOTENAME(r.database_principal_name) + N'''); DROP LOGIN ' + QUOTENAME(r.server_principal_name) + N';' FROM @Results r; END ELSE BEGIN SET @cmd = @cmd + N' PRINT (N''Login ' + @ServerPrincipalName + N' does not exist.'');'; END SET @cmd = @cmd + N' END ELSE BEGIN PRINT (N''Errors occurred dropping users; login has NOT been dropped''); END'; IF @DebugOnly = 1 BEGIN RAISERROR (@cmd, 0, 1); END ELSE BEGIN EXEC sys.sp_executesql @cmd; END |
Let me know if you have any issues with the script, or if you’d like to make a suggestion for an improvement.
This post is part of our series on SQL Server Security.
Ciao, this script works great in most cases, but sometimes (when the db collation is different from the instance collation) I get these 2 errors:
– Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
– Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the not equal to operation.
Can you help me to solve this?
I’ll appreciate any kind of help.
Ciao
Enzo