+1-888-365-2779
Try Now
More in this section

Forums / Deployment / SQL issue on SF3.0 Community

SQL issue on SF3.0 Community

4 posts, 0 answered
  1. dmajkic
    dmajkic avatar
    9 posts
    Registered:
    30 Mar 2007
    21 Sep 2007
    Link to this post
    I'm receiving  SQL issue while  deploying SF 3.0 SP2  community on my site.

    This is the error message:
    [SqlException (0x80131904): Grantor does not have GRANT permission.]
    ...
    Nolics.ORMapper.DataProviders.SQLDataProvider.ExecSchemaChange(String change) +91
    Nolics.ORMapper.Base.OdbDataProvider.Upgrade() +97
    ...
    After a lot of  "hunt in dark" here is what I suspect:

    My ISP gave me server/user/pass to access SQL server and my database.

    Username provided IS NOT dbowner, nor it can GRANT access.
    That is their standard security practice on shared MSSQL server.

    I created sf site localy, and generated SQL script  via db publisher wizard with all sql object and data in it.  Then I executed that script on remote server.

    Complete script executed just fine - except last two commands.

    GRANT EXECUTE ON [NN_GetVersion] TO [public]
    GO
    GRANT EXECUTE ON [NN_NextValue] TO [public]
    GO

    These failed, because my username can't grant access. 

    But these commands are useless, since no other users will connect.
    So I fired my site's address in browser. And got same error from NoLic.

    I suspect that NoLic engine is doing the same - it is trying to keep db schema  in place by granting EXECUTE permission to public - and it is failing, making whole sitefinity unusable on my ISP.

    Is it something else?
    Can db model be changed in a way to avid useless granting to public?
    Is there a way to fire up site on this config?



  2. Vlad
    Vlad avatar
    498 posts
    Registered:
    15 Jul 2016
    24 Sep 2007
    Link to this post
    Hello Dušan,

    Actually these scripts:

    GRANT EXECUTE ON [NN_GetVersion] TO [public]
    GO
    GRANT EXECUTE ON [NN_NextValue] TO [public]
    GO

    are executed only when upgrading database and creating the stored procedures. The upgrading operation requires full code and database permissions. Since you are upgrading your database manually from a generated script, you could skip the above commands and successfully run the script, as you said.

    Thank you for reporting this issue. We are not sure why Nolics needs to run the scrips, but  we will investigate it further.

    Please let us know if we are missing something.


    Sincerely yours,
    Vlad
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  3. dmajkic
    dmajkic avatar
    9 posts
    Registered:
    30 Mar 2007
    26 Sep 2007
    Link to this post

    Since you are upgrading your database manually from a generated script, you could skip the above commands and successfully run the script, as you said.


    If these "GRANT to [public]" commands are not executed,  NoLics will conclude  that database is "not upgraded", than try to upgrade and fail with an exception.

    My provider considers that granting to public is security risk on shared SQL server.

    There is no way to upload "valid" sf database if  I can't GRANT to [public].
    Eg. - sitefinity won't work. 

    Am i right here? Can I avoid nolics permision check?


  4. Vlad
    Vlad avatar
    498 posts
    Registered:
    15 Jul 2016
    27 Sep 2007
    Link to this post
    Hello Dušan,

    We just tested your case:
        - Created a new database;
        - Created a new project, connected it to the database and started it to upgrade the database;
        - Exported the database with Database Publishing Wizard in an .SQL file;
        - Created a second database and run the script from the .SQL file into it. Before running the script , we removed the following lines:

    GRANT EXECUTE ON [dbo].[NN_GetVersion] TO [public]
    GO
    GRANT EXECUTE ON [dbo].[NN_NextValue] TO [public]
    GO

        - Changed the connections string in the project web.config to point to the second database (the one the user has no GRANT permissions for);
        - Started SQL Server Profiler tool to monitor all scripts executed in the SQL Server;
        - Run the project with the second database, it worked fine. Looking into the SQL Server Profiler trace we didn't notice a script like this GRANT EXECUTE ON [dbo].[NN_GetVersion] TO [public].
     
    Actually it's not Nolics, but we who decide when the database should be upgraded.

    For example, when initializing the Telerik.Lists module provider, it checks for SchemaVersion value in sf_Lists_Variables table in the database, and if it is not found or the value is different from the Telerik.Lists assembly build number, the provider is trying to upgrade the database, otherwise it decides that the database is already upgraded. The same goes for each module.
    Please check sf_Lists_Variables, sf_CmsVariables, sf_GC_Variables, sf_Polls_Variables, sf_SecVariables, sf_VersVariables, sf_WrkVariables, sf_DataAccessVariables tables in the database on your production server and make sure that they have SchemaVersion variable with the correct Sitefinity build version number.

    Also if you have custom modules, please make sure that they have a similar implementation.

    Hope the above is the cause of the problem. Please let us know how it goes.

    However, we will ask Nolics whether there is any way to skip the following when upgrading the database.

    GRANT EXECUTE ON [dbo].[NN_GetVersion] TO [public]
    GO
    GRANT EXECUTE ON [dbo].[NN_NextValue] TO [public]
    GO

    Regards,
    Vlad
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
Register for webinar
4 posts, 0 answered