Re: problem with orphaned "dbo" user of an attached database

Holger
db). So, in that case, the "dbo" is orphaned, which leads to the situation
that the owner of the database is not able to access her own db. I know
that

What is authentication are you using?
See if this helps
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html




"Holger" <Holger@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@xxxxxxxxxxxxxxxx
Hi everybody,

I'm in a situation where I allow my users of SQL Server the creation of
her
own databases. This is been done in order to allow the "creator" (the
owner)
full acces to the created DB. Everything works fine, as long as the DB is
created from scratch, like:
CREATE DATABASE universe
But additionally, my users are also exchanging databases, so they will
also
use
CREATE DATABASE universe ... FOR ATTACH
When attaching a database, the owner of the so created database is the
login
that
performed the operation, which is fine. But, the "dbo" user in the
attached
database is still associated with the original login (before detaching the
db). So, in that case, the "dbo" is orphaned, which leads to the situation
that the owner of the database is not able to access her own db. I know
that
this can be fixed with
ALTER AUTHORIZATION ON DATABASE::universe to [login]
So, after attaching a database, the user can simply all ALTER
AUTHORIZATION.
My problem is that ALTER AUTORIZATION requires "CONTROL SERVER"
permission,
which is simply a synonym for "sysadmin" role membership and therefore not
what I want. All I want my users grant is "CREATE ANY DATABASE"
permission.
Does anybody know a solution besides doing the CREATE DATABASE ... FOR
ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with
a
regarding signature?

Thanks,
Holger


.

Relevant Pages

  • Re: SQL 2005 Express-Database does not have a valid owner?
    ... That means that the database's owner is MYDOMAIN\MyUserName ... My design machine is running Windows XP Pro. ... database diagram. ... Database diagram support objects cannot be installed because this ...
    (microsoft.public.sqlserver.security)
  • Re: conflicting object names in sql server 2000
    ... The owner of the object will depend on the user ... On the local database server when it does the select * from ... she must specify the owner: ...
    (microsoft.public.sqlserver.server)
  • Re: unknown database owner
    ... cleared for the 'Admin' user. ... the "owner is unknown" (you described ... An old database, ... Rick Brandt, Microsoft Access MVP ...
    (microsoft.public.access.security)
  • Re: Cross database update issue
    ... Also, for dbo-owned objects, the owner of both databases must be the ... You can execute sp_helpdb to determine the current database owners. ... > User Name: Test1 Role Name=Test ... > Role Test has select permission to table t_test ...
    (microsoft.public.sqlserver.security)
  • Re: Cant access any old tables
    ... I used the same access rights to test the table in the different database. ... Is there a place to set a TABLE owner separate from the database owner? ... but one was SQL based and the other Windows Secured. ...
    (microsoft.public.sqlserver.security)