locked
How do you script out the "create any database" permission? RRS feed

  • Question

  • This post is in regards to SQL Server 2008.

    I am updating our permissions scripts. Part of the script adds a user to the dbcreator server role. We find this undesirable in that this users would be able to drop/alter any database on the server and that would be a security risk. We want to give it the "create any database" server permission in which it would only be able to drop/alter the database this account owns.

    How can I give the "create any database" permission to an account using T-SQL?

    Monday, March 21, 2011 4:20 PM

Answers

  • Yes you are absolutely correct "dbcreator" server role will give drop and alter permissions to logins, also the login which have this permission can grant this server role to any other logins which would be big security risk.

    instead you can grant "create any database".

    here is syntax

    GRANT CREATE  ANY DATABASE TO [DomainName\DomainUserName]

     


    • Proposed as answer by WeiLin Qiao Wednesday, March 23, 2011 2:10 AM
    • Marked as answer by WeiLin Qiao Wednesday, March 30, 2011 2:09 AM
    Monday, March 21, 2011 7:59 PM
  • Yes , CREATE ANY DATABASE permission is better than db_creator . You can use the command to see all available permissions in SQL server
    --  SELECT * FROM sys.fn_builtin_permissions(DEFAULT)

    Once you get the permission , then you can use GRANT/DENY/REVOKE on the permission to account / login name.

    GRANT CREATE ANY DATABASE to LOGINNAME
    Thanks, Leks
    • Proposed as answer by WeiLin Qiao Wednesday, March 23, 2011 2:10 AM
    • Marked as answer by WeiLin Qiao Wednesday, March 30, 2011 2:09 AM
    Tuesday, March 22, 2011 9:58 PM

All replies

  • If it is only DROP/ALTER existing database, then you can make the user a db_owner of the database in question.

    I am not aware of a permission (other than db_crteator) that will allow you to create databases (other than sysadmin, etc).

    My suggestion would be to create a stored procedure which contains an EXECUTE AS LOGIN clause, that takes the CREATE DATABASE statement as a parameter and then executes it with sp_executesql


    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Monday, March 21, 2011 6:58 PM
  • Yes you are absolutely correct "dbcreator" server role will give drop and alter permissions to logins, also the login which have this permission can grant this server role to any other logins which would be big security risk.

    instead you can grant "create any database".

    here is syntax

    GRANT CREATE  ANY DATABASE TO [DomainName\DomainUserName]

     


    • Proposed as answer by WeiLin Qiao Wednesday, March 23, 2011 2:10 AM
    • Marked as answer by WeiLin Qiao Wednesday, March 30, 2011 2:09 AM
    Monday, March 21, 2011 7:59 PM
  • You can use following script to script the explicit permissions for CREATE ANY DATABASE.

    SELECT
    sp.[name] [server_principal],sp.[type_desc] [principal_type],perm.[permission_name],perm.[state_desc]
    FROM [sys].[server_permissions] perm
    INNER JOIN [sys].[server_principals] sp ON perm.[grantee_principal_id] = sp.[principal_id]
    WHERE sp.[type] IN ('S', 'U', 'G')
     and permission_name like 'create any%'
     

     

    You will need to use diffrent script to script out dbcreater server level role. Let me know if you need that also.

     


    Cheers Sunil Gure
    Tuesday, March 22, 2011 5:37 AM
  • Yes , CREATE ANY DATABASE permission is better than db_creator . You can use the command to see all available permissions in SQL server
    --  SELECT * FROM sys.fn_builtin_permissions(DEFAULT)

    Once you get the permission , then you can use GRANT/DENY/REVOKE on the permission to account / login name.

    GRANT CREATE ANY DATABASE to LOGINNAME
    Thanks, Leks
    • Proposed as answer by WeiLin Qiao Wednesday, March 23, 2011 2:10 AM
    • Marked as answer by WeiLin Qiao Wednesday, March 30, 2011 2:09 AM
    Tuesday, March 22, 2011 9:58 PM