1

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.

Community
  • 1
  • 1
DiskJunky
  • 4,750
  • 3
  • 37
  • 66
  • 2
    how do you check if the db exists? – juergen d Oct 24 '17 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)` – DiskJunky Oct 24 '17 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 – Panagiotis Kanavos Oct 24 '17 at 15:23
  • @PanagiotisKanavos I know it works, I've been using it for years. I'm puzzled as to why it's not working *this time*. Something's awry. – DiskJunky Oct 24 '17 at 15:24
  • 1
    How do you even know it isn't? Have you typed `USE SomeDB;` and got an error? – Panagiotis Kanavos Oct 24 '17 at 15:24
  • @PanagiotisKanavos that's the subsequent line in the original script that's failing after `CREATE DATABASE SomeDB; GO;`. So I moved the `CREATE DATABASE` into a script on its own, ran it and refreshed the DB list to check. Same result - no DB created. – DiskJunky Oct 24 '17 at 15:26
  • 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 – Panagiotis Kanavos Oct 24 '17 at 15:27
  • Is it creating the database under a schema you dont have privileges for in your. SSMS connection? – KacireeSoftware Oct 24 '17 at 15:28
  • @PanagiotisKanavos I've posted the entire script as minimum reproducable code. `CREATE DATABASE SomeDB; GO`, on its own, is failing silently. There is no preceding `USE` in the minimum reproducible script above – DiskJunky Oct 24 '17 at 15:28
  • @KacireeSoftware no schema involved apart from standard, basic `dbo`. Run of the mill SQL here, that's what so nuts about it. – DiskJunky Oct 24 '17 at 15:29
  • @DiskJunky final repetition. If you use `USE SomeDB` do you get an error? You didn't post anything that reproduces any bug. You *assume* that it's failinig silently because some other script returns something or other – Panagiotis Kanavos Oct 24 '17 at 15:29
  • @PanagiotisKanavos if I call `USE SomeDB` after `CREATE DATABASE SomeDB; GO`, I get `Database 'SomeDB' does not exist. Make sure that the name is entered correctly.` – DiskJunky Oct 24 '17 at 15:30
  • Have you actually tried with `SomeDB`? Could the *name* be different or have typos? Different language? Characters that look the same yet aren't? Have you copied the original string or retyped it? What does `sys.databases` show after you create the database? – Panagiotis Kanavos Oct 24 '17 at 15:37
  • @PanagiotisKanavos good question and yes I have. The message response from `USE SomeDB` was a copy/paste out of the SSMS Messages panel – DiskJunky Oct 24 '17 at 15:38
  • @DiskJunky I'm not talking about the message. Are you sure the name in `create database ` and `use database ` are the same? And what does `select * from sys.databases` show? What is the *exact* script? – Panagiotis Kanavos Oct 24 '17 at 15:39
  • @PanagiotisKanavos I've updated my question with the requested tests and their results – DiskJunky Oct 24 '17 at 15:45

3 Answers3

2

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.

DiskJunky
  • 4,750
  • 3
  • 37
  • 66
0

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.

Dog
  • 115
  • 10
  • The question is less about "How do I create a database?" and more "Why can't I create a database like this?" – DiskJunky Oct 24 '17 at 19:33
-1

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

Aswani Madhavan
  • 816
  • 6
  • 19