CODEWITHSUNDEEP
sql-serverdatabaset-sqlssms
DiskJunky
DiskJunky

Reputation: 4991

CREATE DATABASE runs successfully but no DB created

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.

Upvotes: 3

Views: 4130

Answers (3)

DiskJunky
DiskJunky

Reputation: 4991

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.

Upvotes: 3

Dog
Dog

Reputation: 115

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.

Upvotes: 0

Aswani Madhavan
Aswani Madhavan

Reputation: 816

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

Upvotes: -1

Related Questions