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

Holger (Holger@xxxxxxxxxxxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: ok so gimme an opinion
    ... You are proposing to alter or delete files on someone else's computer ... without their permission. ... I'm saying take them off a server if she's put them up on the client's ...
    (rec.equestrian)
  • Re: How to GRANT *all* permissions to *all* users?
    ... permission to do anything ... I'm asking because I recently converted from SQL Server 2000 to 2005 ... GRANT VIEW ANY DEFINITION TO public ... But I still can't ALTER tables. ...
    (microsoft.public.sqlserver.security)
  • Re: Long delay before Drives & Files appear in My Computer & Address Bar
    ... > SERVICE JEFF-DZP8HX39G2 The machine-default permission settings do not ... > grant Local Activation permission for the COM Server application with ... > Ratcliff JEFF-DZP8HX39G2 The Adobe LM Service service was successfully ... > within the required timeout. ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: Network shares cannot connect
    ... After a reboot, domain users cannot access any shares on the SBS server, ... Microsoft Online Newsgroup Support ... let's focus on the Users Shared Folder first. ... To check this permission, please click the Advanced button, ...
    (microsoft.public.windows.server.sbs)
  • Re: Kerberized authorization service
    ... You could backend such a thing with LDAP or whatever you want ... the mail server could have an shell group with one ... While this could no doubt be made to work, it would tie the authorization ... I only have a very basic understanding of Kerberos but I'd love to help or at ...
    (comp.protocols.kerberos)