CODEWITHSUNDEEP
.netsqlclrdatabase-permissions
Andy
Andy

Reputation: 338

Specifying granted permissions to SQL CLR User Defined Functions for deployment

I have created a user defined function in .NET 2.0 and am able to successfully deploy it to my local SQL Server instance. However, I need to grant a specific user EXECUTE permission on the function. I want this to be done in the CLR method itself so that I don't have to manually re-grant the permission every time I redeploy the UDF. Deploying the CLR function from Visual Studio generates this function in the database:

ALTER FUNCTION [dbo].[MyFunction](@input [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [LibName].[UserDefinedFunctions].[MyFunction]
GO

Is there a way I can have it tack this on:

GRANT EXECUTE ON [dbo].[MyFunction] TO [wwwUser] AS [dbo]

Thanks.

Upvotes: 2

Views: 1896

Answers (1)

Alasdair C-S
Alasdair C-S

Reputation: 364

Yes - see MSDN - you just create a script called PostDeployScript.sql in your project at the root and it gets automagically appended to the build output.

Note that in VS2012, the Add... dialog has an option to add a script of type "Script (PostDeployment)" or similar - but it then adds the customary "1" to the filename, and that then doesn't work. I've not bothered looking further, although it is also mentioned in MSDN, because the first method just works.

Upvotes: 1

Related Questions