sys.database_principals (Transact-SQL) 从SQL SERVER数据库中查询安全主体
来源:互联网 发布:820空心杯淘宝 编辑:程序博客网 时间:2024/06/05 06:09
default_schema_name为主体的默认schema,默认架构
THIS TOPIC APPLIES TO: SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse
Returns a row for each security principal in a SQL Server database.
A = Application role
C = User mapped to a certificate
E = External user from Azure Active Directory
G = Windows group
K = User mapped to an asymmetric key
R = Database role
S = SQL user
U = Windows user
X = External group from Azure Active Directory group or applicationstype_descnvarchar(60)Description of principal type.
APPLICATION_ROLE
CERTIFICATE_MAPPED_USER
EXTERNAL_USER
WINDOWS_GROUP
ASYMMETRIC_KEY_MAPPED_USER
DATABASE_ROLE
SQL_USER
WINDOWS_USER
EXTERNAL_GROUPSdefault_schema_namesysnameName to be used when SQL name does not specify a schema. Null for principals not of type S, U, or A.create_datedatetimeTime at which the principal was created.modify_datedatetimeTime at which the principal was last modified.owning_principal_idintID of the principal that owns this principal. All principals except Database Roles must be owned by dbo.sidvarbinary(85)SID (Security Identifier) of the principal. NULL for SYS and INFORMATION SCHEMAS.is_fixed_rolebitIf 1, this row represents an entry for one of the fixed database roles: db_owner, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader, db_denydatawriter.authentication_typeintApplies to: SQL Server 2012 through SQL Server 2016.
Signifies authentication type. The following are the possible values and their descriptions.
0 : No authentication
1 : Instance authentication
2 : Database authentication
3 : Windows Authenticationauthentication_type_descnvarchar(60)Applies to: SQL Server 2012 through SQL Server 2016.
Description of the authentication type. The following are the possible values and their descriptions.
NONE : No authentication
INSTANCE : Instance authentication
DATABASE : Database authentication
WINDOWS : Windows Authenticationdefault_language_namesysnameApplies to: SQL Server 2012 through SQL Server 2016.
Signifies the default language for this principal.default_language_lcidintApplies to: SQL Server 2012 through SQL Server 2016.
Signifies the default LCID for this principal.
Remarks
The PasswordLastSetTime properties are available on all supported configurations of SQL Server, but the other properties are only available when SQL Server is running on Windows Server 2003 or later and both CHECK_POLICY and CHECK_EXPIRATION are enabled. See Password Policy for more information.
Permissions
Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.
Examples
A: Listing all the permissions of database principals
The following query lists the permissions explicitly granted or denied to database principals.
The permissions of fixed database roles do not appear in sys.database_permissions. Therefore, database principals may have additional permissions not listed here.
SELECT pr.principal_id, pr.name, pr.type_desc, pr.authentication_type_desc, pe.state_desc, pe.permission_name FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id;
B: Listing permissions on schema objects within a database
The following query joins sys.database_principals and sys.database_permissions to sys.objects and sys.schemas to list permissions granted or denied to specific schema objects.
SELECT pr.principal_id, pr.name, pr.type_desc, pr.authentication_type_desc, pe.state_desc, pe.permission_name, s.name + '.' + o.name AS ObjectName FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id JOIN sys.objects AS o ON pe.major_id = o.object_id JOIN sys.schemas AS s ON o.schema_id = s.schema_id;
Examples: Azure SQL Data Warehouse and Parallel Data Warehouse
C: Listing all the permissions of database principals
The following query lists the permissions explicitly granted or denied to database principals.
The permissions of fixed database roles do not appear in sys.database_permissions
. Therefore, database principals may have additional permissions not listed here.
SELECT pr.principal_id, pr.name, pr.type_desc, pr.authentication_type_desc, pe.state_desc, pe.permission_name FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id;
D: Listing permissions on schema objects within a database
The following query joins sys.database_principals
and sys.database_permissions
to sys.objects
and sys.schemas
to list permissions granted or denied to specific schema objects.
SELECT pr.principal_id, pr.name, pr.type_desc, pr.authentication_type_desc, pe.state_desc, pe.permission_name, s.name + '.' + o.name AS ObjectName FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id JOIN sys.objects AS o ON pe.major_id = o.object_id JOIN sys.schemas AS s ON o.schema_id = s.schema_id;
- sys.database_principals (Transact-SQL) 从SQL SERVER数据库中查询安全主体
- sys.syslogins (Transact-SQL)
- sys.sysobjects (Transact-SQL)
- sys.sysobjects (Transact-SQL)
- sys.sysobjects (Transact-SQL)
- SQL Server Transact-SQL高级查询
- SQL Server Transact-SQL高级查询
- sql server ROW_NUMBER (Transact-SQL) 分组查询
- 【SQL Server学习笔记】Windows主体、SQL Server主体、数据库主体
- 从IIS到SQL Server数据库安全
- 从IIS到SQL Server数据库安全
- 从IIS到SQL Server数据库安全
- SQL Server 数据库安全
- SQL Server 数据库安全
- SQL Server 数据库安全
- SQL Server 数据库安全
- SQL Server 数据库安全
- SQL Server 数据库安全
- PHP empty、isset、isnull的区别
- ZooKeeper的数据结构模型和特点
- JSON文本转化成javascript对象
- Anroid——第三方数据库SQLite——SQLiteOpenHelper+SQLiteDatabase
- Java利用Zxing生成二维码
- sys.database_principals (Transact-SQL) 从SQL SERVER数据库中查询安全主体
- view的setTag() 和 getTag()应用
- Java系统中时间封装处理
- Sublime Text 格式化代码
- ZooKeeper客户端Curator使用一 创建连接
- SpringMVC:Maven构建SpringMVC简单配置(JavaConfig版)
- 图结构练习——BFSDFS——判断可达性
- 第二章 构造函数语意学
- php token命令的简单实现,可用于网站平台的对接