Re: Creating a new database via a store procedure

From: Steve Thompson (SteveThompson_at_nomail.please)
Date: 07/25/03 

Date: Fri, 25 Jul 2003 14:39:25 -0400

"PRAYMOND" <PRAYMOND@MORNEAUSOBECO.COM> wrote in message
news:09c001c352ba$9e46ca60$a601280a@phx.gbl...
> I would like to have a store procedure that will create a
> database after verifying some condition. This stored
> procedure must be callable by an user. I tought that
> since the creator of the stored proc had the Create Table
> wright that any user having the wright to execute this
> stored procedure would execute in the context of the
> creator and then would be able to create the table. This
> does not seem to work. Am I missing something here. I
> want to do this because I want to implement some security
> policy via an external tool that would permit copy or
> creation of specific database to some user. Not an
> overall copy or creation of all databases.

I do not believe that a CREATE DATABASE statement is allowed in a stored
procedure. I hunted through BOL looking for a reference for you, but could
not find one.

Regarding create database permissions, from BOL:

"CREATE DATABASE permission defaults to members of the sysadmin and
dbcreator fixed server roles. Members of the sysadmin and securityadmin
fixed server roles can grant CREATE DATABASE permissions to other logins.
Members of the sysadmin and dbcreator fixed server role can add other logins
to the dbcreator role. The CREATE DATABASE permission must be explicitly
granted; it is not granted by the GRANT ALL statement.
CREATE DATABASE permission is usually limited to a few logins to maintain
control over disk usage on an instance of SQL Server."

Steve

Relevant Pages

  • Re: Permission Denied error
    ... CREATE DATABASE permission defaults to members of the sysadmin and dbcreator ... SQL Scribe Documentation Builder, the quickest way to document your database http://www.ag-software.com/ags_SSEPE_index.aspx. ...
    (microsoft.public.sqlserver.setup)
  • Re: Logging in irrespective of database access
    ... Try connecting using Query Analyzer with one of the problem logins and run ... the following in the context of your demodata and accounts databases: ... This will return the security context used to access the database. ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Try connecting using Query Analyzer with one of the problem logins and run ... the following in the context of your demodata and accounts databases: ... This will return the security context used to access the database. ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Changing security authentication type.
    ... i'm thinking that yes in time the sa account ... A new DBA Admin User login would only be useful for SQL Login purposes. ... if appropriate you can add users to the db_owner role in a database. ... Create one or more logins for the applications. ...
    (microsoft.public.sqlserver.security)
  • Re: Help Ive been an idiot
    ... You need to make sure that you readd any logins that were mapped to database ... Jasper Smith (SQL Server MVP) ... > connectionString, CommandType commandType, String commandText, ...
    (microsoft.public.sqlserver.setup)