2

I had cause to change my computer name (it’s running in a virtual machine). I have an existing SQLExpress instance which was installed prior to the rename.

There are some features which fail because new databases are still created with the old owner. More specifically, when I try to create a new database diagram, I get the below error.

Could not obtain information about Windows NT group/user 'OLDNAME\user', error code 0x534. (Microsoft SQL Server, Error: 15404).

I used the following instructions to rename the server:

sp_dropserver 'OLDNAME\SQLEXPRESS';
GO
sp_addserver 'NEWNAME\SQLEXPRESS','local';
GO
--  Restart Server
SELECT @@SERVERNAME;    --  NEWNAME\SQLEXPRESS

However, this did not affect the default owner for new databases. They are still being created with the owner as OLDNAME\user, who no longer exists.

I know how to change the owner of an existing database, after creation. But, how can I get new databases to have the correct owner to begin with?

2
  • 1
    What features are failing, and what are the error messages? Commented Mar 10, 2022 at 20:49
  • There’s only one, an I admit it’s not too important: When I try to create a new database diagram, I get the message: Could not obtain information about Windows NT group/user 'OLDNAME\mark', error code 0x534. (Microsoft SQL Server, Error: 15404). I might add that I am after a better understanding of the whole database owner thing.
    – Manngo
    Commented Mar 10, 2022 at 21:06

1 Answer 1

5

The default owner is going to be whoever creates the database. In your case, it sounds like you're using a local computer account as a windows login for SQL Server. So, the login name is in the form of OLDNAME\user; however, you would like new databases to be created with NEWNAME\user as the owner.

Changes to Windows accounts are not propagated to SQL Server, so the old login name will persist until you manually change it. However, the login continues work because the SID is how SQL Server makes reference to it when authenticating the account.

To fix this issue you'll need to use the ALTER LOGIN command to change the login name from OLDNAME\user to NEWNAME\user.

ALTER LOGIN [OLDNAME\user] WITH NAME = [NEWNAME\user]

If you need to change the owner of an existing database, you can use ALTER AUTHORIZATION. I would recommend changing the database owner to someone other than a real user, such as [sa].

ALTER AUTHORIZATION ON DATABASE::<database_name>
    TO <domain>\<user>

Another option would be to use EXECUTE AS LOGIN = 'owner_to_be', just ahead of the statement to be executed to create the database.

7
  • That would suggest that my current login must be wrong. I am using Windows authentication. Would the fix involve changing my login?
    – Manngo
    Commented Mar 10, 2022 at 21:07
  • Changing the user after the event does fix the problem, but I wanted to see whether I could create new databases with the correct owner.
    – Manngo
    Commented Mar 10, 2022 at 21:12
  • That’s exactly what I was looking for. SELECT name,type_desc FROM sys.server_principals; gives me a list of logins, including the problematic login, and ALTER LOGIN fixes it. Thanks.
    – Manngo
    Commented Mar 10, 2022 at 21:32
  • Awesome! Glad that worked for you! Commented Mar 10, 2022 at 21:33
  • 1
    sp_changedbowner is to be discontinued. I would recommend using ALTER AUTHORIZATION ON DATABASE::<database_name> TO <domain>\<user> instead.
    – John K. N.
    Commented Mar 11, 2022 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.