Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeOur CommunityOur CommunityGeneral Discuss...General Discuss...Need help from someone who thoroughly understands how DNN uses tables.Need help from someone who thoroughly understands how DNN uses tables.
Previous
 
Next
New Post
9/22/2006 12:52 PM
 
I posted HERE about a problem I experienced after an upgrade to 3.3.5 but got no replies in a couple of days so I am trying a little different track.

The error I get when I try to install a new module indicates that DNN cannot access a system object called "'empulse_dnnuser.sysobjects' .  "sysobjects" is a table in DNN databases, but in my installation, the owner is "dbo" not the user (I have "empulse_dnnuser" specified as the database owner in web.config.

Now when I look at the tables and stored procedures I see that all the objects created by DNN have assigned "empulse_dnnuser" as the database owner.  The stored procedures are mixed, some use that and others assign "dbo".

This was not a problem until I upgraded to 3.3.5

I think that I need to get them consistant, but since I do not know how the core software references these I am not sure whether it makes more sense to change all ownership to the user or to "dbo".  It looks like either one may create problems with stored procedures.

Can anyone offer any guidence?

 
New Post
9/22/2006 2:05 PM
 

 

If your web.config has empulse_dnnuser as the objectQualifier, then all your objects need to be owned by that user.

Below is a script that will make a procedure that will allow you to change all the ojects to the correct user.
After running the below script to create all the chObjOwner procedure you'll need to do two steps:

   1.  Run the procedure in query analyzer like this: EXEC chObjOwner 'dbo', 'empulse_dnnuser'   
         This wiil produce a list of commands to change each object.

   2. Copy the output and paste into the query window then execute all of those.

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE proc chObjOwner( @usrName varchar(20), @newUsrName varchar(50))
as
-- @usrName is the current user
-- @newUsrName is the new user

set nocount on
declare @uid int                   -- UID of the user
declare @objName varchar(50)       -- Object name owned by user
declare @currObjName varchar(50)   -- Checks for existing object owned by new user
declare @outStr varchar(256)       -- SQL command with 'sp_changeobjectowner'
set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static
for
select name from sysobjects where uid = @uid

open chObjOwnerCur
if @@cursor_rows = 0
begin
  print 'Error: No objects owned by ' + @usrName
  close chObjOwnerCur
  deallocate chObjOwnerCur
  return 1
end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0
begin
  set @currObjName = @newUsrName + "." + @objName
  if (object_id(@currObjName) > 0)
    print 'WARNING *** ' + @currObjName + ' already exists ***'
  set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"
  print @outStr
  print 'go'
  fetch next from chObjOwnerCur into @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0

GO
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


DotNetNuke Modules from Snapsis.com
 
New Post
9/23/2006 12:24 AM
 
Thanks so much for the reply.

now, before I do this, I want to make sure that I understand what is happening.  I am concerned about two sets of tables, and two sets of procedures.  There are both tables and procedures that have "aspnet_" as the object qualifier, and those all have "dbo" as the owner.  The stored procedures (about 40 of them) also have "dbo" as the owner and all the ones that I looked at refer to "dbo" somewhere within he procedure (i.e.  in the SP aspnet_profile_setproperties there is "SELECT @UserId = UserId  FROM   dbo.aspnet_Users    WHERE  ApplicationId = @...")

There also are tables and procedures whose names all begin with "dt_" that are owned by "dbo" , The insides of those stored procedures ab\lso refer to objects owned by "dbo".  (i.e. in SP dt_droproppertiesbyid includes "    if (@property is null) or (@property = '')    delete from dbo.dtproperties where objectid=@id  else  delete from dbo.dtproperties   where objectid=@id and property=@property")  I do not know what either of these sets of tables/SPs are for or where they came from.  I am concerned that changing the ownership to empulse_dnnuser for those will cause more problems

Should I proceed with the procedures that you gave me?  Will that also change the references withinin the stored procedures from "dbo" to "empulse_dnnuser"? 

I did back up everything before I upgraded, so in a worse case scenerion, I could go back to square one.
 
New Post
9/24/2006 1:03 AM
 
John - hopefully you haven't given up on this --

After some experimentation, I find that when I create a new database through the hosting control panel (I am prevented from creating databases with Enterprise Manager) the owner of the automatically created tables (those beginning with "sys") is "dbo."  When I create table with EM the owner is the "database user".   That leaves me a bit confused about several things -

1) How are  the tables beginning with "asp_", the "dtproperties" table and the associated stored prcedures created?  I am not sure what dtproperties is, but i assume that all of these were created by the core DNN

2) is it normal in other installs for the "sys" tables to have "dbo" as the owner or are they normally owned by the databasse user in a DNN install?

3) why did this not matter before the upgrade from 322 to 335?


 
New Post
9/26/2006 5:33 AM
 
I can answer some but not all of your questions:

sysobjects, dtproperties and all other objects beginning "sys" or "dt" are SQL Server system objects. They are not created by DotNetNuke. All SQL Server databases have them, and they are used to maintain system data (for example sysobjects holds the list of all tables, views procedures etc in the current database, and it is common, as you have seen, for SQL developers to reference sysobjects in their code e.g. to find out if a particular table exists). I think they are always owned by dbo, but I am not 100% sure on that. Don't try to change anything about these objects!


The aspnet_ tables are created by DotNetNuke and are used to hold user account data.

As John said, you should get all your DotNetNuke objects owned by the same user, and it looks like life is much easier if they are all owned by dbo.
 
Previous
 
Next
HomeHomeOur CommunityOur CommunityGeneral Discuss...General Discuss...Need help from someone who thoroughly understands how DNN uses tables.Need help from someone who thoroughly understands how DNN uses tables.


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out