Posted Feb 12, 2010
This is the first book to outline the capabilities of SQL Server 2000, one of the key components of.NET. SQL Server 2000 introduces powerful new data mining functionality designed specifically to capture and process customer profiles and to predict future buying patterns on e-commerce sites. In this article. Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance You can define a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. Creating a primary key automatically creates a corresponding unique clustered index, or a nonclustered index if specified as such.
By Deanna Dicken
SQL Server impersonation, or context switching, is a means to allow the executing user to assume the permissions of a given user or login until the context is set back, set to yet another user, or the session is ended. Deanna Dicken shows you two mechanisms for accomplishing this task and walks through some examples. Introduction
SQL Server impersonation, or context switching, is a meansto allow the executing user to assume the permissions of a given user or loginuntil the context is set back, set to yet another user, or the session isended. In the following sections we will discuss two mechanisms foraccomplishing this task and walk through some examples. SETUSER
If you have cause to require context switching in SQL Server2000, you have but one choice: SETUSER. Execution of this statement with aprovided username temporarily sets the execution permissions to that of thesupplied account. Multiple calls can be made and the context will continue toswitch until reverted back to the original context by calling SETUSER without ausername. Permissions
Contrary to Books Online, an account wishing to call SETUSERmust have the sysadmin server role. Books Online mistakenly states users withdbo rights can also utilize this statement, however this does not work. Examples
The following example shows context being switched from thelogged in account, adminacct in this case, to a specific SQL login, to anotherSQL user, and back again. Notice how the context reverts to the logged inaccount with the first execution of SETUSER without a username.
Results:
Its important to note that in the above example jdoe musthave the sysadmin role for the SETUSER jschmoe command to completesuccessfully. If jdoe does not have sysadmin, the following error is thrown.
SETUSER has an optional directive WITH NORESET, which can beused with the above example to cause the impersonation to not revert to the sysadminthat kicked it off. So again, logged in as adminacct, we kick off thefollowing.
Results:
The results show that the context did not revert to theoriginal account, but did revert to the previous context jdoe even though thataccount also has sysadmin. To revert the context all the way back to the adminacct,the session will have to be closed and a new one opened. EXECUTE AS
Starting with SQL Server 2005, the EXECUTE AS statement hastaken the place of SETUSER for context switching. SETUSER is deprecated andshould not be used in SQL Server 2005 or later.
There are two incarnations of EXECUTE AS, EXECUTE AS LOGINand EXECUTE AS USER. The former allows the calling account to take on theserver level permissions of the login such as securityadmin. The latter,provides access to the database level permissions of the passed in username. Toend the impersonation, the REVERT statement can be issued or the sessionterminated. EXECUTE AS LOGIN
Should you have a particular need to impersonate a login andrequire its server level permissions, you will need to use the EXECUTE ASLOGIN. In the following example, assume we are logged in as adminacct, whichhas the sysadmin role. We have a list of requests for new logins to be createdin one of the companys many databases. Because of some auditing procedures wehave in place, we need to use a particular login, securacct, which has the securityadminserver role. Rather than hunting down the password and logging in as thisaccount, we can just execute our stored procedure to create these logins as securacct. EXECUTE AS USER
If your needs are contained to the permissions at thedatabase level, EXECUTE AS USER will do just fine. An example of this is asystem account that logs into the database, but needs to impersonate the userthat actually kicked of the transaction in the front-end application. Thecontext of the transaction can be switched to the executing users username. In this case, the system account need only have dbo rights in the database.
Say the system account thats logging in on behalf of theuser is MyDomainAppAcct. The SQL username of the user that initiated thetransaction is jdoe. The system account logs into the SQL Server database andissues the following:
Issuing the REVERT statement at the end of the transactionallows the context to switch back to the system account in this case. In thecase of connection pooling, this would ensure that the session opened by thesystem account is consistently impersonating the application user and thenswitching back to itself for the user whose transaction is next in line forthat session.
As with SETUSER, it is possible to constrain theimpersonation to prevent it from returning the context to the originalaccount. To do this with EXECUTE AS, add WITH NO REVERT to the end of thestatement as below. The REVERT at the end has no effect on the context. Permissions
Whereas SETUSER required sysadmin permissions, EXECUTE ASUSER requires just dbo permissions to execute the statement. EXECUTE AS LOGINrequires the sysadmin server role since the permissions you wish to impersonateare at the server level.
An alternative to granting these roles to UserA who has alegitimate reason to impersonate UserB is to have UserB explicitly grantimpersonation rights to UserA.
Now UserA can use EXECUTE AS USER = UserB before hisstatements without being granted the dbo database role. Similarly, this can bedone for a login if server level permissions are needed. In this example,LoginD is going on vacation and temporarily needs to grant LoginC his abilitiesas a securityadmin without divulging his password. When he returns fromvacation, he can simply execute a REVOKE to remove the impersonate permission. Impersonation across Linked Servers
Impersonation can get a little tricky when the task at handcrosses SQL Server linked servers. If impersonation is being handled usingSETUSER, a call that crosses linked servers will fail. The context needs to bereverted to the original user prior to crossing servers.
With the introduction of EXECUTE AS, crossing link serversas an impersonated user or login becomes a possibility. To make this happen,the principle (userlogin) needs to be recognized on the linked server. Additionally, the linked servers need to be trustworthy. To make the databasestrustworthy, you will need to set their TRUSTWORTHY property to TRUE by issuingan ALTER DATABASE command such as this: Conclusion
When working in SQL Server 2000, SETUSER is your option forimplementing impersonation (or context switching). Be aware of the elevatedrights needed by the account executing SETUSER and the security risk involved. Its also important to note the limitation on linked server usage.
SQL Server 2005 and above offer a better context switchingmethod in EXECUTE AS. Understanding the requirements for impersonation willhelp determine if the impersonation is at the server level (the login) or thedatabase level (the user). If the impersonation is one off, consider the useof GRANT IMPERSONATE instead of elevating privileges for the logged in account. For More Information
The following links can provide additional information forthe concepts covered here:
EXECUTE AS - http://msdn.microsoft.com/en-us/library/ms181362.aspx
REVERT - http://msdn.microsoft.com/en-us/library/ms178632.aspx
SETUSER - http://msdn.microsoft.com/en-us/library/ms186297.aspx
TRUSTWORTHY - http://msdn.microsoft.com/en-us/library/ms187861.aspx
See All Articles by Columnist Deanna Dicken
Latest Forum Threads MS SQL Forum Topic By Replies Updated SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM Need help changing table contents nkawtg 1 August 17th, 03:02 AM SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM Sql Server 2000 Registry Keys Sql Server 2000 X64
This is the first book to outline the capabilities of SQL Server 2000, one of the key components of.NET. SQL Server 2000 introduces powerful new data mining functionality designed specifically to capture and process customer profiles and to predict future buying patterns on e-commerce sites. In this article. Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance You can define a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. Creating a primary key automatically creates a corresponding unique clustered index, or a nonclustered index if specified as such.
By Deanna Dicken
SQL Server impersonation, or context switching, is a means to allow the executing user to assume the permissions of a given user or login until the context is set back, set to yet another user, or the session is ended. Deanna Dicken shows you two mechanisms for accomplishing this task and walks through some examples. Introduction
SQL Server impersonation, or context switching, is a meansto allow the executing user to assume the permissions of a given user or loginuntil the context is set back, set to yet another user, or the session isended. In the following sections we will discuss two mechanisms foraccomplishing this task and walk through some examples. SETUSER
If you have cause to require context switching in SQL Server2000, you have but one choice: SETUSER. Execution of this statement with aprovided username temporarily sets the execution permissions to that of thesupplied account. Multiple calls can be made and the context will continue toswitch until reverted back to the original context by calling SETUSER without ausername. Permissions
Contrary to Books Online, an account wishing to call SETUSERmust have the sysadmin server role. Books Online mistakenly states users withdbo rights can also utilize this statement, however this does not work. Examples
The following example shows context being switched from thelogged in account, adminacct in this case, to a specific SQL login, to anotherSQL user, and back again. Notice how the context reverts to the logged inaccount with the first execution of SETUSER without a username.
Results:
Its important to note that in the above example jdoe musthave the sysadmin role for the SETUSER jschmoe command to completesuccessfully. If jdoe does not have sysadmin, the following error is thrown.
SETUSER has an optional directive WITH NORESET, which can beused with the above example to cause the impersonation to not revert to the sysadminthat kicked it off. So again, logged in as adminacct, we kick off thefollowing.
Results:
The results show that the context did not revert to theoriginal account, but did revert to the previous context jdoe even though thataccount also has sysadmin. To revert the context all the way back to the adminacct,the session will have to be closed and a new one opened. EXECUTE AS
Starting with SQL Server 2005, the EXECUTE AS statement hastaken the place of SETUSER for context switching. SETUSER is deprecated andshould not be used in SQL Server 2005 or later.
There are two incarnations of EXECUTE AS, EXECUTE AS LOGINand EXECUTE AS USER. The former allows the calling account to take on theserver level permissions of the login such as securityadmin. The latter,provides access to the database level permissions of the passed in username. Toend the impersonation, the REVERT statement can be issued or the sessionterminated. EXECUTE AS LOGIN
Should you have a particular need to impersonate a login andrequire its server level permissions, you will need to use the EXECUTE ASLOGIN. In the following example, assume we are logged in as adminacct, whichhas the sysadmin role. We have a list of requests for new logins to be createdin one of the companys many databases. Because of some auditing procedures wehave in place, we need to use a particular login, securacct, which has the securityadminserver role. Rather than hunting down the password and logging in as thisaccount, we can just execute our stored procedure to create these logins as securacct. EXECUTE AS USER
If your needs are contained to the permissions at thedatabase level, EXECUTE AS USER will do just fine. An example of this is asystem account that logs into the database, but needs to impersonate the userthat actually kicked of the transaction in the front-end application. Thecontext of the transaction can be switched to the executing users username. In this case, the system account need only have dbo rights in the database.
Say the system account thats logging in on behalf of theuser is MyDomainAppAcct. The SQL username of the user that initiated thetransaction is jdoe. The system account logs into the SQL Server database andissues the following:
Issuing the REVERT statement at the end of the transactionallows the context to switch back to the system account in this case. In thecase of connection pooling, this would ensure that the session opened by thesystem account is consistently impersonating the application user and thenswitching back to itself for the user whose transaction is next in line forthat session.
As with SETUSER, it is possible to constrain theimpersonation to prevent it from returning the context to the originalaccount. To do this with EXECUTE AS, add WITH NO REVERT to the end of thestatement as below. The REVERT at the end has no effect on the context. Permissions
Whereas SETUSER required sysadmin permissions, EXECUTE ASUSER requires just dbo permissions to execute the statement. EXECUTE AS LOGINrequires the sysadmin server role since the permissions you wish to impersonateare at the server level.
An alternative to granting these roles to UserA who has alegitimate reason to impersonate UserB is to have UserB explicitly grantimpersonation rights to UserA.
Now UserA can use EXECUTE AS USER = UserB before hisstatements without being granted the dbo database role. Similarly, this can bedone for a login if server level permissions are needed. In this example,LoginD is going on vacation and temporarily needs to grant LoginC his abilitiesas a securityadmin without divulging his password. When he returns fromvacation, he can simply execute a REVOKE to remove the impersonate permission. Impersonation across Linked Servers
Impersonation can get a little tricky when the task at handcrosses SQL Server linked servers. If impersonation is being handled usingSETUSER, a call that crosses linked servers will fail. The context needs to bereverted to the original user prior to crossing servers.
With the introduction of EXECUTE AS, crossing link serversas an impersonated user or login becomes a possibility. To make this happen,the principle (userlogin) needs to be recognized on the linked server. Additionally, the linked servers need to be trustworthy. To make the databasestrustworthy, you will need to set their TRUSTWORTHY property to TRUE by issuingan ALTER DATABASE command such as this: Conclusion
When working in SQL Server 2000, SETUSER is your option forimplementing impersonation (or context switching). Be aware of the elevatedrights needed by the account executing SETUSER and the security risk involved. Its also important to note the limitation on linked server usage.
SQL Server 2005 and above offer a better context switchingmethod in EXECUTE AS. Understanding the requirements for impersonation willhelp determine if the impersonation is at the server level (the login) or thedatabase level (the user). If the impersonation is one off, consider the useof GRANT IMPERSONATE instead of elevating privileges for the logged in account. For More Information
The following links can provide additional information forthe concepts covered here:
EXECUTE AS - http://msdn.microsoft.com/en-us/library/ms181362.aspx
REVERT - http://msdn.microsoft.com/en-us/library/ms178632.aspx
SETUSER - http://msdn.microsoft.com/en-us/library/ms186297.aspx
TRUSTWORTHY - http://msdn.microsoft.com/en-us/library/ms187861.aspx
See All Articles by Columnist Deanna Dicken
Latest Forum Threads MS SQL Forum Topic By Replies Updated SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM Need help changing table contents nkawtg 1 August 17th, 03:02 AM SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM Sql Server 2000 Registry Keys Sql Server 2000 X64
