none
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

     

     

    Tuesday, September 28, 2010 6:01 PM

Answers

  • 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, esquel@sommarskog.se
    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
    • Marked as answer by scott_m Thursday, September 30, 2010 1:51 AM
    Tuesday, September 28, 2010 8:34 PM

All replies

  • 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

    Tuesday, September 28, 2010 6:04 PM
  • 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.

     

     

    Tuesday, September 28, 2010 6:24 PM
  • 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

    Tuesday, September 28, 2010 8:19 PM
  • 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, esquel@sommarskog.se
    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
    • Marked as answer by scott_m Thursday, September 30, 2010 1:51 AM
    Tuesday, September 28, 2010 8:34 PM
  • 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
    Tuesday, September 28, 2010 8:35 PM
  • 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, esquel@sommarskog.se
    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

     

    • Marked as answer by scott_m Thursday, September 30, 2010 1:26 AM
    • Unmarked as answer by scott_m Thursday, September 30, 2010 1:51 AM
    Thursday, September 30, 2010 1:26 AM