How to FIX: Manage Orphaned users in SQL SERVER with a simple script

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

 


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’;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s