Problem with DBO after restore

Hi,
I support an application and part of it has an archive function. It takes
backups of a main database (using the SQL backup command) and saves them to
be restored at a later date to an archive database on the same SQL Server
instance for reporting on past events. All of this has worked fine until
recently. The user that we do this under is a SQL Server login and until
recently was a sysadmin. Based on customer requests we have lowered the
permissions on this user to be DBO on the databases it needs to backup and
restore to and also a member of the dbcreator fixed server role. Everything
still works fine except in one case. When the user moves everything to a new
SQL Server. When this happens and they try to restore one of the backups to
the archive database, after it restores our user is no longer the DBO and has
no access to the database. I originally thought this was because the SIDs
were different between the two machines so I used the sp_help_revlogin
procedure to create the user on the new SQL Server with the SID from the old
machine. This worked because I do not see our user as an orphan when running
sp_change_users_login but unfortunately this did not fix the problem. After
the restore it is still not DBO. I can give our user access again by running
sp_Changedbowner after the database restore but that isn't practical for
customers especially since it requires sysadmin authority and the need for
our user to not be a sysadmin was what prompted this change in the first
place. Thanks in advance for any help.
WayneWayne
Well , it is well known issue
Please search on internet for "sp_help_revlogin script" and read the
explanation
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:51F9F2CC-E47F-45F0-B9D6-76B21F1E3E02@.microsoft.com...
> Hi,
> I support an application and part of it has an archive function. It takes
> backups of a main database (using the SQL backup command) and saves them
> to
> be restored at a later date to an archive database on the same SQL Server
> instance for reporting on past events. All of this has worked fine until
> recently. The user that we do this under is a SQL Server login and until
> recently was a sysadmin. Based on customer requests we have lowered the
> permissions on this user to be DBO on the databases it needs to backup and
> restore to and also a member of the dbcreator fixed server role.
> Everything
> still works fine except in one case. When the user moves everything to a
> new
> SQL Server. When this happens and they try to restore one of the backups
> to
> the archive database, after it restores our user is no longer the DBO and
> has
> no access to the database. I originally thought this was because the SIDs
> were different between the two machines so I used the sp_help_revlogin
> procedure to create the user on the new SQL Server with the SID from the
> old
> machine. This worked because I do not see our user as an orphan when
> running
> sp_change_users_login but unfortunately this did not fix the problem.
> After
> the restore it is still not DBO. I can give our user access again by
> running
> sp_Changedbowner after the database restore but that isn't practical for
> customers especially since it requires sysadmin authority and the need for
> our user to not be a sysadmin was what prompted this change in the first
> place. Thanks in advance for any help.
> Wayne|||Thanks Uri,
Ok, just wanted to confirm that is how it is and that I wasn't missing
something.
Wayne
"Uri Dimant" wrote:
> Wayne
> Well , it is well known issue
> Please search on internet for "sp_help_revlogin script" and read the
> explanation
>
>
>
>
> "Wayne" <Wayne@.discussions.microsoft.com> wrote in message
> news:51F9F2CC-E47F-45F0-B9D6-76B21F1E3E02@.microsoft.com...
> > Hi,
> > I support an application and part of it has an archive function. It takes
> > backups of a main database (using the SQL backup command) and saves them
> > to
> > be restored at a later date to an archive database on the same SQL Server
> > instance for reporting on past events. All of this has worked fine until
> > recently. The user that we do this under is a SQL Server login and until
> > recently was a sysadmin. Based on customer requests we have lowered the
> > permissions on this user to be DBO on the databases it needs to backup and
> > restore to and also a member of the dbcreator fixed server role.
> > Everything
> > still works fine except in one case. When the user moves everything to a
> > new
> > SQL Server. When this happens and they try to restore one of the backups
> > to
> > the archive database, after it restores our user is no longer the DBO and
> > has
> > no access to the database. I originally thought this was because the SIDs
> > were different between the two machines so I used the sp_help_revlogin
> > procedure to create the user on the new SQL Server with the SID from the
> > old
> > machine. This worked because I do not see our user as an orphan when
> > running
> > sp_change_users_login but unfortunately this did not fix the problem.
> > After
> > the restore it is still not DBO. I can give our user access again by
> > running
> > sp_Changedbowner after the database restore but that isn't practical for
> > customers especially since it requires sysadmin authority and the need for
> > our user to not be a sysadmin was what prompted this change in the first
> > place. Thanks in advance for any help.
> >
> > Wayne
>
>|||The SQL 2000 SP4 version of sp_changedbowner proc has a hard-coded check for
sysadmin role membership. A possible work-around (unsupported) is to create
a special version of this system proc, changing the role check from
'sysadmin' to 'dbcreator' (see script below). I don't know if this is
palatable in your case but I can't think of another solution.
Backup/restore really isn't intended for routine application functionality
and even 'dbcreator' is a very powerful role.
-- create a system proc (unsupported) --
sp_configure 'allow', 1
GO
RECONFIGURE WITH OVERRIDE
GO
--*** create new sp_changedbowner_dbcreator here ***
GO
EXEC sp_MS_marksystemobject 'sp_changedbowner_dbcreator'
GO
sp_configure 'allow', 0
GO
RECONFIGURE
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:51F9F2CC-E47F-45F0-B9D6-76B21F1E3E02@.microsoft.com...
> Hi,
> I support an application and part of it has an archive function. It takes
> backups of a main database (using the SQL backup command) and saves them
> to
> be restored at a later date to an archive database on the same SQL Server
> instance for reporting on past events. All of this has worked fine until
> recently. The user that we do this under is a SQL Server login and until
> recently was a sysadmin. Based on customer requests we have lowered the
> permissions on this user to be DBO on the databases it needs to backup and
> restore to and also a member of the dbcreator fixed server role.
> Everything
> still works fine except in one case. When the user moves everything to a
> new
> SQL Server. When this happens and they try to restore one of the backups
> to
> the archive database, after it restores our user is no longer the DBO and
> has
> no access to the database. I originally thought this was because the SIDs
> were different between the two machines so I used the sp_help_revlogin
> procedure to create the user on the new SQL Server with the SID from the
> old
> machine. This worked because I do not see our user as an orphan when
> running
> sp_change_users_login but unfortunately this did not fix the problem.
> After
> the restore it is still not DBO. I can give our user access again by
> running
> sp_Changedbowner after the database restore but that isn't practical for
> customers especially since it requires sysadmin authority and the need for
> our user to not be a sysadmin was what prompted this change in the first
> place. Thanks in advance for any help.
> Wayne