--創建登陸用戶
        

        --
        
          create
        
         login login_name 
        
          from
        
         windows 
        
          with
        
         default_database = 
        
          database
        
         | default_language = 
        
          language
        
        ;
        

        
          create
        
         login [localhost\hoojo-pc] 
        
          from
        
         windows 
        
          with
        
         default_database = testHome;
        

        --創建登陸用戶
        

        --
        
          create
        
         login login_name 
        
          with
        
         password = 
        
          'pwd'
        
         [hashed] [must_change] [check_expiration] [check_policy]
        

        
          create
        
         login 
        
          admin
        
        
          with
        
         password = 
        
          '123456'
        
        , default_database = testHome;
        

        

        --查看登陸用戶
        

        
          select
        
         * 
        
          from
        
         sys.sql_logins;
        

        

        --修改用戶
        

        
          alter
        
         login 
        
          admin
        
        
          with
        
         name = jackson;
        

        
          alter
        
         login jackson 
        
          with
        
         password = 
        
          'abcd'
        
        ;
        

        

        --禁用、啟用用戶
        

        
          alter
        
         login jackson disable;    --禁用
        

        
          alter
        
         login jackson enable;        --啟用
        

        

        --刪除用戶
        

        
          drop
        
         login jackson;
        

        

        --修改映射憑據(將登錄名MacraeS 映射到憑據Custodian04)
        

        
          alter
        
         login jackson 
        
          with
        
         credential = Custodian04;
        

        

        -------數據庫用戶
        

        --創建數據庫用戶
        

        
          create
        
        
          user
        
         jack 
        
          from
        
         login 
        
          admin
        
        ;
        

        
          create
        
        
          user
        
         jason 
        
          for
        
         login jackson;
        

        

        
          create
        
        
          user
        
         jack 
        
          from
        
         login 
        
          admin
        
        
          with
        
         default_schema = temp_schema;
        

        

        --啟動guest用戶（不推薦使用）
        

        --特殊用戶dbo、guest；sa對應的dbo用戶，其他沒有映射的用戶就是guest用戶
        

        
          grant
        
        
          connect
        
        
          to
        
         guest;
        

        

        --修改用戶
        

        
          alter
        
        
          user
        
         jack 
        
          with
        
         name = jason;
        

        
          alter
        
        
          user
        
         jason 
        
          with
        
         default_schema = jason_schema;
        

        

        --刪除用戶
        

        
          drop
        
        
          user
        
         jason;
        

        

        --------數據庫角色
        

        --給jack用戶授權buyers角色
        

        
          create
        
        
          role
        
         buyers 
        
          authorIzation
        
         jack;
        

        

        --修改角色
        

        
          alter
        
        
          role
        
         buyers 
        
          with
        
         name = new_buyers;
        

        

        --刪除角色
        

        
          drop
        
        
          role
        
         new_buyers;
        

        

        --------架構管理
        

        --創建
        

        
          create
        
        
          schema
        
         temp_schema;
        

        

        --指定用戶
        

        
          create
        
        
          schema
        
         jason_scheam 
        
          authorization
        
         jason;
        

        

        --授權查詢
        

        
          grant
        
        
          select
        
        
          to
        
         jason;
        

        

        --刪除
        

        
          drop
        
        
          schema
        
         jason_scheam;
        

        

        --------權限
        

        --授權創建table
        

        
          grant
        
        
          create
        
        
          table
        
        
          to
        
         jason;
        

        --授權jason查詢student表
        

        
          grant
        
        
          select
        
        
          on
        
         student 
        
          to
        
         jason;
        

        

        --收回權限
        

        
          revoke
        
        
          create
        
        
          table
        
        
          to
        
         jason;
        

        
          revoke
        
        
          select
        
        
          on
        
         student 
        
          to
        
         jason;