App Pool User Unable to Execute Stored Procedure

Question

Background

I'm using SQL Server 2008 R2's full text search to retrieve a number of documents, and wish to use the results of sys.dm_fts_parser to highlight the matching terms.

Doing that ordinarily requires permissions an app user should not have. There is a solution proposed on MSDN as well as a deleted question/answer on StackOverflow (see why it was deleted).

Problem

I'm trying to implement the solution, but am unable to grant execute permission to the IIS App Pool user.

What I have tried

In SSMS under Security / Logins I created a user IIS APPPOOL\My App. I grant that user db_datareader and db_datawriter for table access, and that works fine.

Again in SSMS:

• Under MyDB / Programmability / Stored Procedures I right-click on dbo.usp_fts_parser
• Select Properties, Permissions.
• Click Search...
• Add IIS APPPOOL\My App
• On the Explicit tab, Execute row I check Grant
• Click OK

Question

Why is this grant insufficient to execute dbo.usp_fts_parser?

Notes

• I can execute it just fine from SSMS logged in as an administrator.
• If I temporarily grant the sysadmin Server Role to IIS APPPOOL\My App, the code is able to execute dbo.usp_fts_parser

Answer 1

Here is the answer originally posted at https://stackoverflow.com/questions/3816023/sys-dm-fts-parser-permission/3816407#3816407:

In your database:

create procedure usp_fts_parser
    @query nvarchar(max)
with execute as caller
as
select * from sys.dm_fts_parser(@query, 1033, 0, 0);
go

grant execute on usp_fts_parser to [<some low priviledged user>]
go

create certificate sign_fts_parser
    encryption by password = 'Password#1234'
    with subject = 'sign_fts_parser';
go

add signature to object::usp_fts_parser
by certificate sign_fts_parser with password = 'Password#1234';
go

alter certificate sign_fts_parser
remove private key;
go

backup certificate sign_fts_parser
to file = 'c:\temp\sign_fts_parser.cer';
go

In master:

create certificate sign_fts_parser
from file = 'c:\temp\sign_fts_parser.cer';
go  

create login login_sign_fts_parser
from certificate sign_fts_parser;
go

grant control server to login_sign_fts_parser;
go

I just tested these steps on a SQL Server 2008 and I was able to execute the usp_fts_parser from a low privileged login. The only difference from the original post answer is that I added explicit GRANT EXECUTE on the stored procedure and I removed the unrequited enabling trustworthy step. In your case the <low priviledged user> should be IIS APPPOOL\My App, assuming you do not use impersonation in your ASP application.

The required permissions on calling sys.dm_fts_parser are being derived from the signature on the procedure which, via the certificate exported and imported into master database, has explicit CONTROL SERVER permission (highest possible priviledge). Because the private key of the certificate was explicitly removed there is no way to abuse the high priviledge associated with this certificate, since it cannot ever sign again anything (the private key is lost forever). See Module Signing (Database Engine) for more details.

Note that the steps above have to be repeated every time you modify the stored procedure. Any ALTER on the procedure will result in an automated, silent, drop of the associated signature. Loosing the module signature implies loosing the priviledges derived from this signature and thus the subsequent calls to sys.dm_fts_parser will fail for a low priviledged user.