Re: Database Owner
From: Michael (mminzloff_at_co.dutches.ny.co)
Date: 05/19/03
- Next message: Michael: "Permissions for executing a stored procedure"
- Previous message: Bottomless Pit: "Re: Application Role and access to 'other' databases"
- In reply to: Dan Guzman: "Re: Database Owner"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 19 May 2003 10:40:54 -0400
Thanks Dan. Changing the owner with the changedbowner stored procedure
worked. I actually think I understand it now too.
Thanks again.
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:uVO#urnGDHA.2152@TK2MSFTNGP12.phx.gbl...
> > If our DBA is now the owner, shouldn't they be the only one with
> > owner rights? We are finding that the user who originally created the
> > database on the test server can run a stored procedure that can drop
> and
> > create tables even though there userid doesn't have rights to do so.
>
> This is because you have a mismatch between the owner recorded in
> sysdatabases and the 'dbo' entry in the sysusers table. After the
> restore, the sysusers table will still contain the original SID from the
> test server so the developer will be mapped to 'dbo' if they have a SQL
> Server login on the production server.
>
> You can use sp_changedbowner to change the owner as desired. For
> example:
>
> Use MyDatabase
> EXEC sp_changedbowner 'NewOwner'
> GO
>
> If you get message 'the proposed owner is already a user in the
> database', try:
>
> Use MyDatabase
> EXEC sp_addlogin 'TempOwner'
> EXEC sp_changedbowner 'TempOwner'
> EXEC sp_changedbowner 'NewOwner'
> EXEC sp_droplogin 'TempOwner'
> GO
>
> Note that you may have a similar issue with non-dbo database users as
> well. These can be reported/corrected with sp_change_users_login. See
> the Books Online for details.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> -----------------------
> SQL FAQ links (courtesy Neil Pike):
>
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> -----------------------
>
> "Michael" <mminzloff@co.dutches.ny.co> wrote in message
> news:udDr73hGDHA.1600@tk2msftngp13.phx.gbl...
> > We have been using a backup/restore procedure to copy databases from
> our
> > test server to our production server. We use the SQL Server backup
> utility
> > to backup the database on the test server and then use the restore
> utility
> > to put it down on the production server. In our situation, the user
> who is
> > the owner on the test server backs up the database and then our DBA
> does the
> > restore on the production server. This appears to make the DBA the
> owner on
> > the production server, but we are finding that the user who created
> the
> > database on test still seems to have owner privledges on the
> production
> > server. If our DBA is now the owner, shouldn't they be the only one
> with
> > owner rights? We are finding that the user who originally created the
> > database on the test server can run a stored procedure that can drop
> and
> > create tables even though there userid doesn't have rights to do so.
> >
> > Any comments would be appreciated.
> >
> >
>
>
- Next message: Michael: "Permissions for executing a stored procedure"
- Previous message: Bottomless Pit: "Re: Application Role and access to 'other' databases"
- In reply to: Dan Guzman: "Re: Database Owner"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
