1

I created the following login/user

user master
create login user1 with password=N'....', default_database=[TestDB], check_expiration=off, check_policy=on;
use TestDB
create user user1 for login user1

create role testRole;
grant exec to testRole;

Then I created a test proc and run it using the new login

create proc test 
as 
select top 10 * from table1;

exec('exec test') as login = 'user1'

The exec returns rows even exec('select top 10 * from table1') as login = user1 failed because of no permission.

Then I created another proc using dynamic Sql.

create proc test1 
as 
exec('select top 10 * from table1');

exec('exec test1') as login = 'user1'

Now it fails for lack of permission. How to make test1 be able to be run by user1 without explicit granting select permission on the table (I don't want the user1 be able to select the table directly) ?

arghtype
  • 4,376
  • 11
  • 45
  • 60
ca9163d9
  • 27,283
  • 64
  • 210
  • 413
  • You are presuming lots here: who has privilege to run the proc, the login and user that the proc runs under, and the context by which the object was given permission to execute to the targeted user. Instead, use USER_NAME() and SUSER_NAME() when running the script and find out the context – clifton_h Jan 25 '17 at 01:54
  • Why don't you create a view instead? – SQL_Deadwood Jan 25 '17 at 03:48

0 Answers0