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--
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment