Problem
I have a SQL Server account that is allowed to create stored procedures but not execute them. I cannot use another login to execute the execution, so I am looking for an alternative way to run the code in sp or grant these permissions.
EXECUTE permission was denied in the sp_mystoredprocedurename object, in the mydatabasename database, in the dbo schema.
User cannot perform execution on his own
Unable to grant, deny or revoke permissions for sa, dbo, entity owner, information_schema, sys or on its own.
Background
We have Windows software written in Powerbuilder that creates and updates a SQL Server database, which works on its own.
At the first start, the application requests the login of the database administrator, which uses 1 time (we do not store this information) to create the database and login. Login is granted with permissions of db_ddladmin , db_datareader and db_datawriter . Currently, we have hundreds of such applications and databases on the servers we manage, as well as on our clients' own servers.
For this reason, I will do everything to prevent the need to again ask the user to log in to admin db admin, so I can grant execution permissions, which would be the easiest way ... Migrating all servers to SQL Server 2000 is of course also not an option :)
The stored procedure I am trying to implement is the getnewid method. My Powerbuilder code currently uses several built-in TSQL statements to accomplish this, but due to network performance issues, I would like to move them to a single stored procedure.