Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

Stored Procedure Permissions

This is my first time creating a stored procedure.  I basically copied another and tweaked it to my needs.  What I found however is that I didn't set the permissions on it so therefore my ASP page produces this error when I try to submit the form data to the database:

EXECUTE permission denied on object 'sp_myprocedurename, database 'Test', owner 'dbo'

I am not sure what type of access is setup.  I am very familiar with creating logins in SQL Server EM but not sure if we're really using that type of authentification...or even if I do create a login, I don't even see a list of stored procedures, just the ability to give dbowner rights and such to databases.
0
dba123
Asked:
dba123
  • 7
  • 6
1 Solution
 
crescendoCommented:
You need to run this in Query Analyser:

    GRANT EXCUTE ON sproc_name TO ASPNET_account

where sproc_name is the name of the sproc (obviously) and ASPNET_account is the SQL user corresponding to the Windows user running ASP.NET, usually ASPNET.
0
 
crescendoCommented:
Sorry, can't type/spell

    GRANT EXECUTE ON sproc_name TO ASPNET_account
0
 
crescendoCommented:
If the names in the error message are real, you need

    GRANT EXECUTE ON Test.dbo.sp_myprocedurename TO ASPNET_account

just putting in your ASPNET_account
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
dba123Author Commented:
We dont use .Net...does that make a diff?
0
 
SjoerdVerweijCommented:
Not really. Find out what account IIS is running as (Administrative Tools -> Services -> World Wide Web), find out what login that is on the SQL Server, and then replace ASPNET_account above with that.
0
 
crescendoCommented:
No, just use whatever user you want to give the permissions to. : )

0
 
crescendoCommented:
It's probably IUSR_SERVERNAME

where SERVERNAME is the name of the web server.
0
 
dba123Author Commented:
what is IUSER ?
0
 
dba123Author Commented:
our servername has dashes in it so it didn't like that syntax  IUSR_ddd-ddd-dddd

I want to find out what IUSR is first to be safe...not familiar with it
0
 
crescendoCommented:
IIS, the web server, creates a windows user account to run as, usually called IUSR_<the name of the server>, for example, IUSER_DEV02. You can find out who it is in the Internet Services Manager administrative tool, by looking at the properties for a web site, going to the Directory Security tab, clicking Edit, then clicking Edit on the next dialog box.

The important thing however, from your question, is that you need to grant execute permission to the right users on your SQL box. Go into Enterprise Manager, open up the Security folder, then the Logins folder. On the right you will see all the users. Their names are what you put in that GRANT command I gave you.

If you use Enterprise Manager you can set the permissions there just as easily. Find the stored procedure (look in the database you used) and double-click on its name on the right-hand window. Click on the Permissions button and you can then just tick the boxes for the users you want to run the stored procedure.
0
 
dba123Author Commented:
Yea, I am familiar with Logins like I said.  But as far as I see I only see myself and a few other coders as users in logins.
0
 
dba123Author Commented:
thanks for the last and first tip....will check out the last one
0
 
dba123Author Commented:
ahhh, thank you so much for helping me remember.  I forgot that you can do that directly using the permissions button of a sp

thanks! it worked.
0
 
crescendoCommented:
No problems, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now