Two stored procedures. The first one gets all the inactive users, the second one drops one user at a time
use dynamics
go
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'dd_DropLogin'
AND type = 'P')
DROP PROCEDURE dd_DropLogin
GO
CREATE PROCEDURE dd_DropLogin
-- dbo.dd_DropLogin
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @sql VARCHAR(MAX)
declare @user VARCHAR(MAX)
DECLARE @interid varchar(10)
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
SELECT top 1
l.name as sqlname
FROM sys.sql_logins l
JOIN DYNAMICS..SY01400 U ON l.name = U.USERID
where u.UserStatus = 2
ORDER BY name;
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @user
if @@fetch_status <> 0 begin
break
end
--loop through each dynamics db
DECLARE curName2 CURSOR LOCAL FAST_FORWARD FOR
select rtrim(INTERID)
from DYNAMICS..sy01500
OPEN curName2
WHILE 1=1
BEGIN
FETCH NEXT FROM curName2 INTO @interid
if @@fetch_status <> 0 begin
break
end
--drop schema, roles
exec dd_DropLogin2 @interid, @user
END
CLOSE curName2
DEALLOCATE curName2
-- drop user
SET @sql = 'DROP login [' + @user + ']';
PRINT @sql;
EXEC (@sql);
delete sy01400 where userid = @user
END
CLOSE curName
DEALLOCATE curName
GO
grant exec on dd_DropLogin to public
use dynamics
go
--
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'dd_DropLogin2'
AND type = 'P')
DROP PROCEDURE dd_DropLogin2
GO
CREATE PROCEDURE dd_DropLogin2
-- dd_DropLogin2 'havis','aabrahim'
@interid varchar(10),
@user VARCHAR(MAX)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--print 'db: ' + @interid
--print '@user: ' + @user
--==============================================================================================
-- alter schemas for user
--==============================================================================================
DECLARE @schema_name VARCHAR(MAX);
declare @sql varchar(max)
set @sql = 'DECLARE c_schema CURSOR FOR SELECT name FROM ' + rtrim(@interid) + '.sys.schemas WHERE USER_NAME(principal_id) = ' + QUOTENAME( @user,char(39))
exec (@sql)
OPEN c_schema;
FETCH NEXT FROM c_schema INTO @schema_name;
WHILE (@@FETCH_STATUS = 0) BEGIN
DECLARE @sql_schema VARCHAR(MAX);
SELECT
@sql_schema = 'use ' + @interid + ';ALTER AUTHORIZATION ON SCHEMA::[' + @schema_name + '] TO [dbo]';
PRINT @sql_schema;
EXEC (@sql_schema);
FETCH NEXT FROM c_schema INTO @schema_name;
END;
CLOSE c_schema;
DEALLOCATE c_schema;
-- dd_DropLogin2 'havis','aabrahim'
--==============================================================================================
-- alter roles for user
--==============================================================================================
DECLARE @dp_name VARCHAR(MAX);
set @sql = 'DECLARE c_database_principal CURSOR FOR SELECT name FROM ' + rtrim(@interid) + '.sys.database_principals WHERE type = ''R'' AND USER_NAME(owning_principal_id) = ' + QUOTENAME( @user,char(39))
exec (@sql)
OPEN c_database_principal;
FETCH NEXT FROM c_database_principal INTO @dp_name;
WHILE (@@FETCH_STATUS = 0) BEGIN
DECLARE @sql_database_principal VARCHAR(MAX);
SELECT
@sql_database_principal = 'use ' + @interid + ';ALTER AUTHORIZATION ON ROLE::[' + @dp_name + '] TO [dbo]';
PRINT @sql_database_principal;
EXEC (@sql_database_principal);
FETCH NEXT FROM c_database_principal INTO @dp_name;
END;
CLOSE c_database_principal;
DEALLOCATE c_database_principal;
--==============================================================================================
-- drop roles for user
--==============================================================================================
DECLARE @role_name VARCHAR(MAX);
set @sql = 'DECLARE c_role CURSOR FOR SELECT dp.name FROM ' + rtrim(@interid) + '.sys.database_role_members drm JOIN ' + rtrim(@interid) + '.sys.database_principals dp ON dp.principal_id = drm.role_principal_id WHERE USER_NAME(member_principal_id) = ' + QUOTENAME( @user,char(39))
--print @sql
exec (@sql)
OPEN c_role;
FETCH NEXT FROM c_role INTO @role_name;
WHILE (@@FETCH_STATUS = 0) BEGIN
DECLARE @sql_role VARCHAR(MAX);
SELECT @sql_role = 'use ' + @interid + ';EXEC ' + rtrim(@interid) + '..sp_droprolemember N''' + @role_name + ''', N''' + @user + '''';
PRINT @sql_role;
EXEC (@sql_role);
FETCH NEXT FROM c_role INTO @role_name;
END;
CLOSE c_role;
DEALLOCATE c_role;
-- dd_DropLogin2 'havis','aabrahim'
GO
grant exec on dd_DropLogin2 to public
GO
--SP_SPS