SQLEXPRESS 2014 (?)

I'm a little confused about my exact version as I see 2012 and 2014 in various locations, but I'm sure my question doesn't depend on the version.

Problem: I can't change the database owner

I noticed this when I went to create a database diagram and the error said the database does not have a valid owner.

So for that database I went to Properties > Files and sure enough, the owner is wrong.  Why?  I had changed the name of the PC recently, actually a couple of times.  When I finally got the name right, I ran the following commands in the query window:

    SELECT @@SERVERNAME to get the current server name

    and then

sp_dropserver <old name>

go

sp_addserver <new name>, local

go

     to change the server name     

The owner in the Properties > Files is for a pc name that doesn't exist.  But when I click on the '...' button to the right to select a new Database Owner I can't select the primary account on the PC.

If I create a new database, it puts that old name in as the database owner.

I went down to <server>\Security\Logins and the old <pcname>\<username> is still listed, and I can't add the <newpcname>\<username> because it says that principle already exists .  I'm afraid to make any other change without knowing for certain what to do.

So I'm stuck not understanding how to get a new database owner name on all the databases on this server.

Do I delete that old Login and then try to add in the new name?

Any help is appreciated.

• Edited by Alan Wheeler 11 hours 54 minutes ago

Did you tried this ..

https://msdn.microsoft.com/en-us/library/ms178630.aspx?f=255&MSPPError=-2147217396

https://technet.microsoft.com/en-us/library/ms190909%28v=sql.105%29.aspx?f=255&MSPPError=-21472173

Thanks for your response.

So I tried the solutions at both links and ran these queries:

  EXEC sp_changedbowner '<newMachineName\<username>'
  go

  alter authorization on database::testdb to "<newMachineName\<username>"
  go

Both commands completely successfully, but the database owner name didn't change.

I'm thinking my problem has to do with the name displayed in the server tree at SQLServerName > Security > Logins.  Is it so simple as 'Rename' the Login account that is incorrect, meaning <oldMachineName\username> should be right-clicked and renamed to <newMachineName\username>?

I did successfully add a different network name to servername > Security > Logins and apply that successfully as a new database owner, but I still have the issue of getting the SQL server to 'see or recognize' my local machine user account.

• Edited by Alan Wheeler 11 hours 14 minutes ago

So what does the owner name shows in DB properties ?

Also to find your sql version, run select @@version

Ahh yes you can rename that way and for windows logins if it doesn't exists in AD it will throw an error..

You can also use

ALTER LOGIN "Old_Server_or_Domain_Name\Windows_Login"
WITH NAME="New_Server_or_Domain_Name\Windows_Login"

These are the relevant server > security > logins used to answer your 1st question:

OldMachineName\UserName1

Domain\UserName2

In the databases with the wrong owner they display in 'database > properties > files':

OldMachineName\UserName1 (That name is not valid & I cannot do things like create a diagram)

In the databases I changed they display in 'database > properties > files':

Domain\UserName2  (That name IS valid & I CAN do things like create a diagram)

I ran the @@version and it is: Microsoft SQL Server 2014 - 12.0.4213.0 (X64)

======

Now a question about your last post.  You said 'you can rename that way and for windows logins if it doesn't exists in AD it WILL throw an error.'

Did you mean it WILL NOT throw an error?

• Edited by Alan Wheeler 10 hours 32 minutes ago

When I finally got the name right, I ran the following commands in the query window:

    SELECT @@SERVERNAME to get the current server name

    and then    sp_dropserver <old name>     go     sp_addserver <new name>, local     go

     to change the server name     

And you restarted SQL Server after this?

Since I don't know exactly what you have done, it's a little difficult to say exactly. But the simplest would be to change the database owner to sa, drop all users/logins with the incorrect machine name and add them back. Once this is done, you can change the owner to the correct login.

I recommend that you use SQL commands for the operations and get the information som sys.server_principals and sys.database_principals.

Thanks, Erland.  Yes regarding the restart.  Understood about using sa as a temporary owner for all the databases.  I assume I have to do that manually for each database, yes?  (It's not that many)  Will work on this over the weekend, day is ending here.

When I try to add the 'newMachine\username' at the present time, SQL tells me that name already exists, but it's not visible in the Login list.  Is it likely the case that SQL still thinks that 'oldMachine\username' is the primary machine account because it used to be, and once I delete that old Login, then adding 'newMachine\username' will work OK?

I was a little startled when I first tried to add 'newMachine\username' and it said it's already a register user, and it wasn't even in the list.

Yes you could just try dropping the old login and change DBO for required DB's . I think that should resolve....