CLR - Pro SQL Server Internals - page 254

execute as user='Bob'
go
-- Success
select * from dbo.vOrdersTSQL
-- Failure - Bob needs to have SELECT permission on dbo.OrderLineItems table
select * from dbo.vOrdersCLR
revert
go
In Listing 13-3, we created two views: vOrdersTSQL and vOrdersCLR , which utilize T-SQL and CLR user-defined
functions. Both functions select the data from OrderLineItems table.
When user Bob queries the vOrdersTSQL view, it works just fine. SQL Server does not require Bob to have SELECT
permission on the tables referenced by the view as long as he has SELECT permission on the view and both the view
and the table have the same owner. This is an example of the ownership chaining .
However, Bob would not be able to query vOrdersCLR view, as ownership chaining would not work in the CLR
routines and he needs to have SELECT permission on OrderLineItems table for the GetOrderTotalCLR method to work.
When CLR code accesses external resources, it is done in the context of a SQL Server startup account, and it
could require that additional privileges be granted. You can work around such requirements by using impersonation
in the .Net code, although it would work only when Windows Authentication is used.
Finally, EXTERNAL_ACCESS or UNSAFE assemblies must be signed with the same key as the SQL Server login, which
has EXTERNAL ACCESS or UNSAFE permission granted. Let's look how you can do that.
For a first step, as shown in Figure 13-2 , you need to generate a key pair file. Visual Studio and Windows SDK
have the utility sn.exe that you can use.
Figure 13-2. Generating key pair file with sn.exe
You should specify the generated file in CLR project properties, as shown in Figure 13-3 and Figure 13-4 .