User can create but not execute stored procedure

Question

Problem

I have a SQL Server login that is allowed to create stored procedures, but not execute them. I cannot use another login to grant execute so I am looking for an alternative way to either run the code in the sp or to grant these permissions.

The EXECUTE permission was denied on the object 'sp_mystoredprocedurename', database 'mydatabasename', schema 'dbo'.

The user cannot grant execute to itself

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

Background

We have a Windows software application, written in Powerbuilder, that creates and updates the SQL Server database it works on itself.

On first startup the application prompts for a database admin login which it uses 1 time (we don't store this information) to create the database and a login. The login is given db_ddladmin, db_datareader and db_datawriter permissions. We currently have hundreds of these applications and databases running on servers managed by us, but also on our customers' own servers.

For this reason I would do anything to prevent the need to ask the user for a db admin login again so I can grant the execute permissions, which would be the easiest way... Downgrading all servers to SQL Server 2000 is of course also not an option :)

The stored procedure I am trying to implement is a "getnewid" method. Currently my Powerbuilder code uses multiple embedded TSQL statements to achieve this but because of network performance issues I would like to move these to a single stored procedure.

There is no db admin, so **I am not able to use grant**. I am 99% sure this is a catch 22, and thus impossible to solve. I would just like to hear if I missed something or if there's an alternative. In SQL Server 2000 it was possible, if you were allowed to create a stored procedure, you were allowed to execute it. — Martijn Kooij, Jul 11 '13 at 11:58
HI,if you do nothing datamodification within your sql logic, the User Defined Functions (UDF) could be an alternative. In case you have right to execute/create them. — DARKinVADER, Jul 11 '13 at 12:57

score 4 · Answer 1 · answered Jul 11 '13 at 09:50

4

Does this help ?

CREATE ROLE db_executer
GRANT EXECUTE to db_executer
EXEC sp_addrolemember N'db_executer', N'<username>'

answered Jul 11 '13 at 09:50

t-clausen.dk

43,517
12
59
92

I'm afraid not, because the logged in user cannot execute stored procedures. So I cannot execute sp_addrolemember... – Martijn Kooij Jul 11 '13 at 11:16
@MartijnKooij no, of course not, you can't hack the database granting yourself more permissions, In order for your user to be able to execute all procedures including those made in the future, you need to run this script. You need a database administrator to run this for you – t-clausen.dk Jul 11 '13 at 11:51

score 0 · Answer 2 · answered Jul 11 '13 at 11:47

0

Try this.

GRANT EXECUTE ON sp_OACreate to UserLogin
GO

answered Jul 11 '13 at 11:47

Bhupendra

350
3
16

User can create but not execute stored procedure

2 Answers2