Permissions on Stored Procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Permissions on Stored Procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-24 : 07:41:02

Mike writes "We are running SQL 2k SP4. In one of our databases, I am getting the following message when I attempt to run any stored procedure:

*************************************************
Server: Msg 229, Level 14, State 5, Procedure prc_2_6_insert_userhistory,
Line 1

EXECUTE permission denied on object 'prc_2_6_insert_userhistory', database
'DCS', owner 'dbo'.
*************************************************
The procedure is:
CREATE PROCEDURE dbo.prc_2_6_insert_userhistory
@OldUserID char(10),
@NewUserID char(10),
@OldName char(50),
@NewName char(50),
@OldLevel tinyint,
@NewLevel tinyint,
@OldPassword char(50),
@NewPassword char(50),
@ChangeID char(10),
@ChangeType char(1),
@IDENTITY Numeric(9) OUTPUT
AS
INSERT INTO dbo.[UserAccessHistory]
(
[UH_OldUserID],
[UH_NewUserID],
[UH_OldName],
[UH_NewName],
[UH_OldLevel],
[UH_NewLevel],
[UH_OldPassword],
[UH_NewPassword],
[UH_ChangeDate],
[UH_ChangeID],
[UH_ChangeType]
)
VALUES (
@OldUserID,
@NewUserID,
@OldName,
@NewName,
@OldLevel,
@NewLevel,
@OldPassword,
@NewPassword,
GETDATE(),
@ChangeID,
@ChangeType
)
SET @IDENTITY = @@IDENTITY
RETURN @@ERROR
GO
******************************************************
I have been granted dbo access to the database, and can run the contained SQL statement with NO errors:

INSERT INTO dbo.[UserAccessHistory]
([UH_OldUserID], [UH_NewUserID], [UH_OldName],
[UH_NewName], [UH_OldLevel], [UH_NewLevel],
[UH_OldPassword], [UH_NewPassword], [UH_ChangeDate],
[UH_ChangeID],[UH_ChangeType])
VALUES ('CDPICH', 'CDPICH', 'MIKE ADAMS', 'MIKE ADAMS', 5,
5, '', '', GETDATE(), 'TEST','T' ) .

ANY ideas would truly be appreciated.

Mike Adams"

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-24 : 08:11:19

GRANT EXECUTE TO MyUser ON MyStoredProcedure

====================================================
Regards,
Sean Roussy

Thank you, drive through

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-24 : 10:09:29

What Sean wrote would go right after your last GO statement.

And it does not matter that you have access in Enterprise Manager to see the query, or rights to compile the query in Query Analyzer. The code that executes the stored proc is the one that has to have access rights.

How is the stored proc called? Is it a ASP or VB application? If so, you are probably using an ODBC connection or a UDL or something to connect your app to the database. That connection string is what needs to have execute rights on the stored proc.

It can get awfully complex to set some of this stuff up. We can help if you can give us information, but your best bet might be to Google on granting permissions on stored procedures.

Aj

Hey, it compiles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources