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.

3
  • Post the complete command line that you're running.
    – mfinni
    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?
    – mfinni
    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?
    – ssmith
    Sep 24, 2010 at 18:54

2 Answers 2

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.

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! Sep 24, 2010 at 19:55
  • As I suspected, too :-) Happens to all of us.
    – mfinni
    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'
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. Sep 24, 2010 at 19:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .