October 4, 2006 at 1:13 pm
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.
October 4, 2006 at 1:30 pm
October 4, 2006 at 1:34 pm
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
October 4, 2006 at 1:35 pm
Thnaks John, I did and able to change the DB owner but having problem with object owner change.
October 4, 2006 at 1:37 pm
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?
October 4, 2006 at 2:05 pm
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.
October 5, 2006 at 12:50 am
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.
October 5, 2006 at 3:09 am
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.....
October 5, 2006 at 8:01 am
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.
October 6, 2006 at 10:30 am
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.
October 6, 2006 at 12:05 pm
Thanks Shane! Would you please explain what you did?
February 13, 2008 at 3:25 pm
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.
August 15, 2008 at 4:11 am
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.
August 15, 2008 at 4:34 am
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" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply