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?