CODEWITHSUNDEEP
sql-servert-sql
S Nash
S Nash

Reputation: 2499

SQL Server running script with Execute causes permission error

I have a stored procedure Which we call it Test here.

For simplicity I modified the SP code to:

Select * from table A

I can run this SP without any issues.

Now if I use Dynamic SQL 

@SQL='Select * from table A'

EXECUTE (@SQL)

I get 
The SELECT permission was denied on the object 'A', database 'MyDb', schema 'dbo'.

What is different here?

Upvotes: 0

Views: 61

Answers (2)

M.Ali
M.Ali

Reputation: 69564

Dynamic SQL has this restriction/limitation. When you use dynamic sql inside a stored procedure , even if the calling user has the permissions on the stored procedure , the user also need permissions on the tables/objects being called inside the dynamic sql .

You have two options

  1. Do not use Dynamic sql at all.
  2. Give the calling user permissions on the table being used inside the dynamic sql.

Upvotes: 4

A_Sk
A_Sk

Reputation: 4630

This error comes when the user does not have the sufficient privileges to access your tables in the database. Do grant the privilege to the user in order to get what you want.

Grant The Permission For Select statement(or any other if you want).

Upvotes: 0

Related Questions