Using below scripts
- Fixes all orphaned users in the current database if a login exists
- If a login does not exist in thesql server instance
- User will be removed — @fix = 0
- If a user is a schema owner then the schema is transferred to DBO and then user is removed from DB
- or A login will be created — @fix = 1
- User will be removed — @fix = 0
DECLARE @t TABLE (
id INT IDENTITY(1, 1)
,username SYSNAME
,usersid VARBINARY(85)
);
DECLARE @username SYSNAME = ”;
DECLARE @loopid INT = 0D
ECLARE @DeleteCmd NVARCHAR(1000) = ”
DECLARE @fix BIT = 0; — if it is set 0 then all the orphans will be removed
— if its is set 1 orphans will be created and mapped to the user with a default passwordINSERT INTO @t (
username
,usersid
)
EXEC sp_change_users_login @Action = ‘Report’;SET @loopid = @@IDENTITY;WHILE @loopid > 0
BEGIN
SET @username = (
SELECT username
FROM @t
WHERE id = @loopid
)
IF EXISTS (
SELECT 1
FROM master.dbo.syslogins
WHERE NAME = @username
)
BEGIN
EXEC sp_change_users_login @Action = ‘update_one’
,@UserNamePattern = @username
,@LoginName = @username;
PRINT CONVERT(VARCHAR, @username) + ‘User updated succesfully ‘
END
ELSE
BEGIN
IF @fix = 0
BEGIN
SELECT @DeleteCmd = @DeleteCmd + (
CASE
WHEN s.NAME IS NULL
THEN ”
ELSE ‘ALTER AUTHORIZATION ON SCHEMA::’ + ‘[‘ + s.NAME + ‘] TO dbo’ + ‘;’
END
) + ‘DROP USER ‘ + ‘[‘ + dp.NAME + ‘]’ + ‘;’
FROM sys.database_principals dp
LEFT OUTER JOIN sys.schemas s ON s.principal_id = dp.principal_id
WHERE type IN (
‘s’
,‘G’
,‘U’
)
AND dp.principal_id > 4
AND dp.NAME = @username
PRINT CONVERT(VARCHAR, @username) + ‘User Removed succesfully ‘
END
ELSE
BEGIN
EXEC sp_change_users_login ‘Auto_Fix’
,@username
,NULL
,‘Auto_Fix_Pass’;
PRINT CONVERT(VARCHAR, @username) + ‘ User has added to sql server with password Auto_Fix_Pass succesfully ‘
END
END
SET @loopid = @loopid – 1;
ENDPRINT @DeleteCmdEXEC sp_executesql @DeleteCmd
— Print un resolved db accounts EXEC sp_change_users_login @Action = ‘Report’;
Sqljunkieshare 