Fix Orphaned Users Automatically

Posted on April 8, 2019 by cesmngin Utility

When a database is attached or restored from one server to another, the logins IDS stored in the master database do not align with the login IDS stored in each user database. As result you may receive the following errors.

Msg 229, Level 14, State 1
“” permission denied on object “”.*s, database “”.*s, owner “”.*s

or

Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role “” already exists in the current database.

This happens because of a mismatch between their SIDs. The following script will automatically check orphaned users in all databases and fix them if any is found.

You may consider putting it in a Job as part of a Maintenance task.

Execution samples:

To Detect and FIX orphaned users run:

  1.  EXEC [dbo].[FixOrphanedUsers] ‘EXECUTE’

To Detect and generate scripts only run:

2. EXEC [dbo].[FixOrphanedUsers] ‘VIEW’

USE [DBA_Repository]
GO
/****** Object: StoredProcedure [dbo].[FixOrphanedUsers] Script Date: 2/22/2018 9:47:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: dbasvc.com
-- Create date: 05/05/2017
-- Description: Fixes orphaned users across all user databases. The stored procedure will only execute if the @action
-- parameter is equal to 'EXECUTE'
-- Execution sample:
-- EXEC FixOrphanedUsers 'EXECUTE'
-- EXEC FixOrphanedUsers 'VIEW'
-- =============================================
ALTER PROCEDURE [dbo].[FixOrphanedUsers]
@action VARCHAR(10) = NULL
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @username VARCHAR(25)
,@TSQL NVARCHAR(4000)
,@DBnameQuoted VARCHAR(100)
,@dbname VARCHAR(50)
DECLARE dbs CURSOR
FOR SELECT [name]
FROM sys.databases
WHERE database_id > 4
IF ISNULL(@action, '') = ''
BEGIN
RAISERROR ('You have to specify an action such as EXECUTE or VIEW', 16, 1)
GOTO FINALIZING
END
OPEN dbs;
FETCH NEXT FROM dbs
INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #Users ( Username SYSNAME
,UserSID VARBINARY(85)
);
CREATE TABLE #Logins ( Username sysname );
SET @DBnameQuoted = QUOTENAME(@DBname);
SET @TSQL = 'INSERT INTO #Users EXEC ' + @DBname
+ '.sys.sp_change_users_login ''report''';
EXECUTE sys.sp_executesql @TSQL;
SET @TSQL = 'INSERT INTO #Logins
select name
from ' + @DBnameQuoted + '.sys.server_principals
where type in (''U'',''S'',''G'')
and is_disabled = 0
';
EXECUTE sys.sp_executesql @TSQL;
DECLARE fixusers CURSOR
FOR SELECT Username
FROM #Users
WHERE Username IN ( SELECT Username
FROM #Logins );
OPEN fixusers;
FETCH NEXT FROM fixusers
INTO @username;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TSQL = 'EXEC ' + @DBnameQuoted
+ '.sys.sp_change_users_login ''update_one''' + ', '
+ '''' + @username + '''' + ', ' + '''' + @username
+ '''';
IF @action = 'EXECUTE'
EXECUTE sys.sp_executesql @TSQL;
ELSE
PRINT @TSQL
FETCH NEXT FROM fixusers
INTO @username;
END;
CLOSE fixusers;
DEALLOCATE fixusers;
DROP TABLE #Logins
DROP TABLE #Users
FETCH NEXT FROM dbs
INTO @dbname;
END;
FINALIZING:
CLOSE dbs;
DEALLOCATE dbs;
END