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?

  • 3
    I suggest you fix your build process.
    – Dan Guzman
    Jul 15, 2022 at 18:33

1 Answer 1

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

    SET @GrantStatement = N'GRANT EXECUTE ON ' + @ObjectName + N' TO SomeRoleGettingPermission;';
    EXEC sp_executesql @GrantStatement;

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.