SQL Source Control 6

Managing role memberships

Post-Deployment script to add an environment specific role member

IF @@SERVERNAME = 'test-server'
	DECLARE @return_status int
	EXEC @return_status = sp_addrolemember 'db_owner', 'a.tester'
	IF @return_status <> 0 SET NOEXEC ON

Notable points:

  • Post-Deployment scripts must be re-runnable as they will be executed as part of every deployment.
  • Post-Deployment scripts are deployed to every environment.
    • @@SERVERNAME is used to only grant a role membership to a.tester on the test-server server.
  • By default there is no error handling, so this script tests for an error, and sets NOEXEC to ON if an error occurs.

Didn't find what you were looking for?