-2

I have an SQL user 'ABC', having sysadmin rights and access to all the databases, lets say database A and Database B, of the specific server. Where as my application uses users having access to only one database A. I have created an stored procedure to insert records in tables of database B. So I am using EXECUTE AS clause to execute stored procedure with user ABC, but after testing I found that if my application users don't have exactly the same server roles as the use ABC have,it don't work. When I make my other user sysadmin, it gives no error and works great. If both users needs to have same roles then what's the use of this EXECUTE AS clause? Or am I missing something?

Almas Mahfooz
  • 896
  • 1
  • 10
  • 19
  • post your sql code. is impossible to tell what's wrong in your procedure without the code. maybe is a permission issue but maybe not. – Paolo Jan 12 '15 at 12:51
  • Paolo, issue is not with the procedure code, issue is related with permission, and I am clearly asking about the permission issue. – Almas Mahfooz Jan 13 '15 at 12:15

1 Answers1

0

I think you question has already been answered here: SQL Server Execute Impersonation

execute as user = 'ABC' --The scope of impersonation is restricted to the current database.

vs

execute as login = 'ABC' --The scope of impersonation is at the server level.

Community
  • 1
  • 1
oob2
  • 11
  • 1
  • Ok let me try execute as login='ABC'. I will update you if this helps me out or not. – Almas Mahfooz Jan 13 '15 at 12:17
  • I can not add execute as login='ABC' with stored procedures, it's giving error. Error is Incorrect syntax near 'login'. Below is my code. 'code' alter procedure storedProcedure with Execute as login -- @r char(15) output as insert into Table1(ID,NAME) values('11','sam') go'code' – Almas Mahfooz Jan 22 '15 at 11:34