2

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.

4

The problem resides here:

SET @sqlstring = N'USE ' + @dbname
EXECUTE sp_executesql @sqlstring

What's happening is this executes - changes the database in the execution context for sp_executesql but will not change the parent context. Later on the code assumes this to be true by not continuing to use dynamic sql which means it'll run in the original database context.

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'))

ALTER USER SomeUser WITH LOGIN = SomeUser
ALTER ROLE [db_backupoperator] ADD MEMBER [SomeUser]
ALTER ROLE [db_owner] ADD MEMBER [SomeUser]

This can be fixed by making it execute in the proper context by extending the dynamic sql.

  • Thanks. I got it working now. I have prepared the entire query in @sqlstring and then EXECUTE (@sqlstring). Now the permissions are working as expected.. – Sid Apr 15 '17 at 1:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.