1

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?

1 Answer 1

3

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.

5
  • There's another approach discussed on MSDN. Unfortunately I could not get that to work. Would you mind having a look and sharing whether you think it should work in principal? social.msdn.microsoft.com/Forums/en-US/transactsql/thread/…
    – Eric J.
    Apr 1, 2013 at 17:50
  • I've never tried it myself but I've heard of it before and it should work, better than my solution for that mater. Apr 1, 2013 at 18:09
  • I tried it (by literally executing every step) but it did not work for me. Can you see any details left out of any step that DBA would just fill in but a non-DBA might miss? On a separate note, your blog on logins vs. users was very useful to me.
    – Eric J.
    Apr 1, 2013 at 18:14
  • Glad to hear the blog helped. Unfortunately I'm not seeing anything particularly missing. I would still do all of the work in a database specific to it. Trustworthy is a scary property :). You might try asking the original poster of the information. The link is in the post. Apr 1, 2013 at 18:27
  • The answer really came from a Stack Overflow user. The whole topic was deleted on SO because both the question and answer were copied to MSDN (apparently some user not the original answerer was gaming both sites) and SO didn't want to have any copyright issues. I'll see if I can find a way to reach the original SO answerer. Thank you for your help and I appreciate the advice to use a separate DB to minimize the security exposure.
    – Eric J.
    Apr 1, 2013 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.