changing database owner to access diagrams

hello,

i recently changed the machine name of my development computer and am now no longer able to create or view any diagrams for the sql database that was created by the old machine name user. i receive an error where I cannot make myself "the dbo of this database." i can see the old name in the "owner" properties field of the mdf database, but the box is grayed out and i am unable to change it to the new machine/user name. is there a way to change the owner of the database to my new machine/user name? the new name has admin rights and the computer is a standalone workstation not connected to a network.

i am using sql server 2005 express edition with visual web developer.

thanks!

Hi,

you can achieve this by sp_changedbowner store procedure refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-0e0641774c04.htm in SQL BOL.

Hemantgiri S. Goswami

|||

thank you for the response. unfortunately, i am new to sql and do not know where or how to use this command. do i use VWD? Sql Server Management Studio Express does not allow me to navigate to the directory where the database resides. also, one additional piece of information: only the machine name changed.

old name: "old_machine_name/steve"

new name: "new_machine_name/steve"

which EXEC command should I use to change ownership of the database to the new name?

EXEC sp_changedbowner 'new_machine_name/steve'

or simply,

EXEC sp_changedbowner 'steve'

i am concerned that there is a larger problem with permissions and sql. anytime I add a new website and new database in VWD, the default "owner" field comes up with the old name, "old_machine_name/steve". how do I change this default so that all future databases and web sites have the new owner, "new_machine_name/steve"?

thanks!

|||

Hi,

When you rename your Server name it will reflect to your SQL Server too, your sql server's name also change refer http://blog.opsan.com/archive/2005/05/10/465.aspx for more. If you wants to change the DB Owner Open

SQL Server Management Studio -> connect -> Click on New Query -> run exec sp_changedbowner <schema> Refer http://weblogs.asp.net/dneimke/archive/2003/11/10/36691.aspx and http://weblogs.asp.net/eporter/archive/2004/10/29/249627.aspx for more on DB owner change.

Hemantgiri S. Goswami

|||

ok i tried using Sql Server Management Studio Express but was unable to navigate into the "C:\Documents and Settings\Steve\My Documents\My Web Sites\WebSite1\AppData" user directory. even though Steve is a user with admin rights, the Add database window would not open any directories below the Steve directory. therefore, i was unable to even see the mdf file to open.

i tried copying the complete web site to a c:\temp directory and was able to navigate to the AppData directory and open the database. when I ran the sql command in a query window:

EXEC sp_changedbowner 'Steve'

i received the error:

"Cannot find the principal 'Steve', because it does not exist or you do not have permission."

I received the same error when using this command: EXEC sp_changedbowner 'new_machine_name/Steve'

i did try renaming the computer name back to the old_machine_name, rebooted, and was successfully able to open the Diagrams directory of the database in the user Steve directory. Although I find this very strange, i do not know why it fails when I simply change the computer name to new_machine_name. do you have any insight to this? it still seems that there is a default setting using the old_machine_name/Steve stored somewhere in VWD or SQL Server 2005 Express.

thanks!

|||

Hi,

That is because of Schema refer Schema in BOL and below articles http://www.databasejournal.com/features/mssql/article.php/3481751 and http://www.sswug.org/see/SQL_Server_2005_-_Schema_Definition-19357

Hemantgiri S. Goswami

|||

i finally gave up and called Microsoft. the root cause was not found, but the fix was to create a new user with admin rights, then move all the files over to the new user account. the new machine name is now recognized and i am able to access the diagrams folder for the database. i hope this helps others with similar user account issues.

thanks for your help!