iFinity

Written by:
Monday, March 01, 2010 12:47 PM 

With DotNetNuke 5.2 a new change was introduced, where a specific GRANT EXECUTE statement was run for all the objects on the database.  This caused plenty of problems with shared hosts, where a specific database user is used to run all of the Sql for a specified database, and those users don’t have permission to use ‘grant execute’.

The change was to tighten up security, but unfortunately it tightened things up to the point where many users could not run them.  I know I personally burnt many hours trying to figure out where these errors had suddenly come from.  I was genuinely bewildered when first hit with this problem after providing someone some script to run to correct an error in their database.

Typically, you’d get something like this, either as a result of using the ‘Host->Sql’ screen, or by installing a module.

System.Data.SqlClient.SqlException: Cannot find the object 'dnn_DeleteLanguage', because it does not exist or you do not have permission. Cannot find the object 'dnn_GetUserByUsername', because it does not exist or you do not have permission. Cannot find the object 'dnn_DeleteTabSettings', because it does not exist or you do not have permission. Cannot find the object 'dnn_Wiki_TopicAdd', because it does not exist or you do not have permission. Cannot find the object 'dnn_Forum_UserGet', because it does not exist or you do not have permission. Cannot find the object 'dnn_GetContacts', because it does not exist or you do not have permission…

This would be repeated for virtually all the objects in the database.  Note that this occurs when the ‘run as scripts’ checkbox is ticked.

The problem in this case is not the lack of ‘Grant Execute’ permission (that would create a different error) but is a slightly different issue. 

What the DNN Sql Script execution process does is run a piece of Sql that checks the system tables, and comes up with a ‘grant execute’ statement for each stored procedure and function in the database.  If you’re using the ‘Host->Sql’ page, this gets run at the completion of your script, as kind of a blanket coverage of permissions for your database.  An important thing to note here is that even if you do get this error, your script probably finished OK.  The ‘grant execute’ permissions are not run at the same time as the script, so they are not part of the same ‘transaction’ as far as the database is concerned.  Therefore the completion of the script is not the same as the granting of the permissions.

The granting of permissions looks to your database like this:

grant EXECUTE on [dnn_DeleteLanguage] to [dnnAdmin]

grant EXECUTE on [aspnet_RegisterSchemaVersion] to [dnnAdmin]

grant EXECUTE on [dnn_GetUserByUsername] to [dnnAdmin]

grant EXECUTE on [dnn_DeleteTabSettings] to [dnnAdmin]

The problem is easily identified by inspecting this stream of Sql.  Many, many DotNetNuke installations use a ‘databaseOwner’ – this is specified when you first install the site, and is kept in the web.config entries for the SqlDataProvider object (the SqlData Provider object is responsible for running all database queries in a DNN install).  Here’s the relevant entry:

<add name="SqlDataProvider" type="DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider" connectionStringName="SiteSqlServer" upgradeConnectionString="" providerPath="~\Providers\DataProviders\SqlDataProvider\" objectQualifier="" databaseOwner="hosting_user" />

Now, if you have ‘dbo’ as the database owner, you’ve probably got no problems.  But if you have a value like databaseOwner=”hosting_user” then you probably have a problem.  This is because each of the above scripts does not specify the ‘owner’ or ‘schema’ of the database object. 

When DNN runs some Sql against the database to either retrieve information or update information, it prefixes the owner of the object based on the value in the ‘databaseOwner’ web.config entry.  This allows people who use a specific database user to run sql correctly.  When the ‘grant execute’ code runs, it does not specify the owner of the tables, and depending on other settings in the database, this means it can fail with errors like these:

System.Data.SqlClient.SqlException: Cannot find the object 'dnn_DeleteLanguage', because it does not exist or you do not have permission. Cannot find the object 'dnn_GetUserByUsername', because it does not exist or you do not have permission. Cannot find the object 'dnn_DeleteTabSettings', because it does not exist or you do not have permission. Cannot find the object 'dnn_Wiki_TopicAdd', because it does not exist or you do not have permission. Cannot find the object 'dnn_Forum_UserGet', because it does not exist or you do not have permission. Cannot find the object 'dnn_GetContacts', because it does not exist or you do not have permission…

Note that the list of objects failing depends on what’s installed in the database and the order in which it returns them.  It’s unlikely to have two databases returning the exact same error.

