Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Stored Procedure Permissions

Posted on 2004-08-05
14
482 Views
Last Modified: 2010-08-05
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
Comment
Question by:dba123
  • 7
  • 6
14 Comments
 
LVL 9

Expert Comment

by:crescendo
ID: 11731477
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
 
LVL 9

Expert Comment

by:crescendo
ID: 11731481
Sorry, can't type/spell

    GRANT EXECUTE ON sproc_name TO ASPNET_account
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11731486
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:dba123
ID: 11731503
We dont use .Net...does that make a diff?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11731513
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
 
LVL 9

Expert Comment

by:crescendo
ID: 11731522
No, just use whatever user you want to give the permissions to. : )

0
 
LVL 9

Expert Comment

by:crescendo
ID: 11731528
It's probably IUSR_SERVERNAME

where SERVERNAME is the name of the web server.
0
 
LVL 1

Author Comment

by:dba123
ID: 11731538
what is IUSER ?
0
 
LVL 1

Author Comment

by:dba123
ID: 11731561
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
 
LVL 9

Accepted Solution

by:
crescendo earned 500 total points
ID: 11731606
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
 
LVL 1

Author Comment

by:dba123
ID: 11731654
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
 
LVL 1

Author Comment

by:dba123
ID: 11731656
thanks for the last and first tip....will check out the last one
0
 
LVL 1

Author Comment

by:dba123
ID: 11731662
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
 
LVL 9

Expert Comment

by:crescendo
ID: 11731671
No problems, glad to help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL create line numbers for data sampling 11 28
Query for timesheet application 3 18
SQL Quer 4 22
SQL - Simple Pivot query 8 15
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question