SQL Server/T-SQL Tutorial/User Role/USER

Change the default schema for user Fred

   <source lang="sql">

ALTER USER Fred WITH DEFAULT_SCHEMA = Production</source>

Create a proxy user

   <source lang="sql">

6> CREATE USER Bob 7> WITHOUT LOGIN 8> GO 1> 2></source>

Create a user and a table, and make the user own the table

   <source lang="sql">

4> CREATE USER Tom 5> WITHOUT LOGIN 6> GO 1> CREATE TABLE TomsData 2> ( 3> AColumn INT 4> ) 5> GO 1> 2> ALTER AUTHORIZATION ON TomsData TO Tom 3> GO 1> 2> 3> --Impersonate the user 4> EXECUTE AS USER="Tom" 5> GO 1> 2> SELECT USER_NAME() 3> GO

---

Tom (1 rows affected) 1> 2> drop table TomsData; 3> drop user tom; 4> GO Msg 15151, Level 16, State 1, Server J\SQLEXPRESS, Line 3 Cannot drop the user "tom", because it does not exist or you do not have permission. 1></source>

Creating Database Users with schema

   <source lang="sql">

USE YourDatabaseName GO CREATE USER Joe FOR LOGIN [YourID\TestUser] WITH DEFAULT_SCHEMA = HumanResources</source>

Modifying a Database User for name

   <source lang="sql">

ALTER USER YourName WITH NAME = YourNewName</source>

Modifying a Database User for the schema

   <source lang="sql">

USE YourDatabaseName GO ALTER USER Joe WITH DEFAULT_SCHEMA = Production</source>

Removing a Database User from the Database

   <source lang="sql">

DROP USER UserName</source>

select USER

   <source lang="sql">

10> SELECT CURRENT_TIMESTAMP, USER, SYSTEM_USER, CURRENT_USER, 11> SESSION_USER 12> GO

---

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

---

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

---

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

2008-08-17 13:20:57.503 dbo J\Administrator

                                                                                 dbo
          dbo

(1 rows affected)</source>