SQL 登入名,用户管理

----- 1.管理登入名  
-- 创建登入名  
create login test with password = '123456'   
exec sp_addlogin 'test' , '123456'   --系统过程  
  
-- 删除登入名  
drop login test   
  
-- 注 登入名必须关联一个数据库才能登入(sql 2008)  

-- 授予服务器角色
sp_addsrvrolemember  'teacher','sysadmin' 
sp_dropsrvrolemember 'teacher','sysadmin' 
  
  
----- 2.管理用户名  
--创建一个用户  
use datebasename  
go  
create user test for login test with default_schema = --dbo  
  
--更改用户关联的登入名  
use datebasename  
exec sp_change_users_login 'update_one','test','test'  
  
-- 更改用户名设置  
ALTER USER userName      
     WITH <set_item> [ ,...n ]    
[;]    
<set_item> ::=     
      NAME = newUserName     
    | DEFAULT_SCHEMA = { schemaName | NULL } -- 架构    
    | LOGIN = loginName    
    | PASSWORD = 'password' [ OLD_PASSWORD = 'oldpassword' ]    
    | DEFAULT_LANGUAGE = { NONE | <lcid> | <language name> | <language alias> }    
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]    
      
---例如:  
alter user test with login  = sa  
alter user test with name = test1  
  
-- 更改用户名的所属框架  
alter user test with default_schema =  db_datareader  
  
--授予角色  
exec sp_addrolemember 'db_owner', 'test'  
exec sp_droprolemember 'db_owner', 'test'  
  
--授予 , 撤销, 禁止 权力 对于表的
grant select, delete, update  on dbo.OrderCenter to teacher --( with grant option ) 
revoke update on dbo.Study to student cascade

--授予创建表的权力
grant create view  to student --with grant option 
grant create table  to student --with grant option 

-- 删除用户名  
drop user test