Script to be run when database platform upgraded to SQL Server Enterprise

This is something that you can do in PROD only.  Once Hosting updates the SQL Server and runs these scripts below, deliver a ticket to the wallet and SCAN a digital ticket.

Please send the following to Hosting for completion in PROD (you can add  laura.zehe@true-tickets.com in case they have any questions):

1. Enable OLE Automation Procedures on the database:

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'Ole Automation Procedures';

GO

2. Re-grant permissions to the Access Control / N-Scan Database User using the script below.  Note that $(database) needs to be replaced with the new name of the SQL Server database (WRITHEUSIL0). For Access Control (v16), the database SQL User is the same as the "Services Database Database User Name" (from TIM) that the Tessitura API / services use.

use [$(database)]

go

declare @ac_db_user_id varchar(128) = 'tessApi'

declare @sql varchar(500)

set @sql = 'use [master]' + ' '

   + 'if not exists(select 1 from sys.database_principals where name = ''' +  @ac_db_user_id + ''')' + ' '

   + 'CREATE USER ' + @ac_db_user_id + ' FOR LOGIN ' + @ac_db_user_id

exec(@sql);

exec('use [master] GRANT EXECUTE ON [sys].[sp_OACreate] TO ' + @ac_db_user_id )

exec('use [master] GRANT EXECUTE ON [master].[sys].[sp_OADestroy] TO ' + @ac_db_user_id )

exec('use [master] GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO ' + @ac_db_user_id )

exec('use [master] GRANT EXECUTE ON [sys].[sp_OAMethod] TO ' + @ac_db_user_id )

go