locked
Break association between my login and dbo user RRS feed

  • Question

  • Hi,

    I have a database application using SQL Server 2005 with Windows Authentication. I have setup MSSQL 2005 developer edition on a development server. When I installed MSSQL 2005, I was logged in with my windows network account 'co4114'. I set up the service to run as a Local Service and created an explicit login for co4114 with sysadmin privileges.

    I then proceeded to create a database as co4114 and loaded it with a backup of our production database. There is a user 'kyle' in the production database that is mapped to the 'co4114' network login.

    My problem is that whenever I connect to my development database using windows authentication and my co4114 network account, it always associates me with the dbo user in my database. I want to be associated with the 'kyle' user. I've checked the user mappings for the co4114 login on the development database server, and it correctly claims to be mapped to the 'kyle' user. However, whenever I connect as co4114, it still associates me with dbo. I thought that this might be because I created the database as co4114, so I changed to the database owner to be the sa account. Then, when I go check the login associated with the dbo user on my database, it says it's tied to 'sa' and not co4114. But the problem persists! This is very frustrating and I'd appreciate any insight on how to permanently break the association between my co4114 accound and the dbo user so that I can be properly associated with the 'kyle' user.

    Thanks for any help,
    Kyle
    Friday, October 9, 2009 9:10 PM

Answers

  • You need to remove your sysadmin role membership to avoid being "dbo" because sysadmin role members are "dbo" in all databases.  Other than sysadmin role members, only the database owner is mapped to the "dbo" user.  You have apparently addressed that issue with sp_changedbowner (although ALTER AUTHORIZATION ON DATABASE is the preferred method).  


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Alex Feng (SQL) Tuesday, October 13, 2009 7:33 AM
    • Marked as answer by kylejmcintyre Tuesday, October 13, 2009 4:32 PM
    Saturday, October 10, 2009 4:32 PM

All replies

  • Sounds like you just want the db owner changed to 'kyle'?
    If so, look up the sp_changedbowner proc.
    [Edit] or the default database associated with your login?


    ajmer dhariwal || eraofdata.com
    Friday, October 9, 2009 10:09 PM
  • Hi Ajmer,

    Thanks for the reply. I don't know if you understood my question correctly. I don't want the 'kyle' user to be the dbowner. I just want the 'kyle' user to be associated with the co4114 login, which is my windows user account. However, no matter what I do it seems that the co4114 login is tied to the dbo user for that database even though I have set up the appropriate user mappings.

    I thought that perhaps this problem was occurring because the database was owned by co4114, so I changed it (using sp_changedbowner) to be owned by sa, but it still always associates me with the dbo user when I log in as co4114. I also have my default database set to the database that is mapped to the 'kyle' user.

    Sorry for all of the silly kyle/co4114 details in this posting. I just thought it might be easier to deal in specifics.

    Kyle
    Friday, October 9, 2009 10:52 PM
  • You need to remove your sysadmin role membership to avoid being "dbo" because sysadmin role members are "dbo" in all databases.  Other than sysadmin role members, only the database owner is mapped to the "dbo" user.  You have apparently addressed that issue with sp_changedbowner (although ALTER AUTHORIZATION ON DATABASE is the preferred method).  


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Alex Feng (SQL) Tuesday, October 13, 2009 7:33 AM
    • Marked as answer by kylejmcintyre Tuesday, October 13, 2009 4:32 PM
    Saturday, October 10, 2009 4:32 PM
  • Dan, I may name my first-born child after you. Thanks!
    Tuesday, October 13, 2009 4:33 PM