-1

T-SQL Problem: My user is not admin but I want to run a query using my sa user and password from my current user only.

Explanation:

My Current User

USER: [shashank] --This is not admin user

My Admin

USER: sa

Pass: 12345

I am logged in as [shashank] and want to run some admin queries without logging in to [sa].

EXECUTE AS doesn't seems work for me, because it need to be sysadmin and do not require password.

Thanks in advance.

Community
  • 1
  • 1
Shashank
  • 19
  • 1
  • 3
  • 1
    Your explanation of why `EXECUTE AS` doesn't work for you is unclear. That's how I'd do it... – Ben Thul Nov 17 '14 at 21:48
  • Please clearly explain why "execute as" does not work. – Jason D Nov 17 '14 at 21:49
  • EXECUTE AS as per MSDN webaddress [link](http://msdn.microsoft.com/en-us/library/ms181362.aspx) I can only enter admin username, how can I enter password in this syntax ? Can you please share the exact query if it is possible ? – Shashank Nov 17 '14 at 21:52
  • This question could be a bit clearer regarding why `EXECUTE AS` doesn't work, but I don't think it deserves down votes. It's a concise programming question and the OP has put effort into trying to solve the problem before coming to StackOverflow. – Ed Gibbs Nov 17 '14 at 21:55
  • You don't need to specify the password with `EXECUTE AS`. You grant the user permissions to run the Proc or function, and then it just works. [Please see the documentation](http://msdn.microsoft.com/en-us/library/ms188354.aspx). – Bacon Bits Nov 17 '14 at 21:57
  • Thanks Bacon. But this is what my problem is. I can't give permission to user to run Proc or Function. That's why I can't use EXECUTE AS with my current user, so I want any alternate solution (apart of using EXECUTE AS) – Shashank Nov 17 '14 at 22:03
  • So you're literally trying to figure out how to grant user permissions when you don't have permission to run the `GRANT` statement? It's time to have a conversation with your manager, DBA or sysadmin about the difference between security and business requirements. – Bacon Bits Nov 18 '14 at 13:53

1 Answers1

1

Here are two options:

  1. Encapsulate the EXECUTE AS in a stored procedure that only your user has EXECUTE permissions on.

    CREATE PROCEDURE dbo.DoSomething
AS
SET NOCOUNT ON;

EXECUTE AS LOGIN = 'sa';

-- do stuff

REVERT;

  2. Manage the elevated permissions via signing a stored procedure, that again, only your user has EXECUTE permissions on, with a certificate. Look at this answer here that sounds like a nearly identical scenario (the answer does not use EXECUTE AS):

    SQL Server: EXECUTE AS clause of stored procedure not granting sysadmin permissions

    Three benefits of going the signed stored procedure route are:

    1. You don't have to worry about IMPERSONATE permissions
    2. You don't have to deal with the consequences of impersonation, which might come into play depending on what operations are being done while impersonating "sa".
    3. You might not even need full "sysadmin" credentials. Depending on what operations(s) you are doing, you might be able to get away with assigning more restrictive roles and/or permissions so as to avoid the increased security risk of additional code that is allowed to do anything.
Community
  • 1
  • 1
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • I can't provide permission to my current user to run EXECUTE AS command. So I want if there is any alternate solution to run admin queries provided I have admin username and password with me. – Shashank Nov 17 '14 at 22:05
  • 1
    @Shashank : did you look at the "or" part of this answer? The link to the other S.O. question involves signing the proc and doesn't need EXECUTE AS. – Solomon Rutzky Nov 17 '14 at 22:09