Just to confuse things further, whether it is an ‘owner’ or a ‘schema’ depends on the version of Sql Server being used.  In Sql Server 2005, a separation was made between owners of tables – this was decoupled from the user record and turned into a ‘schema’.  Thus one or more database users can share a schema, and it’s possible to delete a user without orphaning a set of tables and stored procedures.

Fixing the Problem

There’s two ways around an error like this:

1) change the DNN scripts so that the owner/schema of the database objects is prefixed to the grant execute statements.  This would mean they would be submitted like this:
grant EXECUTE on hosting_user.[dnn_DeleteLanguage] to [dnnAdmin]

This would require a change in the DNN core itself.  It’s also difficult to do a script that is both Sql 2000 & Sql 2005 compatible, due to the change between these two versions.

2) update the user so that the user has the same default schema as the ‘databaseOwner’ field in the web.config file.

This is achieved by a couple of methods, depending on what the exact problem is.

The problem may be an ‘orphaned’ user, where by the login for the original, schema-owning user is lost, and the user is now either orphaned or mapped to a different user.

Orphaned users are quite common where a database has been restored from a backup on a different server, or the server has gone through an upgrade where everything didn’t quite go swimmingly.  You can ascertain if the user is orphaned by running this Sql (directly via a Sql Query window) 

sp_change_users_login @action ='report'

This statement returns a list of users where there is no mapped login.  If your user is on this list, then you’ve got an orphaned user, and you’re probably only running your site because another user has been mapped through to allow access to those objects.  But that other user isn’t the correct owner, and as such will always need the fully qualified owner.object syntax to run Sql.

If you have a user on this list, then you can fix the user to re-map to a specific login, like this:

sp_change_users_login @action ='Update_ONe', @userNamePattern = 'hosting_user', @loginName = ‘hosting_user'

This example re-maps the user ‘hosting_user’ to the database login ‘hosting_user’.  You would then update your Sql Connection string to the ‘hosting_user’ credentials.  Note these values don’t have to be the same, but often are for the sake of convention.

Now, if you run Sql like this:

grant EXECUTE on [dnn_DeleteLanguage] to [dnnAdmin]

Sql Server will map the [dnn_DeleteLanguage'] object to hosting_user.dnn_DeleteLanguage, and should run OK.

If the problem is not an ‘orphaned user’, then the problem could be an incorrect default schema mapping.    You can check the schema by running this Sql:

select * from sys.database_principals
where name = ‘hosting_user’

Note that this is Sql 2005 specific code – it’s unlikely you’ll be having these issues in Sql 2000.

If the ‘default_schema_name’ doesn’t match what you’d expect, then you’ll have to update it.  This can be done via the Sql Management Studio (select properties for your database user) or you can run a bit of Sql to change it:

ALTER USER hosting_user WITH DEFAULT_SCHEMA = hosting_user

This will ensure that, when logged on with 'hosting_user' you don't have to specify hosting_user. as the database owner prefixed to the object name in Sql statements.

You can test all this out by using a Query Manager program, connecting to your Sql Server instance using the same credentials that are in your DNN web.config file, and try and do a simple select:

select * from Users

If you get an ‘invalid object name’ error, you’ve still got issues.  If you get a list of users back, you’ve solved your problem.  As a double, check, submit the same Sql to the ‘Host->Sql’ page and check the ‘run as script’ box.

Summary

This post doesn’t really cover a specific solution for a specific problem, but more of a round-up of issues relating to the use of specific owners of objects in DotNetNuke Sql Server databases. If you’re getting these types of problems, I hope this article has at least highlighted some areas to look at or try.  The DNN Code that highlights this issue can be argued to be correct, but it relies on the default schema of your user being correct.

If you have these problems on your site, it’s best to trace backwards and figure out what changed with your user configuration.  If you updated the web.config file with new values for a connection string, perhaps try reversing those.  If you’ve restored your database to a different instance, or a different server, then you may need to look further into re-creating logins, remapping schemas and updating the default schema as described.

It’s a tricky little area of Sql Server.  If you’ve found out solutions or more problems, please let me know via the comments field.

Trackback Print

Tags:

Categories:

Location: Blogs Crafty Code

Your name:
Your email:
	(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel