problem with orphaned "dbo" user of an attached database

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,
HolgerHolger (Holger@.discussions.microsoft.com) writes:
> 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?
You could put the ALTER AUTHORSIZATION statement in a stored procedure
that you sign with a certificate, and then you grant a login associated
with that cert CONTOL SERVER. Note that the login is not a real login,
that is, it cannot connect.
For a lot more detail on this, see this article on my web site:
http://www.sommarskog.se/grantperm.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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.blog...on.
html
"Holger" <Holger@.discussions.microsoft.com> wrote in message
news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
> 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|||Hi Erland,
thanks for your answer. I know that this is a solution but the problem is
that, for ALTER AUTHORIZATION I need a login and a certificate inside the
master database and also a master key.
I'd like to fairly avoid storing information of any kind inside master -
regardless of what information this is. That's the reason why I was asking
for a solution without a signed stored procdure.
"Erland Sommarskog" wrote:

> Holger (Holger@.discussions.microsoft.com) writes:
> You could put the ALTER AUTHORSIZATION statement in a stored procedure
> that you sign with a certificate, and then you grant a login associated
> with that cert CONTOL SERVER. Note that the login is not a real login,
> that is, it cannot connect.
> For a lot more detail on this, see this article on my web site:
> http://www.sommarskog.se/grantperm.html
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Hi Uri,
thanks for your answer. As for our SQL Server - we use Windows
authentication. But I do know nothing about the source Server,so it should
work with all authentication modes. Also I guess, in order work with Windows
authentication, the prerequisite will certainly be that all servers reside i
n
one domain, which is not the case. I get databases from unkown sources from
all over the world and simply have to use those.
regards,
Holger
"Uri Dimant" wrote:

> Holger
> What is authentication are you using?
> See if this helps
> http://dimantdatabasesolutions.blog...o
n.html
>
>
> "Holger" <Holger@.discussions.microsoft.com> wrote in message
> news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
>
>|||Holger (Holger@.discussions.microsoft.com) writes:
> thanks for your answer. I know that this is a solution but the problem is
> that, for ALTER AUTHORIZATION I need a login and a certificate inside the
> master database and also a master key.
> I'd like to fairly avoid storing information of any kind inside master -
> regardless of what information this is. That's the reason why I was asking
> for a solution without a signed stored procdure.
If you prefer you can use impersonation in the procedure, that is EXECUTE
AS.
I'm not sure that I understand your reluctance against storing information
in master. After all, it is here you have information about logins,
databases, server-level permissions, the service master key in master.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx