Grant permissions only if an object exists, but without mentioning the object twice

Question

We have a number of database build scripts that grant permissions to specific objects (sprocs, functions, etc) only to specific roles. We have recently moved to an automated deployment model that has certain scripts called every time, but this has resulted in some errors when a permission is added for something that isn't yet on the production branch (we're currently working around this by having the script only run on certain types of deployments).

One of my colleagues suggested putting the permission behind a check for the object, such as the following, but we're both concerned about the possibility for manual error if only one of the references to someStoredProcedure gets updated (e.g. when it's copied for a new object, or updated for a renamed one):

IF OBJECT_ID(N'dbo.someStoredProcedure',N'P') IS NOT NULL GRANT EXECUTE ON dbo.someStoredProcedure TO SomeRoleGettingPermission

I know that there have been updates to SQL Server since 2005 that allow one-mention syntax with some one-argument functions — such as DROP TABLE IF EXISTS TableName rather than previous patterns like IF OBJECT_ID(N'dbo.TableName',N'U') IS NOT NULL DROP TABLE TableName — but I can't see how you could apply that syntax to a two-argument function like GRANT, and I can't find anything that suggests a relevant pattern.

Can anyone suggest an analagous pattern for granting permissions, or can it only be done by mentioning the object twice?

Answer 1

DECLARE @ObjectName NVARCHAR(100) = N'dbo.someStoredProcedure';
DECLARE @GrantStatement NVARCHAR(MAX);

IF OBJECT_ID(@ObjectName, N'P') IS NOT NULL
BEGIN
    SET @GrantStatement = N'GRANT EXECUTE ON ' + @ObjectName + N' TO SomeRoleGettingPermission;';
    EXEC sp_executesql @GrantStatement;
END

In this example, the variable @ObjectName holds the name of the object you want to grant permission to, and the IF condition checks if the object exists. If the object exists, the GRANT statement is dynamically constructed using the @ObjectName variable and then executed using sp_executesql.