CODEWITHSUNDEEP
sql-server-2008t-sqlstored-proceduressql-server-2008-r2ssms
Eric Klaus
Eric Klaus

Reputation: 955

Adding stored procedure to local database

I've created a stored procedure by SA login. I can execute this stored procedure when I'm logged as SA. But when I'm logged via my login, I can't execute it, it returns error

The EXECUTE permission was denied on the object 'fcd_up_MBK', database 'workdb', schema 'dbo'.

How can I grant permission when I'm logged via my login?

Moreover, I can't see this stored procedure in Datebases > workdb > Programmability > Stored Procedures

Upvotes: 0

Views: 82

Answers (1)

T McKeown
T McKeown

Reputation: 12857

you need to logon as sa and GRANT EXECUTE to your schema

GRANT EXECUTE ON SCHEMA::userSchema TO db_execproc;

userSchema would be your login id schema, is your login id a member of dbo? If so then use dbo as the schema.

or to brute force it:

GRANT EXECUTE ON <procedurename> to <username>

Upvotes: 1

Related Questions