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.
Requires membership in the sysadmin fixed server role and access rights to the specified stoplist.
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?