I am creating a script where I have to fix orphan user (app service account) when db gets refreshed from Devl --> Test --> Prod. Since my company sells this software, it is essentially huge number of servers and I alone cannot manage it. So, I decided to create a script and give just that permission to application support people so that they can run that script which should fix orphan user.
This is the script
CREATE PROCEDURE [dbo].[usp_FixPermissionsAfterDBRefresh] AS BEGIN DECLARE @dbname VARCHAR(50) DECLARE @sqlstring NVARCHAR(100) DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases WHERE name NOT in ('master','msdb','model','tempdb') OPEN db_cursor FETCH NEXT from db_cursor into @dbname WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlstring = N'USE ' + @dbname EXECUTE sp_executesql @sqlstring print @sqlstring --EXEC sp_change_users_login 'Auto_Fix', 'SomeUser' IF EXISTS (SELECT dp.name As Orphan_Users FROM sys.database_principals dp left join sys.server_principals sp ON dp.sid=sp.sid WHERE sp.name IS NULL AND dp.type='S' AND dp.name NOT IN ('guest','INFORMATION_SCHEMA','sys')) BEGIN ALTER USER SomeUser WITH LOGIN = SomeUser ALTER ROLE [db_backupoperator] ADD MEMBER [SomeUser] ALTER ROLE [db_owner] ADD MEMBER [SomeUser] END FETCH NEXT FROM db_cursor INTO @dbname END CLOSE db_cursor DEALLOCATE db_cursor END GO
If I run this script from any database except the one that got restored, the script exits successfully but won't fix orphan user and grant permissions. However, if I run this script from the newly refreshed DB then the script works just fine and fixes orphan user and grants necessary permissions. I have searched online but couldn't really get this script working. I will be setting this up as an agent job and grant permissions to application support who will be performing db refresh.
Please advise on what I am doing wrong.