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

Forums / Security / db_owner permissions required, need help!

db_owner permissions required, need help!

17 posts, 0 answered
  1. Rob
    Rob avatar
    7 posts
    Registered:
    18 Jan 2008
    25 May 2009
    Link to this post
    Hi,

    We are working on the first Sitefinity 3.6 implementation for a customer.
    After publishing the site to our liveserver we got an error. We already traced it back to an error trying to create a stored procedure.
    On our live servers users do not have permission to create stored procedures and only have the roles dbreader and dbwriter.
    Can you please explain how we can prevent the error without giving the user more permissions than dbreader/dbwriter?

    The error:

     

    Server Error in '/Multivers' Application.

    CREATE PROCEDURE permission denied in database 'xxx'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: CREATE PROCEDURE permission denied in database 'xxx'.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:

    [SqlException (0x80131904): CREATE PROCEDURE permission denied in database 'xxx'.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +212
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2811
       System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +3041535
       System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +470
       System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +182
       Nolics.ORMapper.DataProviders.SQLDataProvider.ExecSchemaChange(String change) +105
       Nolics.ORMapper.Base.OdbDataProvider.Upgrade() +137
       Telerik.Cms.Data.DefaultProvider.Initialize(String name, NameValueCollection config) +1234
       Telerik.ManagerBase`1.InstantiateProvider(ProviderSettings providerSettings, Type providerType) +1281
       Telerik.ManagerBase`1.InstantiateProvider(ProviderSettings providerSettings) +241
       Telerik.ManagerBase`1.SetProvider(String providerName) +818
       Telerik.ManagerBase`1..ctor(String providerName) +322
       Telerik.ManagerBase`1..ctor() +54
       Telerik.Cms.CmsManager..ctor() +41
       Telerik.Cms.CmsWorkflowService..ctor() +105
       Telerik.Cms.CmsWorkflowService..cctor() +56
    
    [TypeInitializationException: The type initializer for 'Telerik.Cms.CmsWorkflowService' threw an exception.]
       Telerik.Cms.CmsWorkflowService.Initialize() +0
       Telerik.Cms.CmsManager.OnInitialized() +37
       Telerik.ManagerBase`1.Initialize() +1297
       Telerik.ManagerBase`1..ctor(String providerName) +71
       Telerik.ManagerBase`1..ctor() +54
       Telerik.Cms.CmsManager..ctor() +41
       Telerik.Cms.Web.UrlHelper.Initialize(CmsHttpModule cmsModule) +62
       Telerik.Cms.Web.CmsHttpModule.Init(HttpApplication context) +244
       System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +480
       System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +336
       System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +350
       System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +382
    
    [HttpException (0x80004005): The type initializer for 'Telerik.Cms.CmsWorkflowService' threw an exception.]
       System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +11298838
       System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +88
       System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +4338068
    
  2. Georgi
    Georgi avatar
    3583 posts
    Registered:
    28 Oct 2016
    27 May 2009
    Link to this post

    Hello Rob,

    Thank you for providing us with the log file.

    Looking at it, I see that the error is provoked by the fact that Sitefinity is trying to create a stored procedure in the SQL database. This action, of course, requires higher grants than select/execute.

    Sitefinity does not create the stored procedure each time it starts up. Having that said, the problem you experience might be occuring because:

    1. You have more than one application tied with the same database. If you have more that one Sitefinity instance with the same database, please make sure they are all upgraded to the same version. Otherwise, what might happen is that one application downgrades the database, while the other one upgrades it. The stored procedures are created/changed only during the database upgrade/creation.

    2. If you have upgraded the project files only, the first time when you run Sitefinity after the upgrade, the CMS will try to create the stored procedure. Assuming the user is limited when comes to permissions, this could be an expected behavior. Once the the upgrade (database upgrade as well) is over, you can set back the limited permissions for the database account.

    Let us know if this is the case.

    Georgi
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. Rob
    Rob avatar
    7 posts
    Registered:
    18 Jan 2008
    27 May 2009
    Link to this post
    Hi Georgi,

    Thanks for your response.
    Both are not the case. I installed Sitefinity 3.6, created a site (using dbo permissions). I opened the site (front and backend), created a page, added a newsview to it and added a newsitem in the news module, and it works fine.

    After this I set the permissions to dbreader/writer only. When opening the site, the first occasion it does something in the DB (for instance retrieving news) it crashes with the error I sent.

    We're using SQL Server 2005 if that makes any difference...

    The only way we were able to solve it for our live website we launched was to create a different database user which has dbo permissions. Creating a new user with "create procedure" and "grant execute" permissions does not seem to work. Sitefinity needs even more than that. We also created a SF 3.5 site which runs fine without this (www.deltastatement.org).
  4. Vlad
    Vlad avatar
    498 posts
    Registered:
    15 Jul 2016
    03 Jun 2009
    Link to this post
    Hello Rob,

    Seems you are using v3.6.1927 release and this issue is related to the problem described in this forum thread.
    In order to fix it, please upgrade to the latest Sitefinity 3.6 SP2. Upgrading from any 3.6 release does not require any additional efforts – it is just replacing the binary files in the bin folder.

    Please let us know if the problem still exists after the upgrade.


    Sincerely yours,
    Vlad
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  5. terrysmith
    terrysmith avatar
    11 posts
    Registered:
    15 Nov 2006
    15 Sep 2009
    Link to this post
    I've spent hours tonight trying to deploy a website and have this same problem. It is EXTREMELY frustrating.

    I'm using 3.7. I have a web site and database that works perfectly on a development machine. Once deployed to a live web server I get "The EXECUTE permission was denied on the object 'sf_CmsVariables_pkq'". I've given the db user owned schema permission to everything (all database roles except db_denydatareader and db_denydatawriter), permissions to all roles, and explict grant permissions on all permissions for sf_CmsVariables_pkq.

    Please help.



  6. terrysmith
    terrysmith avatar
    11 posts
    Registered:
    15 Nov 2006
    15 Sep 2009
    Link to this post
    Also, the version of my Telerik.Cms.dll and in the sf_CmsVariables database table is 3.7.1990.3.


  7. Systeembeheer UNIT4
    Systeembeheer UNIT4 avatar
    12 posts
    Registered:
    17 Nov 2008
    15 Sep 2009
    Link to this post
    The only way we were able to solve this is to make sure db_owner is used, than use all of the modules once (add an item, remove an item). When doing that, the SP's will be created. After you've completed that, reset the user to db_reader-writer.
  8. terrysmith
    terrysmith avatar
    11 posts
    Registered:
    15 Nov 2006
    15 Sep 2009
    Link to this post
    Thanks Systeembeheer,

    The user accounts I tried (and I tried both integrated security from the website and SQL Server authentication) all had db_owner permissions. I've never been able to get past the errors to run any of the modules.

    Terry
  9. Systeembeheer UNIT4
    Systeembeheer UNIT4 avatar
    12 posts
    Registered:
    17 Nov 2008
    15 Sep 2009
    Link to this post
    Try logging into the sqlserver using the sql management console with the accounts you mentioned and create a stored procedure by hand.
  10. terrysmith
    terrysmith avatar
    11 posts
    Registered:
    15 Nov 2006
    15 Sep 2009
    Link to this post
    I had to explicitly grant EXECUTE PROCEDURE permissions to the db user even though the user was db_owner already. That made it work.

    Unfortunately, I'm about to deploy another website to a server where I won't have the ability to do that.

    Thanks for you help Systeembeheer
  11. Georgi
    Georgi avatar
    3583 posts
    Registered:
    28 Oct 2016
    17 Sep 2009
    Link to this post
    Hello Terry,

    We require db_owner role only when you perform database upgrades or when the database is created for the first time. Once you deploy it, you could go with the following permissions:
    '!!! Grant select on all tables to readonly roles and execute privs to following procedures.' 
    grant execute on NN_NEXTVALUE to CMSCORP_READONLY; 
    grant execute on NN_GETVERSION to CMSCORP_READONLY; 
    grant execute on SF_POLLS_POLLDATA_UPD to CMSCORP_READONLY; 
    grant execute on SF_POLLS_QUESTIONDATA_UPD  to CMSCORP_READONLY; 
    grant execute on SF_POLLS_ANSWERDATA_UPD  to CMSCORP_READONLY; 
    grant execute on SF_POLLS_IPDATA_INS  to CMSCORP_READONLY; 
    grant insert on SF_STAGEDPAGECONTENT  to CMSCORP_READONLY; 
    grant update on SF_CMSTEMPLCONTENT to CMSCORP_READONLY; 

    These are the absolute minimum permissions that the db user should have. 

    Greetings,
    Georgi
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  12. Richard
    Richard avatar
    24 posts
    Registered:
    25 Apr 2008
    14 May 2010
    Link to this post
    Is there an updated list of required permissions for 3.7 sp3? I tried the list given here and found that it was not sufficient. I got errors on:

    sf_PageContentBase_pkq
    sf_CmsContentBase_pkq
    sf_CmsPageTemplate_pkq
    sf_PageBase_pkq

    After adding execute permissions for these and a few more, one at a time, I finally ended up granting execute on all of the stored procedures just to get the site running without making the user db_owner.
  13. Ivan Dimitrov
    Ivan Dimitrov avatar
    16072 posts
    Registered:
    09 Dec 2016
    14 May 2010
    Link to this post
    Hello Richard,

    The account you use should have full access over the stored procedures. The user should be dbo when you install Sitefinity or you perform an update.
    db_datareader and db_datawriter are required. The db_datawriter role allows its members to perform modification of existing data and to insert new data. The members can execute the INSERT, UPDATE, and, DELETE statements against the database objects in a database.

    Greetings,
    Ivan Dimitrov
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  14. Zubair
    Zubair avatar
    142 posts
    Registered:
    26 Dec 2007
    04 Apr 2011
    Link to this post
    Hi,

    We are using v3.7 for .NET 4.0 and get the same problem, our client doesn't like db_owner permissions, could you please help and tell us what to do asap because we are nearing deployment.

    Thanks,
  15. Ivan Dimitrov
    Ivan Dimitrov avatar
    16072 posts
    Registered:
    09 Dec 2016
    04 Apr 2011
    Link to this post
    Hello Zubair,

    db_datareader and db_datawriter are required.

    Greetings,
    Ivan Dimitrov
    the Telerik team

  16. Zubair
    Zubair avatar
    142 posts
    Registered:
    26 Dec 2007
    04 Apr 2011
    Link to this post
    Yes I am giving those but I still get the same error as posted in this thread, I am not upgrading or downgrading the database (we're nearing deployment) but the problem still persists, as soon as I give db_owner, everything is normal.

  17. Basem
    Basem avatar
    131 posts
    Registered:
    22 Dec 2010
    06 Apr 2011
    Link to this post
    We went through the same issue. We even had to click through each module under owner permissions to get the database upgrade scripts to trigger in all places we can think of. Afterwards, we revoked the owner permissions.

    Run-time errors became more of a concern, so we ended up giving owner permissions back on the database and then encrypted the connection string in the web.config for added security: http://msdn.microsoft.com/en-us/library/ff647398.aspx . I hope this helps.
Register for webinar
17 posts, 0 answered