why cannot i GRANT EXECUTE permission on assemblies anymore ?

  • Article

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.

Thanks,

Comments

  • Anonymous
    February 26, 2007
    > CLR supports a rich security model that lets you think about granting security to the code in the assembly ( code access security ). True, but this could be problematic for database security.  In general, DBAs must not assume that developers understand security as it will be implemented in the database.  However, developers may be involved in creating CLR assemblies that will be registered in the database.  Assembly-level permissions give (or would have given) the DBA an appropriate level of granularity for enforcing appropriate permissions in the database instance.  Would this really be so hard to implement?  I don't think that pushing database-level security into the CLR is going to be a good idea.  It certainly won't help Enterprise-Level DBAs (who are already cautious about SQL-CLR, frequently for good reasons) to become more eager to implement CLR support in their databases. Your thoughts? Cheers, Chris Leonard

  • Anonymous
    February 28, 2007
    The comment has been removed

  • Anonymous
    February 28, 2007
    The comment has been removed

  • Anonymous
    November 17, 2008
    Workaround here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101984