T-SQL C# CLR Execute permissions being lost on assemblies when are redeployed

Question

I have inherited a VS2015 .NET C# project that deploys C# assemblies as CLR stored procedures to SQL Server 2016. It has worked seamlessly for many years.

However recently I've noticed that when I redeploy (a.k.a "Publish Database"), one of the assemblies seems to lose EXECUTE permissions for one of the users.

Is there a way I can have the project reapply the EXECUTE permissions after it has redeployed? The script would have to know which SQL Server instance and database I'm deploying the updated CLR to. Thanks

Answer 1

User are never given execute permissions to an assembly. I suspect you are meaning execute permissions to one (or more)of the SQLCLR stored procedures that reference code within this assembly. You can fairly easily add a post-release T-SQL script to your Visual Studio project that will get included in the publish scripts (by SSDT via VS). In this T-SQL script you just add the GRANT statement. And, if it only makes sense in one particular database / instance, then you can wrap that GRANT statement in an IF block that checks for the current instance and/or database names.

Steps for adding a post-release script in VS 2015:

1. Select project in Solution Explorer
2. Go to "Project" menu
3. Select "Add New Item..."
4. Select "SQL Server" -> "User Scripts"
5. Select "Post-Deployment Script"
6. Type a name for the script down in the "Name:" field
7. Click the "Add" button
8. Edit the new SQL script to add the GRANT statement(s)

The contents of that script will be added to the end of any deployment / publish script generated by VS / SSDT, and hence will execute in every DB in which you execute those scripts.