I have the following in my post deployment script in Visual Studio 2013:
-- Drop existing role and create again to be up to date
DROP ROLE payments_data_access_role
GO
CREATE ROLE payments_data_access_role
GO
However when I publish the database from VS, I get the error that the role 'payments_data_access_role' does not exist.
How can I rewrite this so that if the role exists, drop the role and then redefine the role?
Thank you.
UPDATE: I tried the following and it worked:
IF IS_ROLEMEMBER ('payments_data_access_role') IS NOT NULL
DROP ROLE payments_data_access_role
CREATE ROLE payments_data_access_role
GO
Is this the correct approach? Could you provide your views on this? Thank you.
IS_ROLEMEMBER would work like you're using it.
For a deployment, I would just wrap it in a try/catch:
BEGIN TRY
PRINT 'Trying to drop role paymens_data_access_role...';
DROP ROLE payments_data_access_role;
Print 'Role payments_data_access_role dropped.';
END TRY
BEGIN CATCH
PRINT 'Role payments_data_access_role does not exist, not dropping...';
END CATCH;
CREATE ROLE payments_data_access_role;
That way you get feedback in your deployment log and the error does not stop execution. It's also a little more apparent what the intention is compared to using a function that checks to see if a user belongs to a role. Might make it easier for you out in the future when you have to come back to this code after a while, or for the next person who's deciphering your code.