Hi,
I got this 'EXECUTE permission denied on object <mySproc>' error message
everytime I try executing my SQL server Sproc.
What's this and how to fix this err?
many thnaks in advance,
mikeMike
You are not probably an owner of the stored procedure.
So, DBA should grant you 'EXECUTE' permissions on the object
Also ,If you use dynamic sql within the stored procedure it will be forced
you to grant permissions on underlying tables
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ONJfv8pBFHA.1004@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I got this 'EXECUTE permission denied on object <mySproc>' error message
> everytime I try executing my SQL server Sproc.
> What's this and how to fix this err?
> many thnaks in advance,
> mike
>
>|||Thanks Uri,
Actually, I got this prob after I moved my ASP.NET web appl to a new
hosting.
And I never such prob with my old hosting.
How can I grant any permissions in such hosted environment?
rgds,
mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8wyEHrBFHA.4004@.tk2msftngp13.phx.gbl...
> Mike
> You are not probably an owner of the stored procedure.
> So, DBA should grant you 'EXECUTE' permissions on the object
> Also ,If you use dynamic sql within the stored procedure it will be forced
> you to grant permissions on underlying tables
>
> "Mike L." <globalwebservices2005@.yahoo.com> wrote in message
> news:ONJfv8pBFHA.1004@.TK2MSFTNGP15.phx.gbl...
>|||One more thing,
Everytime I log in to my SQL server using my login name/pwd, i've never get
my default DB as the first db in QA db list. It's always 'master' db.
I found out also, that my default db for my login name is not assigend to my
default db, it's to 'master' db instead.
Could this be a 'sign' that I really don't have enough permissions on my own
db?
rgds,
mike|||Hello Mike L.,
It's starting to sound like you should contact your hosting provider about
the way that your database is set up. From the sound of it it looks like
your login doesn't have permission to your database.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/
> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've
> never get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend
> to my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on
> my own
> db?
> rgds,
> mike|||You can change your default database using sp_defaultdb:
EXEC sp_defaultdb 'MyLogin', 'MyDatabase'
> Could this be a 'sign' that I really don't have enough permissions on my
> own
> db?
The default database is used when you haven't specified an initial database
context in your connection string. Since you got far enough to get a
permission error, this indicates that your login has access to your database
but insufficient permissions to execute the proc.
Your hosting provider needs to either:
1) Provide a separate administrative login that you can use to manage your
database and permissions
2) Add your login to a privileged role like db_owner
3) Perform database administration on your behalf. Was this how the
database was created?
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ORub5jsBFHA.1396@.tk2msftngp13.phx.gbl...
> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've never
> get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend to
> my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on my
> own
> db?
> rgds,
> mike
>|||Thanks Aaron, Dan,
I'll try to contact my hosting provider to fix this up.
rgds,
mike
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ORub5jsBFHA.1396@.tk2msftngp13.phx.gbl...
> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've never
get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend to
my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on my
own
> db?
> rgds,
> mike
>sql
Showing posts with label denied. Show all posts
Showing posts with label denied. Show all posts
Monday, March 26, 2012
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
>
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
>
Subscribe to:
Posts (Atom)