Allows a database owner to impersonate another user. The SETUSER statement is used by the system administrator or a database owner when he or she wants to adopt the identity of another user in order to use another user's database object, to grant permissions to that object, to create an object, and so on.
SETUSER ['username' [WITH NORESET]]
where
Using SETUSER to impersonate a user, a database owner, or system administrator will have only those rights given to the user being impersonated. Because the owner of an object always retains the rights to that object, no users, other than those given explicit permission to that object, can access it. Because a database owner and the system administrator always have supervisory permission over their database (or the system) they can temporarily adopt another database user's identity. The database owner or system administrator can act as that user, create objects in that user's name, and test permissions as given to that user. The SETUSER statement remains in effect until another SETUSER statement is issued or until the current database is changed with the USE statement.
In SQL Server 6.0, the SETUSER statement does not need to be used to drop objects owned by another user. The database owner and system administrator can drop user's objects by explicitly qualifying the object name with the owner's name. For details, see the DROP statements (DROP DEFAULT, DROP PROCEDURE, DROP TABLE, and so on).
In earlier releases of SQL Server, the system administrator retained full system administrator permissions and could not use SETUSER to acquire another user's permissions. If you want to maintain compatibility with this previous behavior, use trace flag 206. For details, see the Trace Flags topic.
Execute permission defaults to the database owner and is not transferable.
In this example, Mary (the database username of mary) has created a table called computer_types. Mary happens to be out of town and another user (Joe) needs to access Mary's computer_types table. The database owner or system administrator can execute:
SETUSER 'mary' go GRANT SELECT ON computer_types TO joe go SETUSER
In this example, the database owner needs to create some objects and then test their usability with minimal permissions. For simplicity, the database owner wants to maintain only Mary's permission for the entire session. The database owner or system administrator can execute:
SETUSER 'mary' WITH NORESET go CREATE TABLE computer_types2 . . . GRANT ... go SETUSER /* This statement will have no effect. */
The only way the database owner or system administrator can re-establish his or her own rights is to log off and then log on again.
GRANT | USE |
REVOKE |