【SQL Server学习笔记】Windows主体、SQL Server主体、数据库主体

主体就是可以被授予权限，访问特定数据库对象的对象。比如，用户登录，角色，应用程序。

安全对象是可以访问的受到控制的对象。比如表、视图、存储过程、函数、触发器。

权限是独立的，可以被授予给主体，以访问安全对象，或者未被授予，以阻止访问安全对象。

 

主体可以分为3个不同的范围：

1、Windows级别的主体：基于Windows域用户账号、域用户组、本地用户组的主体。当添加到SQL Server中，并且给予访问对象的权限，那么这类主体将基于Windows身份验证，来获得访问SQL Server所需要的权限。

 

Windows级别的主体

Windows级别的主体通过Windows身份验证，访问SQL Server实例。

在SQL Server中可以创建基于Windows用户账号、用户组的Windows登录名，它可以属于本地机器，或者属于一个域(Windows登录名可以和域用户、本地用户、用户组进行关联)。当添加一个Windows登录名到SQL Server时，这个windows登录名可以对应Windows中的用户名，也可以对应一个Windows用户组。

添加到SQL Server的windwos登录名不需要独立的密码，就可以登录，登录身份验证的过程是由windows来控制的。

默认情况下SQL SERVER只允许Administrators组的成员可以连接到数据库，也就是不能用Administrators组来创建windows登录名。 

CREATE LOGIN loginName  FROM <sources> <sources> ::=    WINDOWS [ WITH <windows_options> [ ,... ] ]    | CERTIFICATE certname    | ASYMMETRIC KEY asym_key_name<windows_options> ::=          DEFAULT_DATABASE = database    | DEFAULT_LANGUAGE = language

 

--1.1以windows用户创建登录名create login [PC0627JVC\ggg]     --机器名称\用户名from windowswith default_database = wc,      --默认数据库     default_language = 简体中文  --登录名的默认语言--1.2以windows用户组创建登录名create login [PC0627JVC\ora_dba] --机器名称\用户组名from windowswith default_database = wc,      --默认数据库     default_language = 简体中文  --登录名的默认语言--2.查看windows登录名select s.name,       s.principal_id,       s.sid,       s.type_desc,       s.is_disabledfrom sys.server_principals swhere s.type_desc in ('WINDOWS_LOGIN',                      'WINDOWS_GROUP')--3.1改变默认语言,默认数据库alter login [PC0627JVC\ggg]with default_database = wcc,     default_language = english--3.2禁用或启用登录名alter login [PC0627JVC\ggg]disablealter login [PC0627JVC\ggg]enable--4.拒绝widnows登录名访问SQL Server--如果用ggg这个账号登陆windows后,就登不上SQL Server了deny connect sql to [PC0627JVC\ggg]--5.删除windows登录名drop login [PC0627JVC\ora_dba]drop login [PC0627JVC\ggg]

2、SQL Server级别的主体：是SQL Server级别的登录名和固定的服务器角色。在SQL Server中创建的SQL登录名，拥有独立于任何Windows实体的登录名和密码。固定的服务器角色是SQL Server实例级别权限的分组，其他主体能成为它的成员，而且继承了服务器角色的权限，另外，固定服务器角色之所以是固定的，是因为不能添加服务器角色。  

CREATE LOGIN loginName  WITH <option_list1>   <option_list1> ::=     PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]    [ , <option_list2> [ ,... ] ]<option_list2> ::=      SID = sid    | DEFAULT_DATABASE = database        | DEFAULT_LANGUAGE = language    | CHECK_EXPIRATION = { ON | OFF}    | CHECK_POLICY = { ON | OFF}    | CREDENTIAL = credential_name 

 

--1.1创建sql登录名create login gggwith password = '123',     default_database = wc     --1.2创建登录名，同时设置密码策略create login ygwith password = '123' must_change,  --在第一次登录时会提示修改密码          check_expiration = on,   --对sql登录名强制实施密码过期策略     check_policy = on        --把Windows密码策略应用到sql登录名上     --1.3查看sql登录名select name,       principal_id,       sid,       type_desc,       is_disabledfrom sys.server_principals where type_desc in ('SQL_LOGIN')--2.1修改登录名的密码alter login gggwith password = '123456'     old_password = '123'--2.2同时修改登录名和密码alter login gggwith name = www,     password = '12345678'--2.2修改登录名的默认数据库alter login wwwwith default_database = master--3.1管理登录名的密码select LOGINPROPERTY('www','IsLocked'),       LOGINPROPERTY('www','IsExpired'),       LOGINPROPERTY('www','IsMustChange'),       LOGINPROPERTY('www','BadPasswordCount'),       LOGINPROPERTY('www','BadPasswordTime'),       LOGINPROPERTY('www','HistoryLength'),       LOGINPROPERTY('www','LockoutTime'),       LOGINPROPERTY('www','PasswordLastSetTime'),       LOGINPROPERTY('www','PasswordHash')--3.2.删除sql登录名drop login www--4服务器角色成员--4.1查看固定服务器角色select name,       principal_id,       sid,       type_desc,       is_disabledfrom sys.server_principals where type_desc = 'SERVER_ROLE'--显示固定服务器角色exec sp_helpsrvrole--4.2把登录名添加到服务器角色exec sp_addsrvrolemember @loginame = 'ggg',@rolename = 'sysadmin'--4.3删除服务器角色成员,这是就算ggg已经登录到sql server,也会导致ggg无法继续操作exec sp_dropsrvrolemember @loginame = 'ggg',@rolename = 'sysadmin'--4.4显示固定服务器角色的成员select p.name,          --固定服务器角色       p.principal_id,       p.sid,              pp.name,         --成员       pp.principal_id,       pp.sidfrom sys.server_principals pinner join sys.server_role_members r        on p.principal_id = r.role_principal_idinner join sys.server_principals pp        on pp.principal_id = r.member_principal_id        where p.name = 'sysadmin'--显示服务器角色的成员exec sp_helpsrvrolemember@srvrolename = 'sysadmin'        

3、数据库级别的主体：数据库用户、数据库角色(固定的、用户定义的)、应用程序角色。可以添加数据库角色和应用程序角色。

 需要注意的是，应用程序角色是不包含成员的成组的权限，能够以应用程序角色的身份登录。当使用一个应用程序角色时，它会覆盖登录名的其他所有权限，只授予你应用程序角色的那些权限。 

CREATE USER user_name     [ { { FOR | FROM }      {         LOGIN login_name         | CERTIFICATE cert_name         | ASYMMETRIC KEY asym_key_name      }       | WITHOUT LOGIN    ]     [ WITH DEFAULT_SCHEMA = schema_name ]

 

--1.1创建数据库用户create user gggfor login gggwith default_schema = ggg  --用户的默认架构，如果不存在，会自动创建--1.2显示数据库用户的信息exec sp_helpuser @name_in_db = 'ggg'select name,       principal_id,       type_desc,       default_schema_name,       sidfrom sys.database_principalswhere type_desc = 'SQL_USER'--1.3修改数据库用户alter user gggwith default_schema = dbo   --修改默认架构alter user gggwith name = wwwc            --修改数据库用户名--1.4删除数据库用户名drop user wwwc/*=====================================================1.5修复孤立的数据库用户登录名有一个安全标示符，能唯一标识出这个登录名。数据库迁移之后，重新创建登录名，而这个登录名会对应到不同的sid，也就是登录名和数据库用户名之间的对应关系可能会变化，才会导致孤立的数据库用户======================================================*/select DP.name,       DP.sid,       DP.principal_id,              SP.name,       SP.sidfrom sys.database_principals dpleft join sys.server_principals sp       on dp.sid = sp.sid where sp.sid is null      and dp.type_desc = 'SQL_USER'--修改用户，重新指定登录名，来更新数据库用户对应的sidALTER USER GGGWITH LOGIN = GGG--2.固定数据库角色--2.1显示固定数据库角色exec sp_helpdbfixedrole --@rolename = 'db_ddladmin'select name,       principal_id,       type_desc,       default_schema_name ,       sid,              is_fixed_role       from sys.database_principalswhere type_desc = 'DATABASE_ROLE'      and is_fixed_role = 1                  --2.2显示固定数据库角色的成员exec sp_helprolemember --@rolename = 'db_ddladmin'select dp.name,              dpp.name,       dpp.sidfrom sys.database_role_members rminner join sys.database_principals dp        on dp.principal_id = rm.role_principal_idinner join sys.database_principals dpp        on dpp.principal_id = rm.member_principal_id--2.3.管理固定数据库角色成员exec sp_addrolemember @rolename = 'db_datawriter',@membername = 'ggg'exec sp_droprolemember@rolename = 'db_datawriter',@membername = 'ggg'--3.用户定义的数据库角色--3.1创建create role wc_role authorization db_owner --3.2修改角色名称alter role wc_rolewith name = w_role--3.3给角色添加用户exec sp_addrolemember@rolename = 'w_role',@membername = 'ggg'--3.4从角色中删除用户exec sp_droprolemember@rolename = 'w_role',@membername = 'ggg'--3.5删除drop role wc_role--4.应用程序角色--4.1创建create application role www_cwith password = '123',     default_schema = dbo     --4.2修改alter application role www_cwith name = wwc,     password = '12345'--4.3授予权限grant select on dbo.wcObjects to wwc--4.4激活应用程序角色exec sp_setapprole @rolename = 'wwc',@password ='12345'--4.5查询数据select * from dbo.wcObjects--4.6拒绝了对对象 'book' (数据库 'wc'，架构 'dbo')的 SELECT 权限。select * from dbo.book--4.7删除应用程序角色drop application role wwc

 

查询当前连接的主机的域用户名

select *from sys.server_principalswhere type_desc in ('WINDOWS_LOGIN','WINDOWS_GROUP')and CHARINDEX('NT',name)=0