BPSoftware.com Blog

Tuesday, November 11, 2008

SQL Delete/Drop a User from each Database

Deleting a user from SQL server deletes them from the security system, however it does not remove them from databases they have 'securables' (permissions speficified) for. The following procedure will remove a user from each database and remove their account.

1 USE [master]
2 GO
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7 CREATE procedure [dbo].[sp_DeleteUser]
8 @username sysname = NULL
9 AS
10
11 -- declare @nam as sysname
12 declare @cmd1 as varchar(500)
13
14 if @username is not null
15 begin
16 set @cmd1 = 'PRINT ''?'' USE ? DROP USER ' + @username
17 exec sp_msforeachdb @command1= @cmd1
18 exec ('DROP LOGIN ' + @username)
19 return(0)
20 END
21 return (1)

The stored procedure is called like;

1 exec sp_DeleteUser 'loginname' /* where loginname is the name of the account that you would like deleted */

Labels: Code, How-To, SQL

posted by Brad Prendergast at 8:45:00 PM

Comments: