CODEWITHSUNDEEP
sql-serversqlclr
user2245759
user2245759

Reputation: 467

Permissions to execute CLR Triggers

I have inherited an application with CLR triggers and am trying to migrate the Database to another server.

My application user has db_owner on the Database but when it executes I get an error.

This appears to be permission related because when I elevate the app user to be a sysadmin, everything fires correctly.

But that seems like a super horrible "fix".

The source server also has the user as a sysadmin so I'm guessing they couldn't sort it out either. Just for fun I gave the user db_owner on the system tables as well since it appears its trying to read locks and other sys table items. Still no luck.

What permissions need to be granted to a user in order for them to execute a SAFE CLR trigger?

This trigger appears to be used for auditing. It fires when certain data has changed and records the difference, inspecting the inserted and deleted tables and query which tables have locks in order to determine where the activity is occurring.

Upvotes: 1

Views: 611

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48836

From a purely-SQLCLR perspective, there shouldn't be any special permissions needed for Assemblies marked as SAFE. Assemblies marked as either EXTERNAL_ACCESS or UNSAFE require a little bit of extra setup. There are, however, two permissions-related issues to look at:

  1. All T-SQL submitted from within SQLCLR objects is, by its very nature of coming from application code and being submitted on-the-fly (and hence not pre-processed), Dynamic SQL. And Dynamic SQL breaks the ownership chain that otherwise allows the execution of a Stored Procedure to infer the permissions for the underlying objects. This is easy enough to fix by doing:

    • Sign the Assembly / DLL
    • Create an Asymmetric Key in the Database containing the Assembly FROM the Assembly
    • Create a User in the Database containing the Assembly FROM the Asymmetric Key
    • Grant the new User the appropriate permissions for the objects accessed in the Trigger
    • When the Trigger executes, it will inherit the permissions of this new User

    However, it is probably not the case that this is the actual issue since you have placed your app User into the db_owner database role, which should have permission to access all objects in the database (assuming no explicit DENYs are in place).

  2. Since you mention that the Trigger is looking at lock info, assuming that is coming from sys.dm_tran_locks, then the app Login needs the VIEW SERVER STATE permission which is server-level (hence app Login and not app User, which is database-level). I doubt placing a User in the db_owner database role would infer this permission because that role is confined to the database. You also mention that adding the Login to the sysadmin server role allows the Trigger to work, which appears to be further evidence that this is the probably the real issue.

Upvotes: 1

Related Questions