- Sql Server 2000 Create Table Primary Key
- Sql Server 2000 Registry Keys
- Sys.foreign_keys Sql Server 2000
Posted Feb 12, 2010
The script works with any SQL Server edition & version starting from 2005: SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. Pay close attention, though, if you’re using Sql Server 2012 or Sql Server 2014 you’ll have to make some small modifications to that code. For Sql Server 2012 you need to replace two lines of code. I know of no hacks to get SQL Server 2000 to install on an existing Windows 10 installation. There are probably forces against you from both within the setup program (which looks for certain things on the host system) and within the operating system (which prevents many flavors of older software from getting installed, mostly for security reasons but also compatibility, stability, etc).
By Deanna Dicken
Sql Server 2000 Create Table Primary Key
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:
It’s 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:
Sql Server 2000 Registry Keys
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 company’s 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 user’s username. In this case, the system account need only have dbo rights in the database.
Say the system account that’s 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
Sys.foreign_keys Sql Server 2000
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. It’s 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 ColumnistDeanna 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 |