3

I'm running the following T-SQL statement from SSMS

CREATE DATABASE SomeDB
GO

With a result

Commands completed successfully.

But no database is actually created. I've been researching and came across this post which has the same behavior. The solution for that post was the run the script under an account with rights to modify sys.databases.

However, the user I'm running the script under and connecting to the DB as is in role sysadmin which is more than enough to create a database.

Any ideas as to what's going on here?

EDIT 1

If I change the script (and this is the whole script, with a DB actually called SomeDB to test), to the following

CREATE DATABASE SomeDB
GO
USE SomeDB

I get the following in SSMS's Messages panel.

Msg 911, Level 16, State 1, Line 56

Database 'SomeDB' does not exist. Make sure that the name is entered correctly.

If I change this to

CREATE DATABASE SomeDB
GO
SELECT * from sys.databases

I see the following in the Messages panel

Commands completed successfully.

But there is no Results panel. This would imply that access to sys.databases is restricted but it's weird that there's no error message.

EDIT 2

Taking this further and trying to narrow down the issue, I've run the following via an unelevated command line;

sqlcmd -S .\SQLExpress2014 -Q "CREATE DATABASE SomeDB"

And this time the database does exist. This narrows down the issue to SSMS itself rather than SQL Server or a syntax quirk.

Improve this question
16
  • 2
    how do you check if the db exists? Commented Oct 24, 2017 at 15:21
  • Not especially relevant, it's the SQL in the post that's running and failing when in a script all to itself...but to answer, the preceding line in the initial version is IF (DB_ID(N'SomeDB') IS NOT NULL) Commented Oct 24, 2017 at 15:22
  • 1
    CREATE DATABASE works. People would have noticed in the last 20+ years if it didn't. Post what you actually tried. There's no IF (DB_ID(N'SomeDB') IS NOT NULL) in what you posted. The linked question referes to something entirely different. DON'T try random things or you may harm your server and lose data Commented Oct 24, 2017 at 15:23
  • 1
    How do you even know it isn't? Have you typed USE SomeDB; and got an error? Commented Oct 24, 2017 at 15:24
  • 1
    What script? You haven't posted anything. I repeat, have you used USE SomeDb; and got an error? Otherwise you should be looking for bugs in your script, not assuming there's no database Commented Oct 24, 2017 at 15:27

3 Answers 3

Reset to default
3

Solution: Run SSMS as Admin.

Despite CREATE DATABASE working fine via an unelevated command line, SSMS requires admin privileges to do the same. The silent failure is...a possible bug?

I'll do further research on this but my working hypothesis is that when executing via a command line, it uses the SQL Server Windows Service instance's credentials (Network Service for older versions, NT Service\MSSQL$SQLEXPRESS for later versions - there's a strong whiff of a permissions issue here), to write to the %programfiles% folder. SSMS uses the currently logged in user (unelevated) if connecting via a Windows account. Without elevation, there is no write access to %programfiles%.

Still though, even if this is the case (to be verified), there should still be an access error when executing CREATE DATABASE in this context.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

1.- Run SSMS as Admin and create the database 2.- After the command shows it succesfully created the database, try disconnecting from the object explorer and connect again to see if it shows up.

Improve this answer

1 Comment

The question is less about "How do I create a database?" and more "Why can't I create a database like this?"
-1

Right click on databases folder in Object explorer and refresh. Then check wheter DB exists or not..

Improve this answer

3 Comments

That's what I'm doing to check if it actually got created after the SQL runs
@DiskJunky are you sure you are doing it on the correct server?
Yes, I'm certain.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.