SQL Server/T-SQL Tutorial/User Role/USER — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 13:23, 26 мая 2010
Содержание
- 1 Change the default schema for user Fred
- 2 Create a proxy user
- 3 Create a user and a table, and make the user own the table
- 4 Creating Database Users with schema
- 5 Modifying a Database User for name
- 6 Modifying a Database User for the schema
- 7 Removing a Database User from the Database
- 8 select 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>