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?
Best Answer
The problem is with the difference between logins and users. When you are granting the permissions you are working with a user. Only a login can have server level permissions such as sysadmin. I discussed this here if you are interested.
In the mean time I can tell you how to do what you need, however it's not the best idea in the world.
First create a seperate database, make SA the owner of that database, set the trustworthy property on for that database. Then create your usp_fts_parser stored procedure with EXECUTE AS OWNER. In this case OWNER is dbo. Also in this case dbo is SA. Because you made the database trustworthy it will allow users in it to access server level permissions. All of this means that your stored procedure will be able to act as a sysadmin. Next grant the login that you are using for the web application connect permissions to your new database. Then grant that user execute permissions on the stored procedure.
This is very important. ONLY grant the user execute permissions on the stored procedure. You have created a big security hole by turning on trustworthy with a database owned by a sysadmin so you need to be very very very careful who you give permissions to that database and what you allow them to do.