A database owner is not reconized as a database owner

A database owner is not reconized as a database owner

Post by ms new » Sat, 09 Feb 2002 03:36:03



Hi there,

I am very puzzled with the following behaviour.  Just wonder if you have
experienced the same and have a fix for it.

I understand that only a database owner (i.e. dbo) can restore a database.
So what I have done is to change the owner by using

use <databasename>
go
sp_changedbowner '<domain>/<username>'

And when I have confirmed that the NT login '<domain>/<username>' is now a
database owner by using

use <databasename>
go
sp_helpusers

However, when the user '<domain>/username>' try to restore the database,
SQL reports the following error:

"Microsoft SQL-DMO (ODBC SQLState:42000)
Only members of the sysadmn role or the database owner of '<databasename>'
can run RESTORE DATABASE.  Backup or restore operation terminating
abnormally."

But I am in fact the user is in fact a database owner.

However, if the database owner is set to a SQL standard login, I don't have
the above problem.  How can I get rid of the error when an NT login is a
database owner.

Thanks for your help in advance.

ChingTai

 
 
 

A database owner is not reconized as a database owner

Post by Aethyr Drago » Sat, 09 Feb 2002 16:46:46


Hi,

A database owner can make backups of a database (and do just about anything
else in the database), but does not have the permissions to restore a
database, only the SA has the permissions to perform a restore.

Run the following command in Query Analyzer to list all the permissions for
a database role.
sp_dbfixedrolepermission 'db_owner'

Cheers
Aethyr Dragon


Quote:> Hi there,

> I am very puzzled with the following behaviour.  Just wonder if you have
> experienced the same and have a fix for it.

> I understand that only a database owner (i.e. dbo) can restore a database.
> So what I have done is to change the owner by using

> use <databasename>
> go
> sp_changedbowner '<domain>/<username>'

> And when I have confirmed that the NT login '<domain>/<username>' is now a
> database owner by using

> use <databasename>
> go
> sp_helpusers

> However, when the user '<domain>/username>' try to restore the database,
> SQL reports the following error:

> "Microsoft SQL-DMO (ODBC SQLState:42000)
> Only members of the sysadmn role or the database owner of '<databasename>'
> can run RESTORE DATABASE.  Backup or restore operation terminating
> abnormally."

> But I am in fact the user is in fact a database owner.

> However, if the database owner is set to a SQL standard login, I don't
have
> the above problem.  How can I get rid of the error when an NT login is a
> database owner.

> Thanks for your help in advance.

> ChingTai


 
 
 

1. SP2 blues: owner object not seen by owner unless fully qualified

Oh man...I ran sql server SP2 and now I'm in trouble:

I have tons of code that's referencing Tables and SPs via ADO and VB...we're
talking 3 years of apps and applets...

Before the SP2, if a table owner was connected, my SQL queries did not have
to specifically reference Table objects owned by that owner by owner name.

ie:  "Select * from TableName" USED to work, EVEN if TableName was owned by,
say Bob, as long as Bob was connecting.

Since SP2.... when Bob is connected, ADO can't find the table, unless I
reference the owner: "Select * from Bob.TableName"

I've learned that its best to have DBO own objects, so I do that from now
on, but I've got 100s of tables and procedures and 100s of apps that
reference tables without the full qualifier.  Is there anything I can do,
short of uninstalling SP2, or changing all my apps, that will allow BOB to
see BOB's tables without referencing Bob.Tablename?  There's got to be some
SQL setting that allows this?

Desperate Eric.

2. About replication from SQL to Access Mdb file

3. SA is not owner of database need to change

4. Importing pictures into VFP: recommendations for a digital camera

5. database not found for non-owner using sesocpip

6. Cannot delete Tablespace without datafile

7. Change DB owner/Table owner

8. Pay nothing for your conference calls!

9. owner name listed for OWNER column

10. Owner of Database vs. Owner of the database objects

11. How to change database owner

12. How to change database owner from dbo