-- This script fixes users and logins for SQL Server Trackit databases with the Bamboo row level security
-- When a database is restored to another DB Server than the backup source, the database users are out of sync with
-- the database logins that are stored in the MASTER database.  This script checks and then fixes such errors.
-- The rest of the object ownership and permissions are correct AS-IS
--

select 
	ProductName ,
	REPLACE( STR(v.ReleaseMajorNumber,2)+'.'+STR(v.ReleaseMinorNumber,1)
	+'.'+STR(v.ReleaseRevisionNumber,3)+'.'+STR(v.ReleaseBuildNumber,4),' ','0') as "Build Number",
	CONVERT(VARCHAR(20),CreateDate,100)as "Installed On"
from 
	DatabaseDeploymentLog v
ORDER BY DatabaseDeploymentLogId

DECLARE @suffix VARCHAR(20),@userName VARCHAR(255), @Pattern VARCHAR(255) 
SELECT @userName = MAX(NAME) FROM sysusers WHERE NAME LIKE 'TrackitApp%'
SELECT @suffix = REPLACE(@userName,'TrackItApp','')
if @suffix is null 
 set @suffix = ''

-- Now fix the logins
PRINT 'Fixing User TRACKIT70'

exec sp_change_users_login @Action = 'Auto_Fix' , @UserNamePattern ='TRACKIT70', @LoginName = null, @Password ='BLUEOCEAN'

PRINT ''
PRINT 'Now fix the 8.x logins'
PRINT 'Trackit 8.x User Suffix detected as "'+@suffix+'"'

PRINT ''
set @pattern = 'TrackitApp'+@suffix
PRINT 'Fixing User '+@pattern
exec sp_change_users_login @Action = 'Auto_Fix' , @UserNamePattern = @Pattern, @LoginName = null, @Password ='TI_DB_P@ssw0rd'
set @pattern = 'TrackitRpt'+@suffix
PRINT ''
PRINT 'Fixing User '+@pattern
exec sp_change_users_login @Action = 'Auto_Fix' , @UserNamePattern = @Pattern, @LoginName = null, @Password ='TI_DB_P@ssw0rd'
set @pattern = 'TrackitSecurity'+@suffix
PRINT ''
PRINT 'Fixing User '+@pattern
exec sp_change_users_login @Action = 'Auto_Fix' , @UserNamePattern = @Pattern, @LoginName = null, @Password ='TI_DB_P@ssw0rd'
set @pattern = 'TRACKIT80'+@suffix
PRINT ''
PRINT 'Fixing User '+@pattern
exec sp_change_users_login @Action = 'Auto_Fix' , @UserNamePattern = @Pattern, @LoginName = null, @Password ='TI_DB_P@ssw0rd'
go
