problem with making owner for a database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AlexC - 27 May 2007 22:52 GMT

Hi,

I use Sql server express 2005.
I have two databases: db1 and db2.
I use Studio Management. I connect with my administrator account 'admin'.

1) When i rightclick on db1 (and also on db2) and i take the properties /
general, i see for both db: owner = servername\admin .
When i take properties / Files, i see for db1: owner = servername\admin ,
but for db2, it remains empty.
why and does it mean that db2 has no owner? But then, why do i see in
properties / general : owner = servername\admin?

2) i want to make 'admin' owner of db2 (for creating a database diagram), so
in properties / Files, i click on the button with three dots.
A windows "select database owner" appears, i click on button 'browse', but
then all i see is:
built-in administartors
built-in users
sa
...

Whatever i choose (e.g; built-in administrators), i get the message: an
entity of database cannot be owned by a role, group ... error 15353).

Why do i not get the account 'admin' in the browse windows and how to make
'admin' owner of db2?

Thanks for help
Bart

Reply to this Message

Erland Sommarskog - 28 May 2007 23:35 GMT

> 1) When i rightclick on db1 (and also on db2) and i take the properties /
> general, i see for both db: owner = servername\admin .
> When i take properties / Files, i see for db1: owner = servername\admin ,
> but for db2, it remains empty.
> why and does it mean that db2 has no owner? But then, why do i see in
> properties / general : owner = servername\admin?

Was db2 restored from a different server?

> 2) i want to make 'admin' owner of db2 (for creating a database
> diagram), so in properties / Files, i click on the button with three
> dots.

Use sp_changedbowner or ALTER AUTHORIZATION to change darabase owner.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Bart - 28 May 2007 23:55 GMT

Hi, thanks for repying..

Yes, db2 was a MDF file which was converted to a full database (with account
'admin').
Why does it no have owner? It should inherit the ownership of 'admin', no?

ALTER AUTHORIZATION works, but why is it not possible to do that with the
properties / Files? What's the meaning of OWNER: un that windows then?

>> 1) When i rightclick on db1 (and also on db2) and i take the properties /
>> general, i see for both db: owner = servername\admin .
[quoted text clipped - 10 lines]
>
> Use sp_changedbowner or ALTER AUTHORIZATION to change darabase owner.

Reply to this Message

Erland Sommarskog - 29 May 2007 08:51 GMT

> Yes, db2 was a MDF file which was converted to a full database (with
> account 'admin').
> Why does it no have owner? It should inherit the ownership of 'admin', no?

It's a mapping problem. The owner on server-level is a login (in the SQL
Server sense). In the database it's a user. Users and logins are mapped
through a SID. When you move a database from one server to another, this
mapping often gets out of sync, because SID are different. This results in
the situation where users in the database does not map to logins on the
new server.

> ALTER AUTHORIZATION works, but why is it not possible to do that with the
> properties / Files? What's the meaning of OWNER: un that windows then?

I haven't explorer why it does not work through the GUI, as I am fully
content with use T-SQL for doing operations.

Signature

Reply to this Message

Bart - 29 May 2007 19:40 GMT

Thanks

>> Yes, db2 was a MDF file which was converted to a full database (with
>> account 'admin').
[quoted text clipped - 14 lines]
> I haven't explorer why it does not work through the GUI, as I am fully
> content with use T-SQL for doing operations.

Reply to this Message