Change the owner of Model database

Hi
We have a model database that is owned by someone who has left the company.
i would like to change the owner but don't seem to be able to.
sp_changedbowner won't let me.
We have come up with two solutions that I would like to get some comments
on. Any other solutions would be welcome too.
Solution 1
Update the sid in master..sysdatabases to the id of the new owner for model.
Solution 2
Temporarily Change sp_changedbowner to allow an update to model.
i.e. change
Any comments or other solutions?
Cheers Brian
-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
if db_name() in ('master', 'model', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
to
-- CANT CHANGE OWNER OF MASTER/TEMPDB --
if db_name() in ('master', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
endBegs the question - how did it get in that state? The sid for those DBs
should always be 0x01, which correlates to the 'sa' login. Both
solutions you suggest are fairly questionable, but it's pretty much a
case of damned if you do, damned if you don't.
Personally, I'd be tempted to hack the sp_changedbowner proc (or rather
copy & paste the code into a new proc (and 'allow updates'
(sp_configure) so the system tables can be updated by our new proc)) to
just take out that restriction for the model database temporarily. That
way you can be sure that all the other things that sp_changedbowner does
(tweaking sysusers, etc.) happens too. But neither method would be
particularly kosher - just make sure you backup master & model before
doing any of this, as you might shoot yourself in the foot if you're not
careful.
Good luck.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
AussieBri wrote:

>Hi
>We have a model database that is owned by someone who has left the company.
>i would like to change the owner but don't seem to be able to.
>sp_changedbowner won't let me.
>We have come up with two solutions that I would like to get some comments
>on. Any other solutions would be welcome too.
>Solution 1
>Update the sid in master..sysdatabases to the id of the new owner for model
.
>Solution 2
>Temporarily Change sp_changedbowner to allow an update to model.
>i.e. change
>Any comments or other solutions?
>Cheers Brian
> -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
> if db_name() in ('master', 'model', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>to
>
> -- CANT CHANGE OWNER OF MASTER/TEMPDB --
> if db_name() in ('master', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>
>

Change the owner of Model database

Hi
We have a model database that is owned by someone who has left the company.
i would like to change the owner but don't seem to be able to.
sp_changedbowner won't let me.
We have come up with two solutions that I would like to get some comments
on. Any other solutions would be welcome too.
Solution 1
Update the sid in master..sysdatabases to the id of the new owner for model.
Solution 2
Temporarily Change sp_changedbowner to allow an update to model.
i.e. change
Any comments or other solutions?
Cheers Brian
-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
if db_name() in ('master', 'model', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
to
-- CANT CHANGE OWNER OF MASTER/TEMPDB --
if db_name() in ('master', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
endThis is a multi-part message in MIME format.
--030201070507090507090504
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Begs the question - how did it get in that state? The sid for those DBs
should always be 0x01, which correlates to the 'sa' login. Both
solutions you suggest are fairly questionable, but it's pretty much a
case of damned if you do, damned if you don't.
Personally, I'd be tempted to hack the sp_changedbowner proc (or rather
copy & paste the code into a new proc (and 'allow updates'
(sp_configure) so the system tables can be updated by our new proc)) to
just take out that restriction for the model database temporarily. That
way you can be sure that all the other things that sp_changedbowner does
(tweaking sysusers, etc.) happens too. But neither method would be
particularly kosher - just make sure you backup master & model before
doing any of this, as you might shoot yourself in the foot if you're not
careful.
Good luck.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
AussieBri wrote:
>Hi
>We have a model database that is owned by someone who has left the company.
>i would like to change the owner but don't seem to be able to.
>sp_changedbowner won't let me.
>We have come up with two solutions that I would like to get some comments
>on. Any other solutions would be welcome too.
>Solution 1
>Update the sid in master..sysdatabases to the id of the new owner for model.
>Solution 2
>Temporarily Change sp_changedbowner to allow an update to model.
>i.e. change
>Any comments or other solutions?
>Cheers Brian
> -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
> if db_name() in ('master', 'model', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>to
>
> -- CANT CHANGE OWNER OF MASTER/TEMPDB --
> if db_name() in ('master', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>
>
--030201070507090507090504
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Begs the question - how did it get in that state? The sid for
those DBs should always be 0x01, which correlates to the 'sa' login.Â
Both solutions you suggest are fairly questionable, but it's pretty
much a case of damned if you do, damned if you don't.<br>
<br>
Personally, I'd be tempted to hack the sp_changedbowner proc (or rather
copy & paste the code into a new proc (and 'allow updates'
(sp_configure) so the system tables can be updated by our new proc)) to
just take out that restriction for the model database temporarily.Â
That way you can be sure that all the other things that
sp_changedbowner does (tweaking sysusers, etc.) happens too. But
neither method would be particularly kosher - just make sure you backup
master & model before doing any of this, as you might shoot
yourself in the foot if you're not careful.<br>
<br>
Good luck.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
AussieBri wrote:
<blockquote cite="midC6A09B6D-DCCF-4E45-ADA4-17226F6BE6EF@.microsoft.com"
type="cite">
<pre wrap="">Hi
We have a model database that is owned by someone who has left the company.
i would like to change the owner but don't seem to be able to.
sp_changedbowner won't let me.
We have come up with two solutions that I would like to get some comments
on. Any other solutions would be welcome too.
Solution 1
Update the sid in master..sysdatabases to the id of the new owner for model.
Solution 2
Temporarily Change sp_changedbowner to allow an update to model.
i.e. change
Any comments or other solutions?
Cheers Brian
-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
if db_name() in ('master', 'model', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
to
-- CANT CHANGE OWNER OF MASTER/TEMPDB --
if db_name() in ('master', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
</pre>
</blockquote>
</body>
</html>
--030201070507090507090504--

Change the owner of Model database

Hi
We have a model database that is owned by someone who has left the company.
i would like to change the owner but don't seem to be able to.
sp_changedbowner won't let me.
We have come up with two solutions that I would like to get some comments
on. Any other solutions would be welcome too.
Solution 1
Update the sid in master..sysdatabases to the id of the new owner for model.
Solution 2
Temporarily Change sp_changedbowner to allow an update to model.
i.e. change
Any comments or other solutions?
Cheers Brian
-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
if db_name() in ('master', 'model', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
to
-- CANT CHANGE OWNER OF MASTER/TEMPDB --
if db_name() in ('master', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
Begs the question - how did it get in that state? The sid for those DBs
should always be 0x01, which correlates to the 'sa' login. Both
solutions you suggest are fairly questionable, but it's pretty much a
case of damned if you do, damned if you don't.
Personally, I'd be tempted to hack the sp_changedbowner proc (or rather
copy & paste the code into a new proc (and 'allow updates'
(sp_configure) so the system tables can be updated by our new proc)) to
just take out that restriction for the model database temporarily. That
way you can be sure that all the other things that sp_changedbowner does
(tweaking sysusers, etc.) happens too. But neither method would be
particularly kosher - just make sure you backup master & model before
doing any of this, as you might shoot yourself in the foot if you're not
careful.
Good luck.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
AussieBri wrote:

>Hi
>We have a model database that is owned by someone who has left the company.
>i would like to change the owner but don't seem to be able to.
>sp_changedbowner won't let me.
>We have come up with two solutions that I would like to get some comments
>on. Any other solutions would be welcome too.
>Solution 1
>Update the sid in master..sysdatabases to the id of the new owner for model.
>Solution 2
>Temporarily Change sp_changedbowner to allow an update to model.
>i.e. change
>Any comments or other solutions?
>Cheers Brian
> -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
> if db_name() in ('master', 'model', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>to
>
> -- CANT CHANGE OWNER OF MASTER/TEMPDB --
> if db_name() in ('master', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>
>

Change schemas owned

I am using SQL Server mgmt studio with 2005 db. I accidently clicked the
owned schemas while in a database user in security. When I went back to
remove the checkboxes they were greyed out. How do I remove them? Thanks.
DavidDavid,
You could, for instance, go to the dbo user and make it the owner of the
schema. That would take it away from the other user. (You may have a
specific user other than dbo that should be the owner.) Or, if they are not
used, get rid of the extra schemas altogether.
RLF
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%23kgXZKxGIHA.2328@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server mgmt studio with 2005 db. I accidently clicked the
>owned schemas while in a database user in security. When I went back to
>remove the checkboxes they were greyed out. How do I remove them? Thanks.
> David
>

Change ownership of all objects owned by

HI, I thought I read somewhere about a Stored Procedure that can be used to
change all objects owned by x to owned by y. Does anyone know of such SP,
tell me about it, please. OR Tell me a better way of changing ownership of
ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
another SQL login I've created with SA privileges, Then I can change the PW
of sa. OR can this not be done. ? PLEASE ADVISE!!This free tool may be a start...
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1116990,00.html?track=NL-464&ad=524311USCA
It uses a parameter of 'IF ^$^ <> ^dbo^ EXECUTE sp_changeobjectowner
^$.*^,^dbo^' to change the owner of selected objects.
"WANNABE" wrote:
> HI, I thought I read somewhere about a Stored Procedure that can be used to
> change all objects owned by x to owned by y. Does anyone know of such SP,
> tell me about it, please. OR Tell me a better way of changing ownership of
> ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
> another SQL login I've created with SA privileges, Then I can change the PW
> of sa. OR can this not be done. ? PLEASE ADVISE!!
>
>

Change ownership of all objects owned by

HI, I thought I read somewhere about a Stored Procedure that can be used to
change all objects owned by x to owned by y. Does anyone know of such SP,
tell me about it, please. OR Tell me a better way of changing ownership of
ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
another SQL login I've created with SA privileges, Then I can change the PW
of sa. OR can this not be done. ? PLEASE ADVISE!!This free tool may be a start...
http://searchsqlserver.techtarget.c...4&ad=524311USCA
It uses a parameter of 'IF ^$^ <> ^dbo^ EXECUTE sp_changeobjectowner
^$.*^,^dbo^' to change the owner of selected objects.
"WANNABE" wrote:

> HI, I thought I read somewhere about a Stored Procedure that can be used t
o
> change all objects owned by x to owned by y. Does anyone know of such SP,
> tell me about it, please. OR Tell me a better way of changing ownership
of
> ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
> another SQL login I've created with SA privileges, Then I can change the P
W
> of sa. OR can this not be done. ? PLEASE ADVISE!!
>
>