Having trouble with dynamic sql

Category: sql server transactsql

Question

Bryan_P_Murtha on Fri, 12 May 2017 14:53:27


Hi All,

     I have to restore a couple of databases after they back up to a UAT server. It's a dated backup so it has to change each night. This runs on the Agent and completes each night with no errors but it doesn't work. The users can't login with the user CaseTrackerApp. Can someone please help me?

-- ADD CASETRACKER LOGIN


IF

  EXISTS(SELECT * FROM sys.server_principals WHERE name = N'CaseTrackerApp')


DROP LOGIN [CaseTrackerApp]


GO



-- ADD CASETRACKER LOGIN


IF

  NOT EXISTS(SELECT * FROM sys.server_principals WHERE name = N'CaseTrackerApp')


CREATE LOGIN [CaseTrackerApp] WITH PASSWORD=N'APassword', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

DECLARE

@CurrentDayCaseTracker varchar(200)


DECLARE

@CurrentDayCaseTrackerLookback varchar(200)


DECLARE

@Query nvarchar(2000)


SELECT

@CurrentDayCaseTracker = 'CaseTracker_'  + CONVERT(VarChar, GetDate()-1, 112)


SELECT

@CurrentDayCaseTrackerLookback = 'CaseTracker_Lookback'  + CONVERT(VarChar, GetDate()-1, 112)


SET

@Query=  'USE ' +  @CurrentDayCaseTracker + '; '


SET

@Query =+ @Query +'IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''CaseTrackerApp'')'


SET

@Query =+ @Query + ' DROP USER [CaseTrackerApp]'


PRINT

@Query


EXECUTE

sp_executesql@Query


SET

@Query = ''


SET

@Query= ' IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''CaseTrackerApp'') '


SET

@Query =+ @Query + ' BEGIN '


SET

@Query =+ @Query + ' CREATE USER [CaseTrackerApp] FOR LOGIN [CaseTrackerApp] WITH DEFAULT_SCHEMA=[dbo] '



SET

@Query =+ @Query + ' EXEC sp_addrolemember ''db_datareader'', ''CaseTrackerApp'' '


SET

@Query =+ @Query + ' EXEC sp_addrolemember ''db_datawriter'', ''CaseTrackerApp'' '


SET

@Query =+ @Query + ' GRANT ALTER, EXECUTE TO [CaseTrackerApp] '


SET

@Query =+ @Query + ' END '


PRINT

@Query


EXECUTE

sp_executesql@Query


SET

@Query = ''


SET

@Query=  'USE ' +  @CurrentDayCaseTrackerLookback + '; '


SET

@Query =+ @Query +'IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''CaseTrackerApp'')'


SET

@Query =+ @Query + ' DROP USER [CaseTrackerApp]'


PRINT

@Query


EXECUTE

sp_executesql@Query


SET

@Query = ''


SET

@Query= ' IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''CaseTrackerApp'') '


SET

@Query =+ @Query + ' BEGIN '


SET

@Query =+ @Query + ' CREATE USER [CaseTrackerApp] FOR LOGIN [CaseTrackerApp] WITH DEFAULT_SCHEMA=[dbo] '



SET

@Query =+ @Query + ' EXEC sp_addrolemember ''db_datareader'', ''CaseTrackerApp'' '


SET

@Query =+ @Query + ' EXEC sp_addrolemember ''db_datawriter'', ''CaseTrackerApp'' '


SET

@Query =+ @Query + ' GRANT ALTER, EXECUTE TO [CaseTrackerApp] '


SET

@Query =+ @Query + ' END '


PRINT

@Query


EXECUTE

sp_executesql@Query


SET

@Query = ''

Replies

Olaf Helper on Fri, 12 May 2017 16:28:20


PRINT @Query

You print out the dynamic SQL statement; when you run the final SQL statement in SSMS does it work then?

Tom Phillips on Fri, 12 May 2017 16:42:49


Why are you dropping and recreating the login every time?  This will create a new GUID and your database code is then only creating a new database user if it doesn't exist, but now it is "orphaned".

Please see:

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server#to-resolve-an-orphaned-user

Jonathan Percoma on Fri, 12 May 2017 18:36:07


Instead of using sp_executesql in you Dynamic SQL, put it in a SELECT statement the @Query.
Then manually run the T-SQL statements.
Also put error handling on it, this is very import specially for Dynamic SQL and check if the SQL your storing in the variable is empty or NULL.
And isn't the User's are included in your backup already?