ExecuteNonQuery on a stored proc causes it to be deleted

Question

This is a strange one. I have a Dev SQL Server which has the stored proc on it, and the same stored proc when used with the same code on the UAT DB causes it to delete itself!

Has anyone heard of this behaviour?

SQL Code:

-- Check if user is registered with the system
IF OBJECT_ID('dbo.sp_is_valid_user') IS NOT NULL
BEGIN
  DROP PROCEDURE dbo.sp_is_valid_user
  IF OBJECT_ID('dbo.sp_is_valid_user') IS NOT NULL
    PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_is_valid_user >>>'
  ELSE
    PRINT '<<< DROPPED PROCEDURE dbo.sp_is_valid_user >>>'
END
go

create procedure dbo.sp_is_valid_user

@username as varchar(20),
@isvalid as int OUTPUT

AS 
BEGIN

    declare @tmpuser as varchar(20) 

    select @tmpuser = username from CPUserData where username = @username

    if @tmpuser = @username
        BEGIN
        select @isvalid = 1
        END
    else
    BEGIN
    select @isvalid = 0
        END

END
GO

Usage example

DECLARE @isvalid int
exec dbo.sp_is_valid_user 'username', @isvalid OUTPUT
SELECT valid = @isvalid

The usage example work all day... when I access it via C# it deletes itself in the UAT SQL DB but not the Dev one!!

C# Code:

    public bool IsValidUser(string sUsername, ref string sErrMsg)
    {
        string sDBConn = ConfigurationSettings.AppSettings["StoredProcDBConnection"];

        SqlCommand sqlcmd = new SqlCommand();
        SqlDataAdapter sqlAdapter = new SqlDataAdapter();

        try
        {
            SqlConnection conn = new SqlConnection(sDBConn);
            sqlcmd.Connection = conn;
            conn.Open();

            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = "sp_is_valid_user";

            // params to pass in 
            sqlcmd.Parameters.AddWithValue("@username", sUsername);

            // param for checking success passed back out 
            sqlcmd.Parameters.Add("@isvalid", SqlDbType.Int);
            sqlcmd.Parameters["@isvalid"].Direction = ParameterDirection.Output;

            sqlcmd.ExecuteNonQuery();

            int nIsValid = (int)sqlcmd.Parameters["@isvalid"].Value;

            if (nIsValid == 1)
            {
                conn.Close();
                sErrMsg = "User Valid";
                return true;
            }
            else
            {
                conn.Close();
                sErrMsg = "Username : " + sUsername + " not found.";
                return false;
            }
        }
        catch (Exception e)
        {
            sErrMsg = "Error :" + e.Source + " msg: " + e.Message;
            return false;
        }
    }

Answer 1

Ok, I have found the answer ... simple when you know how!

I saw this link here :

Disappearing Stored Procedure

http://stackoverflow.com/questions/858648/disappearing-stored-procedure/858890#858890

So from the best answer in that I ran :

select syo.name
from syscomments syc
  join sysobjects syo on
    syo.id = syc.id
where syc.[text] like '%DROP PROC%'

This gave me one of my OTHER stored procs back... sp_is_user_admin, which didn't seem right so I had a quick look ...

create procedure dbo.sp_is_user_admin
@username as varchar(20),
@isadmin as int OUTPUT
AS 
BEGIN

    declare @profile as varchar(20)

    select @profile = profile from CPUserData where username = @username

    if @profile = 'admin'
        BEGIN
        select @isadmin = 1
        END
    else
        BEGIN
        select @isadmin = 0
        END

END

--*********************************************************************************

-- Check if user is registered with the system
IF OBJECT_ID('dbo.sp_is_valid_user') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp_is_valid_user
    IF OBJECT_ID('dbo.sp_is_valid_user') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_is_valid_user >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_is_valid_user >>>'
END

Doh!!! There is the blighter... in the C# what happens is that if the user is valid I also choose what to let them see based on if they are admin or not and calling that was blitzing the sp_is_valid_user proc. Nasty side effect!

// check the user is entitled to use the system at all 
if (usrData.IsValidUser(sCurrentUserName, ref sErrMsg))
{
    // if the user is admin then let them spoof and edit their own data 
    if (usrData.UserIsAdmin(sCurrentUserName, ref sErrMsg))
    {
        chkSpoof.Visible = true;
        grdvwUserDataFromDB.Visible = true;
    }
}
else
{
    // redirect them away
    Response.Redirect("UserNotRegistered.aspx");
    return;

}

I hope this helps someone else out!

PS: DB Artisan is nasty and if I had the full fat SQL Server available in my Development toolkit then I guess I could have used the profiler to see this being called. ;P I can't install SQL Server 2008 as I don't have the right SP / updates to Visual Studio I think and IT here can't sort it out, annoying!!