2

When running a command line executable, one of the tasks is to drop a database. When this gets run, SQL Server tells me that 'DBName' does not exist or you do not have permission. The database is confirmed to exist. At this point in the script, it has already been taken offline and set into single user mode (to ensure that there are no active connections prior to deletion). How come the program cannot drop the database?

EDIT: The user I am running as is able to delete the database manually via SQL Server Management Studio.

The command line executable I am running is actually a compiled C# program that is executing DROP DATABASE SQL statement via System.Data.SqlClient.SqlConnection. It is connecting to SQL Server using the same connection credentials (localhost with integrated security) as used to connect via the GUI.

The actual SQL getting executed (copy/pasted into SQL Server to test) is:

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DBName') DROP DATABASE DBName

EDIT 2: This program Works On My Machine (tm), but not on the remote server that it needs to be run on.

Improve this question
3
  • Post the complete command line that you're running. Commented Sep 24, 2010 at 17:31
  • Also - have you confirmed that the user you're running it as has the permissions necessary? Could you manually drop the database from the GUI? Commented Sep 24, 2010 at 17:31
  • Anything in the event log? Is the remote user running the app in the Database Owner role for that database on the remote server? Commented Sep 24, 2010 at 18:54

2 Answers 2

Reset to default
3

Process monitor (www.sysinternals.com) will let you know if there is a file level permission issue and if the command line executable is really running under the user you expect it to.

For spite, try a sql auth connection string vs the windows auth that you're using. That may help you narrow down permission issues if this is permission related.

Improve this answer
2
  • I fired up SQL Server Profiler and noticed that it was in fact not running as the user I thought it was, as you guessed. It was in fact running as a user that connects through via web.config. I changed it to run as local and it ran without a hitch. Thanks! Commented Sep 24, 2010 at 19:55
  • As I suspected, too :-) Happens to all of us. Commented Sep 25, 2010 at 0:51
2

My guess is that dropping it into Single User Mode (or taking it offline) is causing System.Data.SqlClient.SQLConnection to lose its mind.

That, and I assume your connection is being made to the master database when you execute this statement?

You might also try something like the following to kill connections manually (note, this can/will wreak havoc - but since you're nuking the DB anyhow... I assume those condiderations don't matter too much). 

USE master GO DECLARE killer CURSOR LOCAL FAST_FORWARD FOR SELECT spid FROM dbo.sysprocesses WHERE dbid = DB_ID('dbname here') DECLARE @spid int DECLARE @sql nvarchar(20) OPEN killer FETCH NEXT FROM killer INTO @spid WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = N'KILL ' + CAST(@spid as nvarchar(5)) EXEC sp_executesql @sql FETCH NEXT FROM killer INTO @spid END CLOSE killer DEALLOCATE killer GO WAITFOR DELAY '00:00:00.500'
Improve this answer
1
  • I switched to using this method rather than going into single user mode. I've kept this solution in place. However, the problem still persists. Commented Sep 24, 2010 at 19:49

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.