Summary
When Installing or upgrading to Venafi Trust Protection Platform 19.1, you get an error when running the sample-grants.sql script that says:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.
Applies To
Venafi Trust Protection Platform version 19.1.0
More Details
In 19.1 the sample-grants.sql script was enhanced so that your DB service account username only needs to be changed in one location in the script instead of doing a find and replace. Unfortunately during this refactor some of the required brackets were removed which allowed the script to support special characters in the username.
Without the brackets, if the database service account had special characters in the name like a period or a blackslash, then the script will return an error and the grants do not get applied to the user.
Resolution
An updated version of the sample-grants.sql script is available that resolves the error described above.
-- Sample script that shows the permissions necessary for the Trust Protection Platform
-- to work with a Microsoft SQL Server user with limited permissions.
-- This script needs to be run as the system administrator.
-- please read the script carefully before running.
--
-- Please consult with your DBA to determine the best way to set this up in your
-- environment.
--
-- In the below script, replace 'DIRECTOR_RW' with the username of the account that
-- will be used in the Trust Protection Platform.
-- This script should be run during new install or upgrade.
-- ============ To grant stored procedure and type access a non-owner DB user ==========
-- As sa or DBA user
-- =====================================================================================
DECLARE @username NVARCHAR(MAX)
-- Replace DIRECTOR_RW with the username of the DB account
-- For Windows Authentication Users, the format of the username needs to be in NTLM format. Ex: domain\username
SET @username = 'DIRECTOR_RW'
-- The user will also need data_reader and data_writer
EXEC sp_addrolemember db_datareader, @username
EXEC sp_addrolemember db_datawriter, @username
EXEC ('GRANT EXECUTE ON TYPE::dbo.ConfigObjectRels TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON TYPE::dbo.ConfigObjectRelsKeyed TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON TYPE::dbo.ConfigObjectChildRightsRels TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON TYPE::dbo.ConfigObjectChildRightsPrincipals TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON TYPE::dbo.DAL_V_LOG_TYPE TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON TYPE::dbo.DAL_LOG_T_QUEUE_MESSAGES TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON TYPE::dbo.LargeIntegers TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON TYPE::dbo.SmallIntegers TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON TYPE::dbo.ToDo_Request_Messages TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON TYPE::dbo.ToDo_Requests TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON TYPE::dbo.ToDo_Requests_Defer TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.RefreshConfigObjectRels TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.RefreshConfigObjectChildRels TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_BULK_ENQUEUE TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_BULK_DEQUEUE TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.DAL_SP_V_LOG_LOG_BI TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_LOG_LOG_EXPIRE_COMP TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_LOG_LOG_EXPIRE_DATA TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_LOG_LOG_EXPIRE_PARAMS TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_LOG_LOG_EXPIRE_SOURCE_IP TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_LOG_LOG_EXPIRY TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.ven_request_applock TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.ToDo_Request_Dequeue TO [' + @username + ']')
EXEC ('GRANT EXECUTE ON OBJECT::dbo.ToDo_Request_Enqueue TO [' + @username + ']')
-- Must have receive service on FROM queue to initiate dialogs
EXEC ('GRANT RECEIVE ON LogClientQueue TO [' + @username + ']')
-- Must have receive service on FROM queue to retrieve from queue.
EXEC ('GRANT RECEIVE ON LogServerQueue TO [' + @username + ']')
Comments