EXEC in stored procedure

Hello!
I'm trying to use
EXEC ('SELECT * FROM Categories')
into a Stored Procedure, but the users get permission denied on object
'Categories'
If I'm using
SELECT * FROM Categories
everything works.
The user haven't any permission of Categories, but the SP should have it.
Is the EXEC command inside the SP run as the user? Why?
Regards MagnusHi Magnus
The dynamic select doesn't obey the same ownership chaining rules as
statements outside the EXEC. Users running the stored procedure must have
explicit permissions on the objects in the dynamic SQL.
A SELECT outside of the EXEC obey ownerships chaining rules, and any user
who has execute permission on procedure can run the statements in the
procedure, as long as the procedure and the table have the same owner.
Why do you need the EXEC? It serves no purpose here.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Magnus Blomberg" <magnus.blomberg@.skanska.se> wrote in message
news:utP4%2373uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I'm trying to use
> EXEC ('SELECT * FROM Categories')
> into a Stored Procedure, but the users get permission denied on object
> 'Categories'
> If I'm using
> SELECT * FROM Categories
> everything works.
> The user haven't any permission of Categories, but the SP should have it.
> Is the EXEC command inside the SP run as the user? Why?
> Regards Magnus
>|||When you use:
EXEC ('SELECT * FROM Categories')
... you are using dynamic SQL. Thus, the person running it must have their
permissions checked against the underlying objects - Categories, in this
case.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Magnus Blomberg" <magnus.blomberg@.skanska.se> wrote in message
news:utP4%2373uEHA.4084@.TK2MSFTNGP10.phx.gbl...
Hello!
I'm trying to use
EXEC ('SELECT * FROM Categories')
into a Stored Procedure, but the users get permission denied on object
'Categories'
If I'm using
SELECT * FROM Categories
everything works.
The user haven't any permission of Categories, but the SP should have it.
Is the EXEC command inside the SP run as the user? Why?
Regards Magnus|||> Is the EXEC command inside the SP run as the user? Why?
One reason I can think of is because dynamic SQL is a very serious security
concern, and if you allow any table name -- or any SQL command, for that
matter -- to run, just because the user can run the stored procedure, then a
user can wreak havoc on your system quite easily.
So, the engine verifies permission once it resolves the dynamic SQL...
A|||Hi all of you!
Well, I thought I should get that question. This message is written at home
without any SQL, so it might be small code errors.
First I can tell you, this SP doesn't accept any input parameters, so I
think the security riscs are quite small.
I must also say, that the system is rather old and is using application
role, so the objects aren't made for any user access for the users.
The reason for using dynamic SQL is that the SP will collect info about
tables into table #tbls as TableName, IDColumn, ValueColumn etc.
Then this is used by a Cursor to add data from the tables specified in #tbls
to table #rows.
Eg: INSERT #tbl (@.IDColumn,@.ValueColumn) INTO #rows FROM @.TableName
Correct me if I'm wrong but the row above is not valid, because it's not
using dynamic SQL, yea?!?!
Then the SP should return as SELECT * FROM #rows
This is the complete purpose, and I found this very difficult not using
EXEC...
Any ideas how to do the similar without using dynamic SQL.
Regards Magnus
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OgfAhO4uEHA.3456@.TK2MSFTNGP14.phx.gbl...
> One reason I can think of is because dynamic SQL is a very serious
security
> concern, and if you allow any table name -- or any SQL command, for that
> matter -- to run, just because the user can run the stored procedure, then
a
> user can wreak havoc on your system quite easily.
> So, the engine verifies permission once it resolves the dynamic SQL...
> A
>

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igor
This looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pro...05/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifying
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>
|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/pro...05/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there somethin
g
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igorThis looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pr...005/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure
. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifyin
g
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/pr...005/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

Hello!

I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting
and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As
a
result, we have 40-50 other connections waiting, then next connection
using
this procedure has [COMPILE] lock etc. Client is fully qualifying
stored
procedure by database/owner name and it doesn't start with sp_. I know

these are the reasons for [COMPILE] lock being placed. Is there
something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The
stored
procedure is very simple (I know it could be rewritten/optimized but
our
developer wrote it):

CREATE PROCEDURE [dbo].[vsp_mail_select]

@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified
folder
@.unread_count int = null output -- count of unread mail in specified
folder

AS
SET NOCOUNT ON

select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
@.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0

GO

I was monitoring server for a couple of day before and I am not sure
why
this happens every 3-4 days only!

