Solved

SQL Server 2008 Impersonation

Posted on 2011-02-21
3
688 Views
Last Modified: 2012-05-11
Hi,

I am using impersonation to give my web server user limited rights to my database.

I am grant execute on stored procedure for the web server to use.
I then created a login name limited with datareader and datawriter roles to access the data within my stored procedure.
I am using Execute as user = 'limited' in my t-sql to perform the actions that I need.

My question is, since no password is required when using the "Execute as user" statement, what is to stop a unauthorized user from including this and executing SQL with the rights of the "limited" user?

Thanks
0
Comment
Question by:dilithiumtoys_dot_com
  • 2
3 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34948187
>> My question is, since no password is required when using the "Execute as user" statement, what is to stop a unauthorized user from including this and executing SQL with the rights of the "limited" user?

From BOL:

"Additionally, IMPERSONATE permissions must be granted on the principal. Unless the caller is the database owner, or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL Server through a Windows group membership."

In order for you to use EXECUTE AS clause, you should have IMPERSONATE rights on the login you are trying to impersonate. Or else you should be part of that particular database owner or sysadmin to perform that activity.
By ensuring this, security is compromised and hope this clarifies.
0
 

Author Comment

by:dilithiumtoys_dot_com
ID: 34982383
Thanks for the reply!

I do get that part. My question is what is to stop a malicious user who gains control of the account that has been granted the impersonate rights from using those rights him or herself?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 250 total points
ID: 34985479
>> My question is what is to stop a malicious user who gains control of the account that has been granted the impersonate rights from using those rights him or herself?

If a person has database owner or sysadmin privilege or IMPERSONATE privilege, they would be able to do it..
So, it would be better to grant the above permissions carefully as required
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question