sys.dm_fts_parser permission?

Question

Running the following search in SQL 2008 under my asp.net user context:

 

select * from sys.dm_fts_parser('FORMSOF(FREETEXT,' + 'DISCREPANCY' + ')', 1033, 0, 0)

 

Gives me this error:

Msg 297, Level 16, State 1, Line 1

The user does not have permission to perform this action.

 

Works fine if I run as SA but i really really don't want to have my web app run as SA to get this query to work....  Is there some small grained permission I can grant to my asp.net user to get this query to work?

 

thanks

 

 

Answer 1

I really dont know about this function till now... In MSDN i found this.....

Requires membership in the sysadmin fixed server role and access rights to the specified stoplist.

http://msdn.microsoft.com/en-us/library/cc280463(SQL.100).aspx

Answer 2

We use this function as part of SQL Server Full Searching to get all the various stems for a given word.  Example:

run

ran

running

 

I am shocked this query requires the sysadmin role.   It does not modify anything.  It does not delete anything.  It is only a readonly query.

Going to have a tough time pushing this security requirement through IT management because of its big time security ramifications.

 

 

Answer 3

I asked someone about your question and he gave me the following answer.

Use code signing.

In your db:

create procedure usp_fts_parser 
    @query nvarchar(max) 
with execute as caller 
as 
select * from sys.dm_fts_parser(@query, 1033, 0, 0); 
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 
 
alter database <yourdb> set trustworthy on; 

Now anyone that has granted EXECUTE permission on the procedure will benefit from the code signing and

will be able to run the procedure w/o error.

This is the safest way, 100% bulletproof, and you don't give any additional permission.

Hope this would help u.

Nick

Answer 4

My gut feeling says that there should be some different way to achieve what you want, but I don't know fulltext well enough to suggest an alternative.

However, I can offer an alternative to having your web app running as sysadmin. Put the SELECT statement in a stored procedure which you sign with a certificate that you store both in the application database and in the master database. Then create a "login" from that certificate and add that login to sysadmin. While it is called a login, there is no way to connect to SQL Server with that login.

You find a much more detailed description of the steps in this article on my web site: http://www.sommarskog.se/grantperm.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Answer 5

By 'ask someone' you mean you asked on StackOverflow then shamelesly posted the answer verbatim here, w/o proper attribution.

http://stackoverflow.com/questions/3816023/sys-dm-fts-parser-permission/3816407#3816407

---

http://rusanu.com

Answer 6

My gut feeling says that there should be some different way to achieve what you want, but I don't know fulltext well enough to suggest an alternative.

However, I can offer an alternative to having your web app running as sysadmin. Put the SELECT statement in a stored procedure which you sign with a certificate that you store both in the application database and in the master database. Then create a "login" from that certificate and add that login to sysadmin. While it is called a login, there is no way to connect to SQL Server with that login.

You find a much more detailed description of the steps in this article on my web site: http://www.sommarskog.se/grantperm.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for the helpful tip.  Although it was a P.I.T.A, signing the sproc worked.  I submitted this as suggested fix for the next sql service pack.  

 

Please upvote here if important to you:

https://connect.microsoft.com/SQLServer/feedback/details/606899/sys-dm-fts-parser-should-not-require-sysadmin-privileges