Any help on this matter would be greately appreciated!

Thanks,
IgorSee:
http://support.microsoft.com/defaul...B;en-us;q263889
http://support.microsoft.com/?kbid=836136
Maybe one of them (or the related articles) will help.

Razvan

EXCEPTION_ACCESS_VIOLATION

hello!

i am trying to import a text file using dts into a table created by the
package. everything checks out until it is run. the table is created
but then i get an error:
"need to run the object to perform the operation. Provider generated
code execution exception EXCEPTION_ACCESS_VIOLATION"

any ideas?
this is a development box. maybe the server cant handle it?
thanks in advance!
Tom<tomcaml@.yahoo.com> wrote in message
news:1106153734.711603.163950@.z14g2000cwz.googlegr oups.com...
> hello!
> i am trying to import a text file using dts into a table created by the
> package. everything checks out until it is run. the table is created
> but then i get an error:
> "need to run the object to perform the operation. Provider generated
> code execution exception EXCEPTION_ACCESS_VIOLATION"
>
> any ideas?
> this is a development box. maybe the server cant handle it?
> thanks in advance!
> Tom

You don't mention your version of MSSQL, but here are a couple of related KB
articles:

http://support.microsoft.com/kb/268413/EN-US/
http://support.microsoft.com/kb/271889/EN-US/

Another possibility is to try setting the task to execute on the main
package thread:

http://www.sqldts.com/default.aspx?232

If that doesn't help, you might want to post to
microsoft.public.sqlserver.dts, with more details of your environment and
exactly what task is failing.

Simon

Exception - Insert Record into DB

Hello

I'm using express edition to create my trail testapplication. Below is the code that I have and I'm trying to insert data to thedatabase table named "Coin".

ProtectedSubbtnSave_Click(ByVal senderAsObject,ByVal eAsSystem.EventArgs)Handles btnview.Click

Dim sAsString = txtCname.Text

'Dim myConnection As NewSqlConnection(myConnString)

Dim descAsString = txtCDesc.Text

Dim ConStrAsNew SqlClient.SqlConnection

ConStr.ConnectionString ="server=test\sqlinstance;Integrated Security=True"

Response.Write("Connection string: " & ConStr.ConnectionString)

Try

Dim SelectQueryAsString ="SELECTmax(coinid) from coin"

Dim idvalAsInteger = 0

Dim commandAsNew SqlCommand(SelectQuery, ConStr)

ConStr.Open()

idval = command.ExecuteScalar()

Console.WriteLine(idval)

idval = idval + 1

Dim InsertQueryAsString ="INSERTINTO COIN(coinname, coinid, ebayid, ebaymember, ebaymemid , amount , coindesc)VALUES('1992-Proof'," & idval &",'eewerwer','sp6937','serwryana',67.70,'MattProff of 1992- Mint Set')"

Dim command1AsNew SqlCommand(InsertQuery, ConStr)

command1.ExecuteScalar()

Dim S1AsString ="Recordinsert - Successful!"

Console.WriteLine(S1)

Catch exAsException

Label2.Text = ex.ToString

Finally

ConStr.Close()

EndTry

End Sub

This program isthrowing an exception (mentioned below)

Exception --> System.Data.SqlClient.SqlException:Invalid object name 'coin'. atSystem.Data.SqlClient.SqlConnection.OnError(SqlException exception, BooleanbreakConnection) atSystem.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjectstateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSetbulkCopyHandler, TdsParserStateObject stateObj) atSystem.Data.SqlClient.SqlDataReader.ConsumeMetaData() atSystem.Data.SqlClient.SqlDataReader.get_MetaData() atSystem.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) atSystem.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResultresult) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) atSystem.Data.SqlClient.SqlCommand.ExecuteScalar() at _Default.btnview_Click(Objectsender, EventArgs e) in C:\Documents and Settings\arsha\My Documents\VisualStudio 2005\WebSites\WebSite1\Default.aspx.vb:line 92

"Coin" is the table name – which is in SQL server. Kindlyhelp me to handle and overcome this exception.

Thanks

perhaps you have set up your database with a case sensitive collation?

Try altering your sql statement so it's case exactly matches your table.

also, i would strongly recommend that you let sql server take care of incrementing the CoinId data by using an identity column. By trying to do it yourself, if 2 people run your page simultaneously, you could end up with a problem.

|||

Try using ExecuteNonQuery instead ofExecuteScalar