A web application needs to execute sys.dm_fts_parser
to discover which words SQL Server Fulltext Search used to match documents for the purpose of syntax highlighting. That stored procedure can only be executed if the caller is in the sysadmin
server role.
Permissions
Requires membership in the sysadmin fixed server role and access rights to the specified stoplist.
http://msdn.microsoft.com/en-us/library/cc280463%28v=sql.105%29.aspx
Since it would be most unwise to grant that server role to the web server's user, I attempted to create a stored procedure that runs as a separate user that is in that role.
create procedure usp_fts_parser
@query nvarchar(max)
with execute as owner
as
select display_term from sys.dm_fts_parser(@query, 1033, 0, 0);
go
I then created a user login_sign_fts_parser
, made it the owner of the stored procedure
alter authorization on usp_fts_parser to login_sign_fts_parser
and attempt to allow the web server's DB user rights to impersonate that user:
GRANT IMPERSONATE ON USER::[IIS APPPOOL\Strategic Window] TO login_sign_fts_parser
When I attempt to execute usp_fts_parser
, I receive the error:
Cannot execute as the database principal because the principal "login_sign_fts_parser" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Why do I get this error? How can I accomplish my goal of executing sys.dm_fts_parser
without granting undue permissions to the web server's DB user?