Granting permissions to stored procedures

I am using the following code to grant user access to the stored procedures in my database. However, it does not appear to be working because I am getting an access denied message when running the application as the user.

Here is the code I am using:

Code Snippet

' Grant privileges

Dim ExecutePrivilege As New ObjectPermissionSet

ExecutePrivilege.Execute = True

' Grant privileges to all non-system stored procs

' The following line improves performance

SmoServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")

For Each sp As StoredProcedure In db.StoredProcedures

If Not sp.IsSystemObject Then

sp.Grant(ExecutePrivilege, loginName)

End If

Next

Is there something else I need to do, like a Save or Refresh or something? (I've stepped through the code and it *is* executing for each of my stored procedures. It just does not appear to actually have updated the priviledge.)

Any tips or ideas would be appreciated.

Thanks!

Security related commands are always executed directly, unless you change the setting from the context to only script the commands like the following statement does:

svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

The reflected sources show that a property is set for this which is called

so.ForDirectExecution = true;

Did you have a look in the profiler to see if the commands are arriving at the server and are eventually bounced back due to errors occuring during applying the script ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I skipped the profiler and went right to the database. I can view permissions, and they are actually set correctly. So the code is executing. (Should have thought of that before I posted!<G>)

The problem is that when I try to access any of the stored procs (or the tables), I get a "permission was denied on the object" message. So something else is obviously wrong.

This particular code happens to be in VB6, accessing SQLServer Express. When the *same* code accesses a SQL Server 2000 database, it runs without this error.

Any idea what could be wrong here? Or at this point do I need to move the question elsewhere since it does not appear to be an SMO problem.

sql

Got rows that I can't get rid of

I was working with my DB. I wrote some unit test it failed and I didn't
clean up it correctly in the code. So usually in this case I just go to DB
and clean it myself by hand and lean my lesson. But this time some records I
just can't delete - it tells me that record doesn't exists because it was
modified by other user ?
I restarted machine - didn't help.
Any ideas?
Thanks,
Shimon.How are you trying to delete?
Is There a Primary Key on this table?
The message you describe sounds like an ADO or ODBC message. Can you just
go to query analyzer and do a Select and see if the row is in the database?
"Shimon Sim" wrote:

> I was working with my DB. I wrote some unit test it failed and I didn't
> clean up it correctly in the code. So usually in this case I just go to DB
> and clean it myself by hand and lean my lesson. But this time some records
I
> just can't delete - it tells me that record doesn't exists because it was
> modified by other user ?
> I restarted machine - didn't help.
> Any ideas?
> Thanks,
> Shimon.
>
>|||I delete from VS.NET from the list of records.
Everytime I refresh the query it returns me that record exists. II try to
delete dependent connected (with foreign rel) records that tells me that I
can't because there are related records.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:5EB2C616-36C0-4EAE-AFFD-C088258A8B9F@.microsoft.com...
> How are you trying to delete?
> Is There a Primary Key on this table?
> The message you describe sounds like an ADO or ODBC message. Can you just
> go to query analyzer and do a Select and see if the row is in the
> database?
> "Shimon Sim" wrote:
>|||Sorry for troubles - I just pulled up QA and queries work. I guess something
wrong with VS.
Thanks,
Shimon.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:5EB2C616-36C0-4EAE-AFFD-C088258A8B9F@.microsoft.com...
> How are you trying to delete?
> Is There a Primary Key on this table?
> The message you describe sounds like an ADO or ODBC message. Can you just
> go to query analyzer and do a Select and see if the row is in the
> database?
> "Shimon Sim" wrote:
>

Got err: There is already an object named '#TEMP' in the datab

Thank you for the reply. I did figured it out last Friday after I keep
messing with it. I have a if -else code where the if xxx then insert yyy to
#temp else insert zzz to #temp. I didn't know SQL doesn't allow #temp to be
used in this fashion which is usually allowed in development coding. I
changed the else to insert to #temp2 and it worked.
Thanks, Alpha
"Aaron Bertrand [SQL Server MVP]" wrote:

> Maybe you could show your code...
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:0F8B1D0C-6CD7-42D5-BE0E-4D473E39C188@.microsoft.com...
>
>> #temp else insert zzz to #temp. I didn't know SQL doesn't allow #temp to
> be
> used in this fashion which is usually allowed in development coding.
Well, it doesn't actually EXECUTE the code, so it doesn't understand that
your IF/ELSE can only result in one path. It merely sees you trying to
create the same #temp table twice...|||I see. Thank you for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, it doesn't actually EXECUTE the code, so it doesn't understand that
> your IF/ELSE can only result in one path. It merely sees you trying to
> create the same #temp table twice...
>
>

going over 2 selects in 1 query

i have a code L

;

conPubs =New SqlConnection(conStr) conPubs.Open()'**>> building select box for ??? ?????? cmdSelect =New SqlCommand("Select * From course_type;SELECT UploadTrail_id, UploadTrail_select_types_id FROM UploadTrail_select_values WHERE (UploadTrail_id = 54) AND (UploadTrail_select_types_id = 6);", conPubs)Dim ReaderAs SqlDataReader Reader = cmdSelect.ExecuteReader() course_type.DataSource = Reader course_type.DataValueField ="course_id" course_type.DataTextField ="course_name" course_type.DataBind()

if u look u will see 2 select's that are delimited with ; (semicolon)
how can i access the second recordset after i bind the first one on the last line
(i want to get values from the rows in the second "select")
thnaks in advance
peleg

for the second part you use

Reader.NextResult()

then as you would do for the first part.

Going nuts here!

Alright- I plugged this code in to tell my form to write to SQLServer when I click the Submit button. However I error out when I load the form:

dim Conn as new OleDbConnection("DSN=MyDB")

Sub On_Click(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "'," & _
"'" & params(1) & "'," & _
"'" & params(2) & "'")

ExecuteStatement(strSQL)
end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand(strSQL, Conn)

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as Exception
end try
objCmd.Connection.Close()
end function

I get this error:
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(14) : error BC30471: Expression is not an array or a method, and cannot have an argument list.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(14) : error BC30205: End of statement expected.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(15) : error BC30035: Syntax error.

"'" & params(0) & "'," & _

Any ideas??try this :

strSQL="INSERT INTO tblSCRequest (strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "','" & params(1) & "','" & params(2) & " ')"

better yet, use parameterized queries. (1) you dont have to worry about these quotes (2) you are safe from sql injection attacks.

hth|||So what if for simplicity sake I try this-

strSQL=""INSERT INTO tblSCRequest (strRequestor) VALUES (" & _
";" & params (0) & "')"

Would this work? Also, I saw your link to the param site. If I get this working I'm going to use that going forward. Thanks.|||no a ; indicates end of sql statement. use parameterized queries. thats the best way to go about it.

hth|||Okay. I have this problem now-

Sub Submit(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO Comm (strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "','" & params(1) & "','" & params(2) & " ')"

ExecuteStatement(strSQL)

end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand()

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as exception
end try
objCmd.Connection.Close()
end function

I get an error saying "System.NullReferenceException: Object reference not set to an instance of an object." And it is pointing to line 31 as the culprit-

Line 29: catch ex as exception
Line 30: end try
Line 31: objCmd.Connection.Close()<---Error line
Line 32: end function|||(1) use parameterized queries. ( i suggested this earlier too)
(2) make sure you have values in all the parameters.
(3) put the connection.close in the finally block

hth

going from sqlconnection and sqlreader to using a tableadapter and ????

Below is my code right now Im using a direct sqlconnection for each request.? I would like to use Tableadapters and/or my BLL code like the rest of my sites pages.? I cant find how to do this progamatically and using the xmltextwriter. (Also i need to be able to name the xml output nodes like below)

Thanks for the help.

Neil

Private Sub GetAllEvents(ByVal SqlString)

? ? ? ? Response.Clear()

? ? ? ? Response.ContentType = "text/xml"

? ? ? ? Dim objX As New XmlTextWriter(Response.OutputStream, Encoding.UTF8)

? ? ? ? objX.WriteStartDocument()

? ? ? ? objX.WriteStartElement("Events")

? ? ? ? Dim objConnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\*********.mdf;Integrated Security=True;User Instance=True")

? ? ? ? objConnection.Open()

? ? ? ? Dim sql As String = SqlString

? ? ? ? Dim objCommand As New SqlCommand(sql, objConnection)

? ? ? ? Dim objReader As SqlDataReader = objCommand.ExecuteReader()

? ? ? ? While objReader.Read()

? ? ? ? ? ? objX.WriteStartElement("Event")

? ? ? ? ? ? objX.WriteAttributeString("EventId", objReader.GetInt32(0))

? ? ? ? ? ? objX.WriteAttributeString("EventName", objReader.GetString(1))

? ? ? ? ? ? objX.WriteAttributeString("EventDescription", objReader.GetString(2))

? ? ? ? ? ? objX.WriteAttributeString("EventDate", objReader.GetDateTime(3))

? ? ? ? ? ? objX.WriteAttributeString("CurrentDate", Date.Now.ToString)

? ? ? ? ? ? If Not objReader.IsDBNull(12) Then

? ? ? ? ? ? ? ? objX.WriteAttributeString("EventImage", objReader.GetString(12))

? ? ? ? ? ? End If

? ? ? ? ? ? objX.WriteEndElement()

? ? ? ? End While

? ? ? ? objReader.Close()

? ? ? ? objConnection.Close()

? ? ? ? objX.WriteEndElement()

? ? ? ? objX.WriteEndDocument()

? ? ? ? objX.Flush()

? ? ? ? objX.Close()

? ? ? ? Response.End()

? ? End Sub

Hi,

If you wish to write all the content of the query result to an Xml, I suggest you do the following step:

1. Use a SqlDataAdapter to fill a DataSet.
2. Call DataSet.WriteXml(REsponse.OutputStream) to output it.

Dim objConnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\*********.mdf;Integrated Security=True;User Instance=True")
objConnection.Open()
Dim sql As String = SqlString
Dim DA As New SqlDataAdapter(sql, objConnection)
Dim ds As New DataSet()
DA.Fill(ds)
ds.WriteXml(Response.OutputStream)

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

That works great, However I need to have complete control over which fields are put in nodes and into attributes. We are passing the xml into flash and need to have control over each field. I have pre made table adapters and a BLL that handles all of the "BackEnd" Functions for administration. Trying to use those for the xml output as well(Dont want to dble code). Thanks for the help

Global Temporary table disappear

I have a stored procedure creating a global temporary table which is
used later in coldfusion code. Most of time it works. The coldfusion
will output what's in that global temporary table but sometimes that
global temporary table just disappear in coldfusion and the web page
errors out. I checked the procedure and the global temporary table
does get created. If you have any idea about this, please help!!!!!!!!
I'm stuck here and couldn't do anything about it. Thanks a million!Global temp tables disappear when connections stop referencing them. If the
web site has interrupted connectivity (basically every time you close a
connection, load a new page, etc) then they would not persist. If you are
okay with multiple users of the web site potentially accessing the same
global temp table, why not just use a permanent table? At least that will
only get deleted when you want it to...
A
<mirthcyy@.gmail.com> wrote in message
news:1191867216.347474.55650@.50g2000hsm.googlegroups.com...
>I have a stored procedure creating a global temporary table which is
> used later in coldfusion code. Most of time it works. The coldfusion
> will output what's in that global temporary table but sometimes that
> global temporary table just disappear in coldfusion and the web page
> errors out. I checked the procedure and the global temporary table
> does get created. If you have any idea about this, please help!!!!!!!!
> I'm stuck here and couldn't do anything about it. Thanks a million!
>|||Those global temporary table are report tables. Basically if a user
runs a report, the store procedure will do all the calculations and
put the result into a global temporary table. Then the cold fusion
will return the result from that table. All the reports can be
customized so users will pick different columns and apply different
filters so the report tables will all be different. If we are using
permenant tables, we will create a lot if multiple users are using the
same report and they may be a problem for storage issue. As for
tempdb, it will be cleared on regular basis which is more manageable.
I don't understand coldfusion much so I don't know if they are loading
a new page or not. But I will definitely ask some other developer to
check that.

global code

Is there a place where I can put custom code so that all reports in a
solution can access the same code?
I now copy the code into every new report I createYou can have assemblies that are shared between reports. Search books on
line for custom code and it will tell you all about it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:uTpyCPrCFHA.3924@.TK2MSFTNGP15.phx.gbl...
> Is there a place where I can put custom code so that all reports in a
> solution can access the same code?
> I now copy the code into every new report I create
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schreef in bericht
news:%2393IKdsCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> You can have assemblies that are shared between reports. Search books on
> line for custom code and it will tell you all about it.
Thats not what I meant. The code for calling the assemblies will have to be
duplicated in every report one creates.|||Servé La wrote:
> Thats not what I meant. The code for calling the assemblies will have
> to be duplicated in every report one creates.
I did'nt understand your question. Each report is like a single programm.
If i need an assembly in two programms, i have to write
IMPORTS myassemblyclass
in BOTH programms
frank|||I haven't done this but I think it would work. It looks to me that
report.rdl in this directory:
C:\Program Files\Microsoft SQL Server\80\Tools\Report
Designer\ProjectItems\ReportProject
Is what is used by the report wizard. My guess is if you change this then
all the new reports would have the code. Save a copy of it just in case. Let
me know if that works for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:uS0vSqsCFHA.3324@.TK2MSFTNGP15.phx.gbl...
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schreef in bericht
> news:%2393IKdsCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> > You can have assemblies that are shared between reports. Search books on
> > line for custom code and it will tell you all about it.
> Thats not what I meant. The code for calling the assemblies will have to
be
> duplicated in every report one creates.
>|||"Frank Matthiesen" <fm@.xax.de> schreef in bericht
news:36hi9eF50g2hqU1@.individual.net...
> Servé La wrote:
> > Thats not what I meant. The code for calling the assemblies will have
> > to be duplicated in every report one creates.
> I did'nt understand your question. Each report is like a single programm.
> If i need an assembly in two programms, i have to write
> IMPORTS myassemblyclass
> in BOTH programms
Suppose you have created a component where you have to set a few properties
and then calculate some value.
You create a function for this like the following:
Function Compute(name as String, x as Integer, y as Integer, z as Integer)
as Integer
Dim obj as new MyComponents.Component(name)
obj.X = x
obj.Y = y
obj.Z = z
Try
Compute = obj.Compute()
Catch e as OopsException
Compute = -1
End Try
End Function
You then want to use this function in a few reports. Right now I see no
other way then to put that code in every report and call it, while I
expected to put that function somewhere globally where all reports could
access it. In reality the code gets a lot more complex very fast.|||Could you not store the function as a 'stored procedure' in the database
then just call the stored procedure as you need it?
Dale
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:%23whb6AtCFHA.1188@.tk2msftngp13.phx.gbl...
> "Frank Matthiesen" <fm@.xax.de> schreef in bericht
> news:36hi9eF50g2hqU1@.individual.net...
> > Servé La wrote:
> > > Thats not what I meant. The code for calling the assemblies will have
> > > to be duplicated in every report one creates.
> >
> > I did'nt understand your question. Each report is like a single
programm.
> > If i need an assembly in two programms, i have to write
> > IMPORTS myassemblyclass
> > in BOTH programms
> Suppose you have created a component where you have to set a few
properties
> and then calculate some value.
> You create a function for this like the following:
> Function Compute(name as String, x as Integer, y as Integer, z as Integer)
> as Integer
> Dim obj as new MyComponents.Component(name)
> obj.X = x
> obj.Y = y
> obj.Z = z
> Try
> Compute = obj.Compute()
> Catch e as OopsException
> Compute = -1
> End Try
> End Function
> You then want to use this function in a few reports. Right now I see no
> other way then to put that code in every report and call it, while I
> expected to put that function somewhere globally where all reports could
> access it. In reality the code gets a lot more complex very fast.
>|||You are not being consistent here. On the one hand you said you understood
about using custom assemblies. On the other hand you posted this. Your code
can go in a custom assembly once. Then all you have to do is reference that
assembly in your reports. You do not have to have that function defined in
each and every report.
As I suggested before, read up on custom code. You can do custom code two
ways, code behind report and in your own assembly. What you are talking
about here is code behind report.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:%23whb6AtCFHA.1188@.tk2msftngp13.phx.gbl...
> "Frank Matthiesen" <fm@.xax.de> schreef in bericht
> news:36hi9eF50g2hqU1@.individual.net...
> > Servé La wrote:
> > > Thats not what I meant. The code for calling the assemblies will have
> > > to be duplicated in every report one creates.
> >
> > I did'nt understand your question. Each report is like a single
programm.
> > If i need an assembly in two programms, i have to write
> > IMPORTS myassemblyclass
> > in BOTH programms
> Suppose you have created a component where you have to set a few
properties
> and then calculate some value.
> You create a function for this like the following:
> Function Compute(name as String, x as Integer, y as Integer, z as Integer)
> as Integer
> Dim obj as new MyComponents.Component(name)
> obj.X = x
> obj.Y = y
> obj.Z = z
> Try
> Compute = obj.Compute()
> Catch e as OopsException
> Compute = -1
> End Try
> End Function
> You then want to use this function in a few reports. Right now I see no
> other way then to put that code in every report and call it, while I
> expected to put that function somewhere globally where all reports could
> access it. In reality the code gets a lot more complex very fast.
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schreef in bericht
news:uvhbbYtCFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You are not being consistent here. On the one hand you said you understood
> about using custom assemblies. On the other hand you posted this. Your
code
> can go in a custom assembly once. Then all you have to do is reference
that
> assembly in your reports. You do not have to have that function defined in
> each and every report.
> As I suggested before, read up on custom code. You can do custom code two
> ways, code behind report and in your own assembly. What you are talking
> about here is code behind report.
I guess I meant code behind then. I have some cases where methods need to be
called from code behind in a certain order so it would be nice to have this
functionality in one single function defined somewhere globally.
I take it the answer is "no you cant do this" then :)|||You are still missing the point. You can have code behind and you can have
custom assemblies. You can definitely put your common functions in a custom
assembly that all your reports will use. You can have both code behind and
custom assemblies. The tricky part of custom assemblies is deployment of the
assembly.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:%233S6lYODFHA.3256@.tk2msftngp13.phx.gbl...
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schreef in bericht
> news:uvhbbYtCFHA.2540@.TK2MSFTNGP09.phx.gbl...
> > You are not being consistent here. On the one hand you said you
understood
> > about using custom assemblies. On the other hand you posted this. Your
> code
> > can go in a custom assembly once. Then all you have to do is reference
> that
> > assembly in your reports. You do not have to have that function defined
in
> > each and every report.
> >
> > As I suggested before, read up on custom code. You can do custom code
two
> > ways, code behind report and in your own assembly. What you are talking
> > about here is code behind report.
> I guess I meant code behind then. I have some cases where methods need to
be
> called from code behind in a certain order so it would be nice to have
this
> functionality in one single function defined somewhere globally.
> I take it the answer is "no you cant do this" then :)
>|||"BruceLC" <brucelc@.newsgroup.nospam> schreef in bericht
news:OOGvMxRDFHA.1040@.TK2MSFTNGP09.phx.gbl...
> You are still missing the point. You can have code behind and you can have
> custom assemblies. You can definitely put your common functions in a
custom
> assembly that all your reports will use. You can have both code behind and
> custom assemblies. The tricky part of custom assemblies is deployment of
the
> assembly.
I am talking about calling a custom assembly from code behind! It's about
both!!
The methods in the custom assembly need to be called within a certain order
so this has to be done in code behind.|||Try putting your common code in a custom assembly and then placing it into
GAC
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:uTpyCPrCFHA.3924@.TK2MSFTNGP15.phx.gbl...
> Is there a place where I can put custom code so that all reports in a
> solution can access the same code?
> I now copy the code into every new report I create
>

global code

Hi ,
I wrote a function in the report property code :
Public Function AA(field as string)
AA = field
End Function
When I try to write an expretion like :
=AA(Me.Value)
I get and error of AA is not recognize.
What is the problem ?
10x.You need to call your function as =Code.AA(Me.Value) . This will
solve your problem mentioned error.
I am doubtfull about what you exactly mean by Me.Vlaue
Hope this helps.
Thanks,
Mahesh