I am using a stored procedure that on first execution runs without complaining about permissions. The stored procedure has only one UID/PWD set up (there are not different sets of UIDs/PWDs for granting different permission levels). The single pair provides permission to everything.
My code to call the stored procedure is this:
DataTable dtPriceComplianceResults
SQLDBHelper.ExecuteSQLReturnDataTable(PriceComplianceConstsAndUtils.SUMMARY_STOREDPROC, CommandType.StoredProcedure,
new SqlParameter()
{
ParameterName = "@BegDate",
SqlDbType = SqlDbType.VarChar,
Value = _begDateStr
},
new SqlParameter()
{
ParameterName = "@EndDate",
SqlDbType = SqlDbType.VarChar,
Value = _endDateStr
},
new SqlParameter()
{
ParameterName = "@Member",
SqlDbType = SqlDbType.VarChar,
Value = _member
},
new SqlParameter()
{
ParameterName = "@Unit",
SqlDbType = SqlDbType.VarChar,
Value = _unit
});
public static DataTable ExecuteSQLReturnDataTable(string sql, CommandType cmdType, params SqlParameter[] parameters)
{
using (DataSet ds = new DataSet())
using (SqlConnection connStr = new SqlConnection(PriceComplianceConstsAndUtils.CPSConnStr))
using (SqlCommand cmd = new SqlCommand(sql, connStr))
{
cmd.CommandType = cmdType;
cmd.CommandTimeout = EXTENDED_TIMEOUT;
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
try
{
cmd.Connection.Open();
new SqlDataAdapter(cmd).Fill(ds);
}
catch (SqlException sqlex)
{
for (int i = 0; i < sqlex.Errors.Count; i++)
{
var sqlexDetail = String.Format("From ExecuteSQLReturnDataTable(), SQL Exception #{0}{1}Source: {2}{1}Number: {3}{1}State: {4}{1}Class: {5}{1}Server: {6}{1}Message: {7}{1}Procedure: {8}{1}LineNumber: {9}",
i + 1, // Some users would get the fantods if they saw #0
Environment.NewLine,
sqlex.Errors[i].Source,
sqlex.Errors[i].Number,
sqlex.Errors[i].State,
sqlex.Errors[i].Class,
sqlex.Errors[i].Server,
sqlex.Errors[i].Message,
sqlex.Errors[i].Procedure,
sqlex.Errors[i].LineNumber);
MessageBox.Show(sqlexDetail);
}
}
catch (Exception ex)
{
String exDetail = String.Format(PriceComplianceConstsAndUtils.ExceptionFormatString, ex.Message, Environment.NewLine, ex.Source, ex.StackTrace);
MessageBox.Show(exDetail);
}
return ds.Tables[0];
}
}
Here is the first part of the stored procedure:
ALTER procedure [dbo].[sp_duckbilled_platypus]
@BegDate varchar(10),
@EndDate varchar(10),
@Member varchar(max),
@Unit varchar(max)
AS
drop table zDistDBPExceptions
select (ph.memberno), TotalDesExceptions=1
into zDistDBPExceptions
from priceexceptionshistory ph
inner join MasterUnits MU on ph.Unit=MU.Unit
Inner Join Members M on ph.memberno = M.MemberNo
where ph.memberNo not in ('04501','04503') --,'111','B140')
and ph.memberno in (select [value] from dbo.split(@member,','))
and ph.Unit in (select [value] from dbo.Split(@Unit,','))
and invoicedate between @BegDate and @EndDate
and filtered=0
and abs(contractprice) = 0
and abs(ph.pricepush) = 0
and bidprice > 0
and abs(MU.TruTrack) = 1
and abs(ph.pricesheet) = 1
drop table zContractDBPExceptions
select (ph.memberno), TotalContractExceptions=1
into zContractDBPExceptions
from priceexceptionshistory ph
inner join MasterUnits MU on ph.Unit=MU.Unit
Inner Join Members M on ph.memberno = M.MemberNo
where ph.memberNo not in ('04501','04503') --,'111','B140')
and ph.memberno in (select [value] from dbo.split(@member,','))
and ph.Unit in (select [value] from dbo.Split(@Unit,','))
and invoicedate between @BegDate and @EndDate
and filtered=0
and abs(contractprice) = 1
and abs(ph.pricepush) = 1
and bidprice > 0
and abs(MU.TruTrack) = 1
drop table zDBPExceptions
select (ph.memberno), TotalPriceSheetExceptions=1--, invoicedate
into zDBPExceptions
from priceexceptionshistory ph
inner join MasterUnits MU on ph.Unit=MU.Unit
Inner Join Members M on ph.memberno = M.MemberNo
where ph.memberNo not in ('04501','04503') --,'111','B140')
and ph.memberno in (select [value] from dbo.split(@member,','))
and ph.Unit in (select [value] from dbo.Split(@Unit,','))
and invoicedate between @BegDate and @EndDate
and filtered=0
and abs(contractprice) = 0
and abs(ph.pricepush) = 1
and bidprice > 0
and abs(MU.TruTrack) = 1
drop table zSumtDBPExceptions
select (ph.memberno), TotalSumExceptions=1
into zSumtDBPExceptions
from priceexceptionshistory ph
inner join MasterUnits MU on ph.Unit=MU.Unit
Inner Join Members M on ph.memberno = M.MemberNo
where ph.memberNo not in ('04501','04503') --,'111','B140')
and ph.memberno in (select [value] from dbo.split(@member,','))
and ph.Unit in (select [value] from dbo.Split(@Unit,','))
and invoicedate between @BegDate and @EndDate
and filtered=0
and bidprice > 0
and abs(MU.TruTrack) = 1
and abs(ph.pricesheet) = 1
--this gets all invoice data
--insert into PriceExceptionsHistory
-- *** zContractDBPBase ***
drop table zContractDBPBase
. . .
The very odd (ISTM) thing that's happening is yesterday the stored procedure failed to run at all, telling me that either the tables being dropped (all being dropped in the stored procedure, each one listed in turn) either did not exist or I did not have permission to them. They do all exist. So permissions seemed to be the issue.
However, this morning, with no changes to the code or the database, the stored procedure at first ran without complaining of permissions problems (the first time only).
On the second execution, though, it complained about my not having permissions for the zContractDBPBase table. I guess I now have permissions for the previous four being dropped, but not this one...?!?
IOW, the err msg I get (after "Query completed with errors") is now:
Msg 3701, Level 11, State 5, Procedure sp_zDBP_pella, Line 80
Cannot drop the table 'zContractDBPBase', because it does not exist or you do not have permission.
...whereas formerly it was that same message, but for all the dropped tables, not just one.
So why are permissions mutable? I refreshed the list of tables, and still see the table that I apparently don't have permissions to ("zContractDBPBase"); it is visible on refreshing both in Server Explorer within the Visual Studio IDE and in LINQPad.
What do I need to do (aside from changing the stored procedure itself, which is out of my realm of responsibility and expertise) to get the stored procedure to permit "me" to drop the tables?
Pikoh's comment brought me back to something I had wondered earlier - if having LINQPad running simultaneously was problematic - both the LINQPad "user" and the Visual Studio "user" were both hanging on to those connections, or SPs, or something that was causing a kerfluffle.
I shut down LINQPad, and no longer have those problems - neither running the SP via Server Explorer in VS, or via the C# code in the app.
I love LINQPad to pieces, but it apparently doesn't play well with SQL Server, or vice versa.