Forums

Not answered db_owner permissions required, need help!

Rob

Posted on May 25, 2009 (permalink)

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

Georgi Georgi

Posted on May 27, 2009 (permalink)

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.

Reply

Rob

Posted on May 27, 2009 (permalink)

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).

Reply

Vlad Vlad

Posted on Jun 3, 2009 (permalink)

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.

Reply

terrysmith Intermediate

Posted on Sep 14, 2009 (permalink)

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.

Reply

terrysmith Intermediate

Posted on Sep 14, 2009 (permalink)

Also, the version of my Telerik.Cms.dll and in the sf_CmsVariables database table is 3.7.1990.3.

Reply

Systeembeheer UNIT4

Posted on Sep 15, 2009 (permalink)

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.

Reply

terrysmith Intermediate

Posted on Sep 15, 2009 (permalink)

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

Reply

Systeembeheer UNIT4

Posted on Sep 15, 2009 (permalink)

Try logging into the sqlserver using the sql management console with the accounts you mentioned and create a stored procedure by hand.

Reply

terrysmith Intermediate

Posted on Sep 15, 2009 (permalink)

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

Reply

Georgi Georgi

Posted on Sep 17, 2009 (permalink)

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.

Richard

Posted on May 14, 2010 (permalink)

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.

Reply

Ivan Dimitrov Ivan Dimitrov

Posted on May 14, 2010 (permalink)

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.

Reply

Zubair Intermediate

Posted on Apr 4, 2011 (permalink)

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,

Reply

Ivan Dimitrov Ivan Dimitrov

Posted on Apr 4, 2011 (permalink)

Hello Zubair,

db_datareader and db_datawriter are required.

Greetings,
Ivan Dimitrov
the Telerik team

Reply

Zubair Intermediate

Posted on Apr 4, 2011 (permalink)

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.

Reply

Basem Intermediate

Posted on Apr 6, 2011 (permalink)

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.

Reply

Forums

Not answered db_owner permissions required, need help!

Server Error in '/Multivers' Application.

CREATE PROCEDURE permission denied in database 'xxx'.

DevTools for .NET

Kendo UI

Test Studio

Sitefinity

TeamPulse

Call us

Anonymous

About Telerik