Post reply

Tables created under different username

  • June 10, 2004 at 9:51 am

    #82984

    Hi there, I have senior developer who is a db_owner in one of the databases.  Up to yesterday, whenever he would run a create table, it would be created with dbo as the owner of the table.  For some reason, now the tables are created with his username as the owner.  There have not been any changes to the database.  Does anyone know why this happened and how to change it back?  What actions do I take to find the problem?  Thanks in advance for you help.

    BTW, we are running Win 2000 with latest sp and SQL 2000 with latest SP.  Thanks.

  • June 10, 2004 at 10:26 am

    #509915

    Have you tried using sp_changedbowner to change it back to the senior developer? 

    Incidentally you can use sp_changeobjectowner to change any tables, view etc, back to dbo.

  • June 10, 2004 at 10:41 am

    #509921

    http://www.microsoft.com/sql/techinfo/tips/administration/changingdbo.asp

    Quote:

    You can change the dbo to the sa from the Windows NT login. The fastest way to do this is to detach the database, then reattach it while logged in as the sa. Note that this method will create downtime.

  • June 10, 2004 at 10:42 am

    #509923

    Hi Gavin, thank you for your reply. 

    I am aware of sp_changeobjectowner.  However, there is a bunch of code that is written which do not specifically tell sql server to create tables as dbo.  Now, when this person runs those codes every night, the tables are created under his username and the subsequent update/insert/delete statements do not find the table.  So, it is imperative to find out why the sudden change and fix it.  There is just too much code to go through to try to change. 

    I had told them from 2 years ago to code with the owner name in everything...and to create all objects with dbo as the owner.  Don't ask me why I wasn't able to enforce this rule, it may get me in trouble.

  • June 10, 2004 at 10:42 am

    #509922

    Was the senior developer the database owner before

  • June 10, 2004 at 10:45 am

    #509924

    sa24, yes he was db_owner before.  Nothing has changed.  I just dropped his account and re-created it again and still have the same problem.

  • June 10, 2004 at 10:51 am

    #509926

    raven, if I do that, will all users with db_owner permission, have sa permissions?  Are they going to have all of the system admin permissions?

    I just remembered one thing that has changed.  I removed the builtin\administrators from the users and added nt authority\system user.  I am not sure how that affects dbo, or privileges, but wanted to share that with you.  How can I add builtin\administrators back to the logins?  I want to see if adding it just the way it was, would make a difference.  Thank you.

  • June 10, 2004 at 11:29 am

    #509937

    Aha (maybe)!! Open Enterprise Manager, expand down to the database in question. Right click on that and select Properties. On the General tab, in the Database section, who is the Owner?

    Let's say your coworker (person A) is assigned as DBO, another coworker (person B) is assigned as DBO (yes you can have multiple DBO's on a database - DBO is a privilege).

    Now, A created the database, so they are the OWNER. B has dbo privilege, so they can 'act' as the owner (they aren't the owner, just act as one). Now you delete A's login. B becomes the Owner. You add A back in with DBO privilege. B is STILL the OWNER.

    I think that's what happened to you.

    -SQLBill

  • June 10, 2004 at 11:45 am

    #509942

    Hi Bill,

    The owner of the database is someone else, which is the same person who was the owner when things were working. 

    I recently also installed MDAC 2.8.  Could this be the change that caused this?  Ahhhhh, this is really frustrating. 

    Thank you everyone for your help, really appreciate it.

  • June 11, 2004 at 10:58 am

    #510114

    Are the updates done in a job or manually executed?  If they were run in a job, you could make sa the owner of the job. Then objects created will have dbo ownership.

    Removing builtin/administrators could have caused it if the developer was a member of that role.  MDAC upgrade could also have caused it, but I doubt it.

    You can re-add builtin/administrators by scripting it from another server, then executing the script on this server.    But you could test that theory by simply giving the developer sa rights while he executes the update tonight.

    Steve

  • June 11, 2004 at 11:05 am

    #510117

    Hi everyone,

    Unfortunately we still have not found an answer to this problem. 

    Can someone please explain to me the following?

    If I create a database, add a user, give him/her db_owner role...would the tables that he creates be owned by him/her or by dbo (assuming that he does not specify the table owner in create table statement)?

    What happened in our situation is that someone created a database, later on, I gave this developer db_owner permissions.  So the username that the database was created is different than the username of this developer.  Whenever this developer created tables (without specifying who the table owner is), then the tables were created with dbo as the owner.  How is that possible?

    Thanks in advance and sorry for my bad english.

  • June 11, 2004 at 11:11 am

    #510121

    There was a change at some point in how SQL Server determines the owner of an object.   I'm not sure when that change took place, but I think it was with a 2000 service pack.  We experienced the same thing.  However, if the object is created under the SA loginid, the owner will be dbo.  So, it seems that the solution to your problem would be to place the code for the create statements into a job owned by SA, and then either schedule the job, execute it yourself, or give the developer rights to execute it.

    Steve

     

  • June 11, 2004 at 11:20 am

    #510123

    Not sure if this is any help but if the objects are being created from a job then it could be that your developer has changed the "Run as user:" option on the advanced tab of the step being executed.

    If the code is being executed by a hard-coded connection to the database, and the developer is now using his username and password embedded into the code and he has dbo permissions, it could also be complicating things.

     

    Max

  • June 11, 2004 at 11:21 am

    #510124

    sorry, hard coded in an application using a connection string

    Max

  • June 11, 2004 at 12:48 pm

    #510138

    Thank you for your help everyone.

    Steve, the user used to run create table from QA and the owner of the new table would automatically be dbo.  He has some jobs that he runs every night, the tables used to be created with dbo as owner before.  Now, both the jobs and QA assign his username as the owner.  Can I just say that I love Microsoft?  The developer was not a part of builtin/admin.  I have given the user sa privilege temporarily and everything is working fine.

    Max, the run as option has not changed in the job

    Again, thank you for your help.

     

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply