• Sql Server 2000 X64
• Microsoft Sql Server 2000
• Sql Server 2000 Sp4 Download
• Sql Server 2000 Sp3
• Sql Server 2000 Sp4
• Sql Server 2000 Serial Key

Posted Feb 12, 2010

By Deanna Dicken

Select. from sysobjects where xtype = 'F' That should do the trick and be compatible with SQL Server 2000, I hope! If you additionally need the table and column information in SQL Server 2000, it gets a bit more involved; you need to join the sysforeignkeys and syscolumns catalog views like so. Select so.name 'foreign key name', OBJECTNAME(parentobj) 'table', OBJECTNAME(sf.fkeyid. This SQL Server 2000 specific sheet was extracted from the orginal (all versions) 'SQL Server Data Types Reference' article.Please see the full original article for more details on why data types knowledge is important.

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.

PRIMARY KEY in sql server 2000.avi Code Infosys. Unsubscribe from Code Infosys? SQL Server 2012 - 05 - Primary Key Llave primaria - Tutoriales y mas - Duration: 4:14. This article discusses how to get or find Primary Key or ID column of a table in SQL Server 2000/2005. Sppkeys: This is a built-in stored procedure. This stored procedure requires only one parameter of table name. The result of that stored procedure is.

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:

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.

Sql Server 2000 X64

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.

Microsoft Sql Server 2000

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.

Sql Server 2000 Sp4 Download

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.

Sql Server 2000 Sp3

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

Sql Server 2000 Sp4

Sql Server 2000 Serial Key