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]'
@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 '
@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]'
@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 '
@Query
EXECUTE
sp_executesql@Query
SET
@Query = ''
Replies
Olaf Helper on Fri, 12 May 2017 16:28:20
You print out the dynamic SQL statement; when you run the final SQL statement in SSMS does it work then?PRINT @Query
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?