Change DB and Object owner

Question

Post reply

Change DB and Object owner

rs

SSCrazy

Points: 2819
More actions
October 4, 2006 at 1:13 pm

#367521

Hi,
I created a database under SA account (objects also owned by SA)
Now I want to change the database and object owner to windows user, what are the procedures should I follow.
I have windows user created with no permissions in SQL Server.
Thanks in advance.
RS.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Look at sp_changedbowner in BOL.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Open Enterprise Manager. Expand to Security. Find the login, double click on it. Give it permissions to the database including db_owner.

Open Query Analyzer: Run:

USE dbname

exec sp_changedbowner 'new_owner_login'

For example:

USE MyDatabase

EXEC sp_changedbowner 'SQLBill'

-SQLBill

rs SSCrazy Points: 2819 More actions · Answer 3

Thnaks John, I did and able to change the DB owner but having problem with object owner change.

rs SSCrazy Points: 2819 More actions · Answer 4

Hi SQLBill,

Its SQL Server 2005. I did that but not able to change the object owner.

Should I add that user in sysadmin role before change the DB owner, would you please tell me the procedure to do this?

rs SSCrazy Points: 2819 More actions · Answer 5

This is what I exactly did.

Loged into management studio using SA

created database test

created a table Table_1 in test database. When I look for owner (both object and database ) which is SA.

I created windows user (SQLAdmin)

Created login for MachineName\SQLAdmin

EXEC

sp_changedbowner 'MachineName\SQLAdmin'

sp_changeobjectowner

'Table_1','MachineName\SQLAdmin'

I got the error

Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 107

Database principal or schema 'SQLAdmin' does not exist in this database.

Would you please help me on this.

Petrilli Baptiste SSC Enthusiast Points: 194 More actions · Answer 6

Hi,

First I have two questions : why are you using the sp_changedbowner instead of management studio ?

Why do you want this specific user became the owner of a table instead of the DB ?

Now for the error message, I do not think that a user can be the owner of only one object (a table here) in your db.

I am not really an expert in MSSQL 2K5 , but I think (as well as in SQL2K) dbo means database owner.

alkrup-104967 SSC Enthusiast Points: 147 More actions · Answer 7

first u should create user in ur test database on login SQLAdmin

second : give permission to created user or addrolemember to db_owner.

that's all.....

rs SSCrazy Points: 2819 More actions · Answer 8

Hi, Thanks for your response. I tried from management studio didn't find a way to change.

alkrup, I created login and created user in test db with db_owner option. When I run sp_changeDBOwner getting the following error

Msg 15110, Level 16, State 1, Line 1

The proposed new database owner is already a user or aliased in the database.

Again all thanks for your help.

Shane Miller SSC Eights! Points: 827 More actions · Answer 9

Delete that user from the database, then you should be able to change it with no problem. (When you change owner and go back under users for that database, you'll see your Windows user as dbo.) The same thing happened with me on some software that required a particular user as the owner of the objects.

rs SSCrazy Points: 2819 More actions · Answer 10

Thanks Shane! Would you please explain what you did?

Justin-14543 SSCrazy Points: 2017 More actions · Answer 11

Try this:

ALTER SCHEMA NewOwner TRANSFER ;

Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

roger.penny SSC Enthusiast Points: 110 More actions · Answer 12

Occasionally, particularly under SQL Management Studio Express when you create a user as a dbowner of a database, the schema is created but not linked.

In Studio manager, drill into the database and security, users, and the properties of your user. In the

Schema's Owned by this user - ensure the schema for your user is ticked on, this will add the schema to the database.

This should solve the Database principal or schema 'UserName' does not exist in this database. error.

Perry Whittle SSC Guru Points: 233858 More actions · Answer 13

use this handy script to change object owner to another user for objects ion the database

select 'exec sp_changeobjectowner '''+user_name(Uid)+'.'+name+''','' ''' /*owner to assign to, dbo for example*/

from sysobjects

where user_name(uid) <> 'dbo' /*from owner */

order by name

paste the output into a new query window and execute to change owners

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