Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Post the complete command line that you're running. –  mfinni Sep 24 '10 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 '10 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 '10 at 18:54
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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! –  Scott Sep 24 '10 at 19:55
    
As I suspected, too :-) Happens to all of us. –  mfinni Sep 25 '10 at 0:51
add comment

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'
share|improve this answer
    
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. –  Scott Sep 24 '10 at 19:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.