dba123
asked on
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.
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.
Sorry, can't type/spell
GRANT EXECUTE ON sproc_name TO ASPNET_account
GRANT EXECUTE ON sproc_name TO ASPNET_account
If the names in the error message are real, you need
GRANT EXECUTE ON Test.dbo.sp_myprocedurenam e TO ASPNET_account
just putting in your ASPNET_account
GRANT EXECUTE ON Test.dbo.sp_myprocedurenam
just putting in your ASPNET_account
ASKER
We dont use .Net...does that make a diff?
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.
No, just use whatever user you want to give the permissions to. : )
It's probably IUSR_SERVERNAME
where SERVERNAME is the name of the web server.
where SERVERNAME is the name of the web server.
ASKER
what is IUSER ?
ASKER
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
I want to find out what IUSR is first to be safe...not familiar with it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
thanks for the last and first tip....will check out the last one
ASKER
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.
thanks! it worked.
No problems, glad to help.
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.