25

In Microsoft SQL Server, what is the difference between the CREATE DATABASE and CREATE ANY DATABASE permissions?

I am unable to find an authoritative answer. The best I can infer is that either (a) CREATE ANY implies I can create the database to be owned by another user, whereas with CREATE I can not, or (b) the original permission in the Sybase/early SQL Server days was CREATE ANY and CREATE is aliased for ANSI conformance.

Improve this question
2
  • Echoing what Aaron said, +1 but you really should cut the edit, paste it as an answer, then accept it. Two reasons: it clarifies what the answer is (and your edit is a great answer) and also it's neater (unanswered questions occasionally get bumped to the top for more attention).
    – Simon Righarts
    Commented Mar 14, 2012 at 9:27
  • 1
    Done, but I can't accept it until tomorrow, and I couldn't post any answers at all yesterday (5-hour limit to self-answer for < 100 rep).
    – Tohuw
    Commented Mar 14, 2012 at 17:39

1 Answer 1

Reset to default
26

I believe I have found the authoritative answer, helped greatly by the table provided in this article: http://msdn.microsoft.com/en-us/library/ms178569.aspx. It would seem CREATE DATABASE is indeed the ANSI SQL compliant permission, while CREATE ANY DATABASE is an MSSQL proprietary server permission. However, the two are not the same. In fact, there is a rather important distinction between the two, and I found this out by trying it.

They are both permissions, but CREATE DATABASE only consults the security context of the currently used database, while CREATE ANY DATABASE can lookup the list of logins on the SQL Server. This appears to be implemented because the general expectation for an ANSI SQL server is that permissions are conferred in some main system database, with users in that database being granted permissions. Then the default behavior of new databases is to consult master to see what permissions it ought to inherit. (SQL 101, I know, but it’s really exemplified here.)

So, when you grant CREATE DATABASE, you must actually have a user in master to grant this permission to. If you try this, it works (create a user in master, grant it create database, then you can make databases). However, by granting create any database (or granting the role dbcreator), you do not need to be a literal user in master.

To further illustrate the difference between the two permissions, observe the differing messages returned by granting a user CREATE DATABASE on master vs. another database, then rinse and repeat for the permission CREATE ANY DATABASE.

use master; GRANT CREATE DATABASE to TestUser

Msg 15151, Level 16, State 1, Line 1 Cannot find the user 'TestUser', because it does not exist or you do not have permission.

This error happens because the user TestUser is not yet in the master database. This is a database-level permission – it has no context to go outside the selected database and look for logins or users. Note that if you create the user in master first, this command succeeds, and you can create databases just as if CREATE ANY DATABASE was granted to you.

use master; GRANT CREATE ANY DATABASE to TestUser

Command(s) completed successfully.

This succeeds because, as a proprietary Microsoft SQL Server permission, CREATE ANY DATABASE can consult the list of logins for the SQL instance, not just the users in the currently used database.

use test; GRANT CREATE DATABASE to TestUser

Msg 15151, Level 16, State 1, Line 1 Cannot find the user 'TestUser', because it does not exist or you do not have permission.

This demonstrates the consistency of my explanation for the first example. Note the below query and its resultant error message. You will not receive this error message if you first create the user in this database (see the last query).

use test; GRANT CREATE ANY DATABASE to TestUser

Msg 4621, Level 16, State 10, Line 1 Permissions at the server scope can only be granted when the current database is master

As shown in the second query, CREATE ANY DATABASE consults the list of logins, where I had already created this login name. So while SQL Server acknowledged the existence of the user, it still errors out because I am attempting to grant a server-level permission somewhere other than master, which is not allowed in MSSQL.

use test; create user TestUser; grant create database to TestUser

CREATE DATABASE permission can only be granted in the master database. Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

Now that there is a user for CREATE DATABASE to apply to, I am given the generic error message that you cannot give server-level permissions anywhere but master, because that is where SQL Server stores these permissions.

Well, this was fun.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.