transfer of dbo rights to another user

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

transfer of dbo rights to another user

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-11-25 : 03:56:53

After a domain change I'm trying to assign the exact set of permissions and roles to the corresponding users in the new domain. I guess there exist a smoother ways to do that; I do it one by one, mapping the users to databases, following the same pattern of the corresponding user in the old domain.

How do I reassign in user mapping the user dbo to a database? I can assign as many users as I want to the role owner, but I can't assign dbo to a user, if this is already given to another one. Is that right? And I can't remove this assignment of the former user eather as this would throw an error. If I go the users of that database, the user name dbo is assigned to a login name. How and where do I reset this assignment?

I really find it hard to understand this multi layer labirinth of roles, schemes, users, permission.

The databases in question are content databases and service databases of service accounts of a sharepoint site.

Anyone to light up the darkness?
Regards, Martin

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-25 : 04:59:42

I would say that you do not have the level of rights to grant or revoke dbo from your users. Are you logged in as a sysadmin? If not, ask your sysadmin user to grant the rights.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 05:01:33

haver a look at sp_changedbowner
or better alter authorization.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-11-25 : 05:36:31

I entered as the SA, so I guess there aren't any restrictions...

Yes, I stepped on Alter Authorization too, but what object do I alter then to just change the ownership? I read that sp_changedbowner wont be supported in future editions, and that I better do it with the alter authorization command.

I tried:
ALTER AUTHORIZATION ON Object::DATABASE TO NewUser;
GO

but it didn't work... can't find the object. How am I supposed to declare the object database? Instance.database

Martin

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 05:52:58

try
ALTER AUTHORIZATION ON DATABASE::mydb TO NewUser;

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-11-25 : 06:06:23

Yes, for some reason I hadn't seen DATABASE among the objects...

Thank you! This worked on a test database, but now that I apply it on the original DB I get an error related to the name of that Database, because it contains a hyphen.... ???

The database was created by sharepoint (SharePoint_AdminContent_4ef27ae5-5b43-480a-a011-c1df72332377) and although this is generally fine to SQL, with the alter command it throws: Incorrect syntax near '-'... underlining the hyphen in red.

how can this be?

I wont change the name of that database, because if Sharepoint doesn't find the correct db it will be a hell of a mess, considering that this migration to a new domain is something poorly documented and I'm already scared to death.

Sachin.Nand

2937 Posts

Posted - 2011-11-25 : 06:14:22

Try this



ALTER AUTHORIZATION ON DATABASE::[SharePoint_AdminContent_4ef27ae5-5b43-480a-a011-c1df72332377] TO NewUser;

PBUH

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 06:14:24

Try it in square brackets.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-11-25 : 06:20:32

Thanks folks, I'm getting closer...

now I have:

ALTER AUTHORIZATION ON DATABASE::[SharePoint_AdminContent_4ef27ae5-5b43-480a-a011-c1df72332377] TO [CORP\MOSS_Admin];
GO

and it says:

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

does he mix up the users because they have the same name although they are in different domains? CORP\MOSS_Admin is member of the owner role, but NOT owner yet!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources