Presents your 
SQL SERVER E-NEWSLETTER for June 18, 2002

--------------------------------------------

Impersonate Database users

The SETUSER T-SQL command is a great way to impersonate a user on a
database in order to test security. However, Microsoft warns against using
this command because, although it's currently still available (due to
backward compatibility), it may not be present in future releases.

When executing the SETUSER command with a database user argument, you
are impersonating that user until the point at which you change databases
or execute the SETUSER command without any arguments.

The optional argument "WITH NORESET" does not allow the previous user to
resume. If you run the SETUSER command with the "WITH NORESET" argument,
you'll receive an error when executing SETUSER without any arguments;
the error will state that permission is denied on the SETUSER command.

The following script demonstrates the inability to resume the previous
user after executing SETUSER with the "WITH NORESET" argument:

USE pubs
GO
SELECT SUSER_SNAME(), CURRENT_USER
GO
SETUSER 'guest' WITH NORESET
GO
SELECT SUSER_SNAME(), CURRENT_USER
GO
SETUSER
GO

The command doesn't work for Windows users; however, if you grant SQL
Server login for a domain user and then grant database access with a
database user name, you can impersonate the database user name. In other words,
if you map an NT user to a SQL server database user, you can impersonate
the database user.

The script below demonstrates how to impersonate a Windows user through
the database user. Before running this script, you will need to
substitute the tags for the domain name and the Windows user name.

USE pubs
GO
SELECT SUSER_SNAME(), CURRENT_USER
GO
EXEC sp_grantlogin '<domain name> \ <user name>'
GO
EXEC sp_grantdbaccess '<domain name> \ <user name>', 'ExampleUser'
GO
DENY SELECT ON authors TO public
GO
SETUSER 'ExampleUser'
GO
SELECT SUSER_SNAME(), CURRENT_USER
GO
SELECT * FROM authors
GO
SETUSER
GO
SELECT SUSER_SNAME(), CURRENT_USER
GO
SELECT * FROM authors
GO
EXEC sp_revokedbaccess @name_in_db = 'ExampleUser'
GO
EXEC sp_revokelogin '<domain name> \ <user name>'
GO

----------------------------------------