why cannot i GRANT EXECUTE permission on assemblies anymore ?

Published Mar 23 2019 04:51 AM 223 Views
First published on MSDN on Feb 22, 2007

Just a quick note that this is by design and no you don't need to use it.

What are we talking about ?

Books online talks about granting assembly permissions. You used to do

GRANT execute on ASSEMBLY :: <assembly_name> to <database_principal > with SQL Server 2005

Your database holds your assemblies and as such you could grant / revoke limited set of permissions. We initially allowed you to grant execute permission on the assembly but now when you execute the same on a server with the SP2  service pack, you get the following :

Msg 102, Level 15, State 1, Line 0

Incorrect syntax near 'EXECUTE...'.

Why did this change ?

Execute permission gave a false sense of security and was really inappropriate. Here's why:

As we shipped SQL Server 2005, this permission set was supported in TSQL but never enforced. So you could say "deny execute on assembly" but nothing happened. With this fix to revert the support from TSQL, you will not be misled anymore and we wanted to get this fix to you in a service pack of the same product that introduced you this immensely easy way of expressing your logic in assemblies. Going forward, with database upgrades to next release, any lingering  assemblies that display this permission will be automatically corrected. And so you don't have to worry about a thing.

So how can i secure assemblies registered to the server ?

The assembly is really a container for types, objects etc which are independently permissible. The assembly itself can be marked safe, external_access and unsafe. In addition, you can use the 'trustworthy' property of the database for similar effect. For a complete list with description of our security model, please refer CLR Integration security in Books Online.


Version history
Last update:
‎Mar 23 2019 04:51 AM
Updated by: