August 3, 2011 at 8:51 am
We currently run Active Directory 2003 and many SQL Servers some are 2003 and some are 2008. We have our SQL Servers in a child domain and the users in the primary domain. In the past we added our users individually to Each SQL Server using the AD Account. (Currently we are trying to stick with Security Groups) The problem is when we delete a user from the AD it does not delete from the SQL Server. This is a problem when we get audited because the auditors think the user still has access to the Database and it also makes it messy. Currently when people leave we have to go through every SQL Server and run the below Query to delete the "Ghost" accounts. I would like to know two things. 1. Does anyone else experience this problem with AD and SQL? If not is there a fix for this issue? 2. If there is no fix is there an easier way to run the below query on multiple servers? Currently using SQL Tools 2008
DECLARE @User VARCHAR( 500 )
DECLARE @user1 VARCHAR( 500 )
DECLARE @Cmd1 VARCHAR( 500 ),
@Cmd2 VARCHAR( 500 )
SELECT @User = 'Domain\username'
SELECT @user1 = 'username'
SELECT @Cmd1 = '
USE [?]
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N''' + @User + ''')
BEGIN
DROP SCHEMA [' + @User + ']
PRINT ''*** Found and dropped schema in DB - ? ***''
END
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = N''' + @User + ''')
BEGIN
DROP USER [' + @User + ']
PRINT ''*** Found and dropped user in DB - ? ***''
END'
EXEC SP_MSFOREACHDB @Command1 = @Cmd1
SELECT @Cmd1 = '
USE [?]
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N''' + @user1 + ''')
BEGIN
DROP SCHEMA [' + @user1 + ']
PRINT ''*** Found and dropped schema in DB - ? ***''
END
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = N''' + @user1 + ''')
BEGIN
DROP USER [' + @user1 + ']
PRINT ''*** Found and dropped user in DB - ? ***''
END'
EXEC SP_MSFOREACHDB @Command1 = @Cmd1
USE Master
IF EXISTS ( SELECT * FROM sys.server_principals WHERE name = @User )
BEGIN
SELECT @Cmd2 = 'DROP LOGIN [' + @User + ']'
EXEC( @Cmd2 )
END
IF EXISTS ( SELECT * FROM sys.server_principals WHERE name = @user1 )
BEGIN
SELECT @Cmd2 = 'DROP LOGIN [' + @user1 + ']'
EXEC( @Cmd2 )
END
August 3, 2011 at 9:16 am
You can add AD groups in SQL Server
For multi server execution you can setup a SQLCMD script Multi-Server Execution
August 3, 2011 at 10:06 am
Thank you, Alex. I'm going with the SQL2008 option of running the query on a group of servers. It's working good. Just wish there was a way to prevent these orphaned users from being created in the first place. Sounds like my only option is to get everything into groups.
August 4, 2011 at 9:01 am
Marty
In policy management I think you can setup facet for "user must belong to a group" or something similar.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply