December 6, 2012

Security Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

sys.asymmetric_keys

asymmetric key.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Name of the key. Is unique within the database.
principal_id int ID of the database principal that owns the key.
asymmetric_key_id int ID of the key. Is unique within the database.
pvt_key_encryption_type char(2) How the key is encrypted.
NA = Not encrypted
MK = Key is encrypted by the master key
PW = Key is encrypted by a user-defined password
SK = Key is encrypted by service master key.
pvt_key_encryption_type_desc nvarchar(60) Description of how the private key is encrypted.
NO_PRIVATE_KEY
ENCRYPTED_BY_MASTER_KEY
ENCRYPTED_BY_PASSWORD
ENCRYPTED_BY_SERVICE_MASTER_KEY
thumbprint varbinary(32) SHA-1 hash of the key. The hash is globally unique.
algorithm char(2) Algorithm used with the key.
1R = 512-bit RSA
2R = 1024-bit RSA
3R = 2048-bit RSA
algorithm_desc nvarchar(60) Description of the algorithm used with the key.
RSA_512
RSA_1024
RSA_2048
key_length int Bit length of the key
sid varbinary(85) Login SID for this key
string_sid nvarchar(128) String representation of the login SID of the key
public_key varbinary(max) Public key
attested_by nvarchar(260) System use only.
provider_type   nvarchar(120) Type of cryptographic provider:
CRYPTOGRAPHIC PROVIDER = Extensible Key Management keys
NULL = Non-Extensible Key Management keys
cryptographic_provider_guid   uniqueidentifier GUID for the cryptographic provider. For non-Extensible Key Management keys this value will be NULL.
cryptographic_provider_algid   sql_variant Algorithm ID for the cryptographic provider. For non-Extensible Key Management keys this value will be NULL.

TSQL

Sql 2005
SELECT [name], [principal_id], [asymmetric_key_id], [pvt_key_encryption_type], [pvt_key_encryption_type_desc], [thumbprint], [algorithm], [algorithm_desc], [key_length], [sid], [string_sid], [public_key], [attested_by] FROM sys.asymmetric_keys
Sql 2008
SELECT [name], [principal_id], [asymmetric_key_id], [pvt_key_encryption_type], [pvt_key_encryption_type_desc], [thumbprint], [algorithm], [algorithm_desc], [key_length], [sid], [string_sid], [public_key], [attested_by], [provider_type], [cryptographic_provider_guid], [cryptographic_provider_algid] FROM sys.asymmetric_keys
Sql 2008 R2
SELECT [name], [principal_id], [asymmetric_key_id], [pvt_key_encryption_type], [pvt_key_encryption_type_desc], [thumbprint], [algorithm], [algorithm_desc], [key_length], [sid], [string_sid], [public_key], [attested_by], [provider_type], [cryptographic_provider_guid], [cryptographic_provider_algid] FROM sys.asymmetric_keys
Sql 2012
SELECT [name], [principal_id], [asymmetric_key_id], [pvt_key_encryption_type], [pvt_key_encryption_type_desc], [thumbprint], [algorithm], [algorithm_desc], [key_length], [sid], [string_sid], [public_key], [attested_by], [provider_type], [cryptographic_provider_guid], [cryptographic_provider_algid] FROM sys.asymmetric_keys

Back to Top


sys.certificates

certificate in the database.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Name of the certificate. Is unique within the database.
certificate_id int ID of the certificate. Is unique within the database.
principal_id int ID of the database principal that owns this certificate.
pvt_key_encryption_type char(2) How the private key is encrypted.
NA = There is no private key for the certificate
MK = Private key is encrypted by the master key
PW = Private key is encrypted by a user-defined password
SK = Private key is encrypted by the service master key.
pvt_key_encryption_type_desc nvarchar(60) Description of how the private key is encrypted.
NO_PRIVATE_KEY
ENCRYPTED_BY_MASTER_KEY
ENCRYPTED_BY_PASSWORD
ENCRYPTED_BY_SERVICE_MASTER_KEY
is_active_for_begin_dialog bit If 1, this certificate is used to initiate encrypted service dialogs.
issuer_name nvarchar(442) Name of certificate issuer.
cert_serial_number nvarchar(64) Serial number of certificate.
sid varbinary(85) Login SID for this certificate.
string_sid nvarchar(128) String representation of the login SID for this certificate
subject nvarchar(4000) Subject of this certificate.
expiry_date datetime When certificate expires.
start_date datetime When certificate becomes valid.
thumbprint varbinary(32) SHA-1 hash of the certificate. The SHA-1 hash is globally unique.
attested_by nvarchar(260) System use only.
pvt_key_last_backup_date   datetime The date and time the certificate€™s private key was last exported.

TSQL

Sql 2005
SELECT [name], [certificate_id], [principal_id], [pvt_key_encryption_type], [pvt_key_encryption_type_desc], [is_active_for_begin_dialog], [issuer_name], [cert_serial_number], [sid], [string_sid], [subject], [expiry_date], [start_date], [thumbprint], [attested_by] FROM sys.certificates
Sql 2008
SELECT [name], [certificate_id], [principal_id], [pvt_key_encryption_type], [pvt_key_encryption_type_desc], [is_active_for_begin_dialog], [issuer_name], [cert_serial_number], [sid], [string_sid], [subject], [expiry_date], [start_date], [thumbprint], [attested_by], [pvt_key_last_backup_date] FROM sys.certificates
Sql 2008 R2
SELECT [name], [certificate_id], [principal_id], [pvt_key_encryption_type], [pvt_key_encryption_type_desc], [is_active_for_begin_dialog], [issuer_name], [cert_serial_number], [sid], [string_sid], [subject], [expiry_date], [start_date], [thumbprint], [attested_by], [pvt_key_last_backup_date] FROM sys.certificates
Sql 2012
SELECT [name], [certificate_id], [principal_id], [pvt_key_encryption_type], [pvt_key_encryption_type_desc], [is_active_for_begin_dialog], [issuer_name], [cert_serial_number], [sid], [string_sid], [subject], [expiry_date], [start_date], [thumbprint], [attested_by], [pvt_key_last_backup_date] FROM sys.certificates

Back to Top


sys.credentials

credential.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
credential_id int ID of the credential. Is unique within the server
name sysname Name of the credential. Is unique within the server
credential_identity nvarchar(4000) Name of the identity to be used. This will generally be a Windows user. It need not be unique.
create_date datetime Time at which the credential was created
modify_date datetime Time at which the credential was last modified
target_type   nvarchar(100) Type of credential. Returns NULL for traditional credentials, CRYPTOGRAPHIC PROVIDER for credentials mapped to a cryptographic provider. For more information about external key management providers, see Understanding Extensible Key Management (EKM)1.
target_id   int ID of the object that the credential is mapped to. Returns 0 for traditional credentials and non-0 for credentials mapped to a cryptographic provider. For more information about external key management providers, see Understanding Extensible Key Management (EKM)1.

TSQL

Sql 2005
SELECT [credential_id], [name], [credential_identity], [create_date], [modify_date] FROM sys.credentials
Sql 2008
SELECT [credential_id], [name], [credential_identity], [create_date], [modify_date], [target_type], [target_id] FROM sys.credentials
Sql 2008 R2
SELECT [credential_id], [name], [credential_identity], [create_date], [modify_date], [target_type], [target_id] FROM sys.credentials
Sql 2012
SELECT [credential_id], [name], [credential_identity], [create_date], [modify_date], [target_type], [target_id] FROM sys.credentials

Back to Top


sys.crypt_properties

cryptographic property associated with a securable.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
class tinyint Identifies class of thing on which property exists.
1 = Object or column
class_desc nvarchar(60) Description of the class of thing on which property exists.
OBJECT_OR_COLUMN
major_id int ID of thing on which property exists, interpreted according to class
thumbprint varbinary(32) SHA-1 hash of the certificate or asymmetric key used.
crypt_type char(4) Encryption type.
SPVC = Encrypted by certificate private key
SPVA = Encrypted by asymmetric private key
CPVC = Counter signature by certificate private key
CPVA = Counter signature by asymmetric key
crypt_type_desc nvarchar(60) Description of encryption type.
SIGNATURE BY CERTIFICATE
SIGNATURE BY ASYMMETRIC KEY
COUNTER SIGNATURE BY CERTIFICATE
COUNTER SIGNATURE BY ASYMMETRIC KEY
crypt_property varbinary(max) Signed or encrypted bits.

TSQL

Sql 2005
SELECT [class], [class_desc], [major_id], [thumbprint], [crypt_type], [crypt_type_desc], [crypt_property] FROM sys.crypt_properties
Sql 2008
SELECT [class], [class_desc], [major_id], [thumbprint], [crypt_type], [crypt_type_desc], [crypt_property] FROM sys.crypt_properties
Sql 2008 R2
SELECT [class], [class_desc], [major_id], [thumbprint], [crypt_type], [crypt_type_desc], [crypt_property] FROM sys.crypt_properties
Sql 2012
SELECT [class], [class_desc], [major_id], [thumbprint], [crypt_type], [crypt_type_desc], [crypt_property] FROM sys.crypt_properties

Back to Top


sys.cryptographic_providers

registered cryptographic provider.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
provider_id   int Identification number of the cryptographic provider.
name   sysname Name of the cryptographic provider.
guid   uniqueidentifier Unique provider GUID.
version   nvarchar(50) Version of the provider in the format 'aa.bb.cccc.dd'.
dll_path   nvarchar(512) Path to DLL that implements the Extensible Key Management (EKM) Application Program Interface (API).
is_enabled   bit Whether the provider is enabled on the server or not.
0 = not enabled (default)
1 = enabled

TSQL

Sql 2008
SELECT [provider_id], [name], [guid], [version], [dll_path], [is_enabled] FROM sys.cryptographic_providers
Sql 2008 R2
SELECT [provider_id], [name], [guid], [version], [dll_path], [is_enabled] FROM sys.cryptographic_providers
Sql 2012
SELECT [provider_id], [name], [guid], [version], [dll_path], [is_enabled] FROM sys.cryptographic_providers

Back to Top


sys.database_audit_specification_details

Contains information about the database audit specifications in a SQL Server audit on a server instance for all databases. For more information, see SQL Server Audit (Database Engine)1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
database_specification_id   int ID of the audit specification.
audit_action_id   int ID of the audit action.
audit_action_name   Sysname Name of audit action or audit action group
class   int Identifies class of object which is being audited.
class_desc   Nvarchar(60) Description of class of object which is being audited:
- SCHEMA
- TABLE
major_id   int Major ID of object being audited, such as a Table ID of a Table Audit action.
minor_id   Int Secondary ID of object that is being audited, interpreted according to class, such as the column ID of a Table Audit action.
audited_principal_id   int Principal that is being audited.
audited_result   Nvarchar(60) Audit action results:
- SUCCESS AND FAILURE - SUCCESS
- FAILURE
is_group   Bit Shows whether the object is a group:
0 - Not a group
1 - Group

TSQL

Sql 2008
SELECT [database_specification_id], [audit_action_id], [audit_action_name], [class], [class_desc], [major_id], [minor_id], [audited_principal_id], [audited_result], [is_group] FROM sys.database_audit_specification_details
Sql 2008 R2
SELECT [database_specification_id], [audit_action_id], [audit_action_name], [class], [class_desc], [major_id], [minor_id], [audited_principal_id], [audited_result], [is_group] FROM sys.database_audit_specification_details
Sql 2012
SELECT [database_specification_id], [audit_action_id], [audit_action_name], [class], [class_desc], [major_id], [minor_id], [audited_principal_id], [audited_result], [is_group] FROM sys.database_audit_specification_details

Back to Top


sys.database_audit_specifications

Contains information about the database audit specifications in a SQL Server audit on a server instance. For more information, see SQL Server Audit (Database Engine)1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name   sysname Name of the auditing specification.
database_specification_id   int ID of the database specification.
create_date   datetime Date the audit specification was created.
modify_date   datetime Date the audit specification was last modified.
is_state_enabled   bit Audit specification state:
0 €“ ENABLED
1 €“ DISABLED
audit_guid   uniqueidentifer GUID for the audit that contains this specification. Used during enumeration of member database audit specifications during database attach/startup.

TSQL

Sql 2008
SELECT [name], [database_specification_id], [create_date], [modify_date], [is_state_enabled], [audit_guid] FROM sys.database_audit_specifications
Sql 2008 R2
SELECT [name], [database_specification_id], [create_date], [modify_date], [is_state_enabled], [audit_guid] FROM sys.database_audit_specifications
Sql 2012
SELECT [name], [database_specification_id], [create_date], [modify_date], [is_state_enabled], [audit_guid] FROM sys.database_audit_specifications

Back to Top


sys.database_permissions

Returns a row for every permission or column-exception permission in the database. For columns, there is a row for every permission that is different from the corresponding object-level permission. If the column permission is the same as the corresponding object permission, there will be no row for it and the actual permission used will be that of the object. Important Column-level permissions override object-level permissions on the same entity.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
class tinyint Identifies class on which permission exists.
0 = Database
1 = Object or Column
3 = Schema
4 = Database Principal
5 = Assembly
6 = Type
10 = XML Schema Collection
15 = Message Type
16 = Service Contract
17 = Service
18 = Remote Service Binding
19 = Route
23 = Full-Text Catalog
24 = Symmetric Key
25 = Certificate
26 = Asymmetric Key
class_desc nvarchar(60) Description of class on which permission exists.
DATABASE
OBJECT_OR_COLUMN
SCHEMA
DATABASE_PRINCIPAL
ASSEMBLY
TYPE
XML_SCHEMA_COLLECTION
MESSAGE_TYPE
SERVICE_CONTRACT
SERVICE
REMOTE_SERVICE_BINDING
ROUTE
FULLTEXT_CATALOG
SYMMETRIC_KEY
CERTIFICATE
ASYMMETRIC_KEY
major_id int ID of thing on which permission exists, interpreted according to class. For most, this is simply the kind of ID that applies to what the class represents. Interpretation for nonstandard is as follows:
0 = Always 0
1, 8 = Object-ID
Negative IDs are assigned to system objects.
minor_id int Secondary-ID of thing on which permission exists, interpreted according to class. For most, this is zero. Otherwise, it is the following:
1 = Column-ID if a column. Otherwise, it is 0 if an object.
grantee_principal_id int Database principal ID to which the permissions are granted.
grantor_principal_id int Database principal ID of the grantor of these permissions.
type char(4) Database permission type. For a list of permission types, see the next table.
permission_name nvarchar(128) Permission name.
state char(1) Permission state:
D = Deny
R = Revoke
G = Grant
W = Grant With Grant Option
state_desc nvarchar(60) Description of permission state:
DENY
REVOKE
GRANT
GRANT_WITH_GRANT_OPTION

TSQL

Sql 2005
SELECT [class], [class_desc], [major_id], [minor_id], [grantee_principal_id], [grantor_principal_id], [type], [permission_name], [state], [state_desc] FROM sys.database_permissions
Sql 2008
SELECT [class], [class_desc], [major_id], [minor_id], [grantee_principal_id], [grantor_principal_id], [type], [permission_name], [state], [state_desc] FROM sys.database_permissions
Sql 2008 R2
SELECT [class], [class_desc], [major_id], [minor_id], [grantee_principal_id], [grantor_principal_id], [type], [permission_name], [state], [state_desc] FROM sys.database_permissions
Sql 2012
SELECT [class], [class_desc], [major_id], [minor_id], [grantee_principal_id], [grantor_principal_id], [type], [permission_name], [state], [state_desc] FROM sys.database_permissions

Back to Top


sys.database_principals

principal in a database.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Name of principal, unique within the database.
principal_id int ID of principal, unique within the database.
type char(1) Principal type:
S = SQL user
U = Windows user
G = Windows group
A = Application role
R = Database role
C = User mapped to a certificate
K = User mapped to an asymmetric key
type_desc nvarchar(60) Description of principal type.
SQL_USER
WINDOWS_USER
WINDOWS_GROUP
APPLICATION_ROLE
DATABASE_ROLE
CERTIFICATE_MAPPED_USER
ASYMMETRIC_KEY_MAPPED_USER
default_schema_name sysname Name to be used when SQL name does not specify schema. Null for principals not of type S, U, or A.
create_date datetime Time at which the principal was created.
modify_date datetime Time at which the principal was last modified.
owning_principal_id int ID of the principal that owns this principal. All principals except Database Roles must be owned by dbo.
sid varbinary(85) SID (Security Identifier) if the principal is defined external to the database (type S, U, and G). Otherwise, NULL.
is_fixed_role bit If 1, then 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.

TSQL

Sql 2005
SELECT [name], [principal_id], [type], [type_desc], [default_schema_name], [create_date], [modify_date], [owning_principal_id], [sid], [is_fixed_role] FROM sys.database_principals
Sql 2008
SELECT [name], [principal_id], [type], [type_desc], [default_schema_name], [create_date], [modify_date], [owning_principal_id], [sid], [is_fixed_role] FROM sys.database_principals
Sql 2008 R2
SELECT [name], [principal_id], [type], [type_desc], [default_schema_name], [create_date], [modify_date], [owning_principal_id], [sid], [is_fixed_role] FROM sys.database_principals
Sql 2012
SELECT [name], [principal_id], [type], [type_desc], [default_schema_name], [create_date], [modify_date], [owning_principal_id], [sid], [is_fixed_role] FROM sys.database_principals

Back to Top


sys.database_role_members

member of each database role.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
role_principal_id int Database Principal ID of the role.
member_principal_id int Database Principal ID of the member.

TSQL

Sql 2005
SELECT [role_principal_id], [member_principal_id] FROM sys.database_role_members
Sql 2008
SELECT [role_principal_id], [member_principal_id] FROM sys.database_role_members
Sql 2008 R2
SELECT [role_principal_id], [member_principal_id] FROM sys.database_role_members
Sql 2012
SELECT [role_principal_id], [member_principal_id] FROM sys.database_role_members

Back to Top


sys.key_encryptions

symmetric key encryption specified by using the ENCRYPTION BY clause of the CREATE SYMMETRIC KEY statement.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
key_id int ID of the encrypted key.
thumbprint varbinary(32) SHA-1 hash of the certificate with which the key is encrypted, or the GUID of the symmetric key with which the key is encrypted.
crypt_type char(4) Type of encryption:
ESKS = Encrypted by symmetric key
ESKP = Encrypted by password
EPUC = Encrypted by certificate
EPUA = Encrypted by asymmetric key
ESKM = Encrypted by master key
crypt_type_desc nvarchar(60) Description of encryption type:
ENCRYPTION BY SYMMETRIC KEY
ENCRYPTION BY PASSWORD
ENCRYPTION BY CERTIFICATE
ENCRYPTION BY ASYMMETRIC KEY
ENCRYPTION BY MASTER KEY
crypt_property varbinary(max) Signed or encrypted bits.

TSQL

Sql 2005
SELECT [key_id], [thumbprint], [crypt_type], [crypt_type_desc], [crypt_property] FROM sys.key_encryptions
Sql 2008
SELECT [key_id], [thumbprint], [crypt_type], [crypt_type_desc], [crypt_property] FROM sys.key_encryptions
Sql 2008 R2
SELECT [key_id], [thumbprint], [crypt_type], [crypt_type_desc], [crypt_property] FROM sys.key_encryptions
Sql 2012
SELECT [key_id], [thumbprint], [crypt_type], [crypt_type_desc], [crypt_property] FROM sys.key_encryptions

Back to Top


sys.master_key_passwords

database master key password added by using the sp_control_dbmasterkey_password stored procedure. The passwords that are used to protect the master keys are stored in the credential store. The credential name follows this format: ##DBMKEY__##. The password is stored as the credential secret. For each password added by using sp_control_dbmasterkey_password, there is a row in sys.credentials. Each row in this view shows a credential_id and the family_guid of a database the master key of which is protected by the password associated with that credential. A join with sys.credentials on the credential_id will return useful fields, such as the create_date and credential name.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
credential_id int ID of the credential to which the password belongs. This ID is unique within the server instance.
family_guid uniqueidentifier Unique ID of the original database at creation. This GUID remains the same after the database is restored or attached, even if the database name is changed.
If automatic decryption by the service master key fails, SQL Server uses the family_guid to identify credentials that may contain the password used to protect the database master key.

TSQL

Sql 2005
SELECT [credential_id], [family_guid] FROM sys.master_key_passwords
Sql 2008
SELECT [credential_id], [family_guid] FROM sys.master_key_passwords
Sql 2008 R2
SELECT [credential_id], [family_guid] FROM sys.master_key_passwords
Sql 2012
SELECT [credential_id], [family_guid] FROM sys.master_key_passwords

Back to Top


sys.server_audit_specification_details

Contains information about the server audit specification details (actions) in a SQL Server audit on a server instance. For more information, see SQL Server Audit (Database Engine)1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
server_specification_id   int ID of the audit server specification
audit_action_id   int ID of the audit action
audit_action_name   sysname Name of group or name of audit action
class   tinyint Reserved
class_desc   nvarchar(60) Reserved
major_id   int Reserved
minor_id   int Reserved
audited_principal_id   int Reserved
audited_result   nvarchar(60) Audited result:
- SUCCESS AND FAILURE
- SUCCESS
- FAILURE
is_group   bit Whether the audited object is a group:
0 - Not a group
1 - Group

TSQL

Sql 2008
SELECT [server_specification_id], [audit_action_id], [audit_action_name], [class], [class_desc], [major_id], [minor_id], [audited_principal_id], [audited_result], [is_group] FROM sys.server_audit_specification_details
Sql 2008 R2
SELECT [server_specification_id], [audit_action_id], [audit_action_name], [class], [class_desc], [major_id], [minor_id], [audited_principal_id], [audited_result], [is_group] FROM sys.server_audit_specification_details
Sql 2012
SELECT [server_specification_id], [audit_action_id], [audit_action_name], [class], [class_desc], [major_id], [minor_id], [audited_principal_id], [audited_result], [is_group] FROM sys.server_audit_specification_details

Back to Top


sys.server_audit_specifications

Contains information about the server audit specifications in a SQL Server audit on a server instance. For more information on SQL Server Audit, see SQL Server Audit (Database Engine)1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name   Sysname Name of the server specification.
server_specification_id   Int ID of the server_specification.
create_date   Datetime Date the audit server specification was created.
modify_date   Datetime Date the audit server specification was last modified.
is_state_enabled   tinyint Audit specifications state:0 €“ ENABLED1 €“ DISABLED
audit_guid   uniqueidentifier GUID for the audit that contains this specification. Used during enumeration of member server audit specifications during server startup.

TSQL

Sql 2008
SELECT [name], [server_specification_id], [create_date], [modify_date], [is_state_enabled], [audit_guid] FROM sys.server_audit_specifications
Sql 2008 R2
SELECT [name], [server_specification_id], [create_date], [modify_date], [is_state_enabled], [audit_guid] FROM sys.server_audit_specifications
Sql 2012
SELECT [name], [server_specification_id], [create_date], [modify_date], [is_state_enabled], [audit_guid] FROM sys.server_audit_specifications

Back to Top


sys.server_audits

Contains one row for each SQL Server audit in a server instance. For more information, see SQL Server Audit (Database Engine)1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name   Sysname Name of the audit.
audit_id   int ID of the audit.
create_date   Datetime UTC date the audit was created.
modify_date   Datetime UTC date the audit was last modified.
principal_id   int ID of the owner of the audit, as registered to the server.
type   char(2) Audit type:
0 €“ NT Security event log
1 €“ NT Application event log
2 €“ File on file system
type_desc   Nvarchar(60) SECURITY LOGAPPICATION LOG
FILE
queue_delay   Int Maximum time, in milliseconds, to wait before writing to disk. If 0, the audit will guarantee a write before an event can continue.
on_failure   Tinyint On Failure to write an action entry:0 €“ Continue1 €“ Shutdown server instance
on_failure_desc   Nvarchar(60) On Failure to write an action entry:CONTINUESHUTDOWN SERVER INSTANCE
is_state_enabled   tinyint 0 €“ Disabled
1 - Enabled
audit_guid   uniqueidentifier GUID for the audit that is used to enumerate audits with member Server|Database audit specifications during server start-up and database attach operations.
predicate       nvarchar(3000) The predicate expression that is applied to the event.

TSQL

Sql 2008
SELECT [name], [audit_id], [create_date], [modify_date], [principal_id], [type], [type_desc], [queue_delay], [on_failure], [on_failure_desc], [is_state_enabled], [audit_guid] FROM sys.server_audits
Sql 2008 R2
SELECT [name], [audit_id], [create_date], [modify_date], [principal_id], [type], [type_desc], [queue_delay], [on_failure], [on_failure_desc], [is_state_enabled], [audit_guid] FROM sys.server_audits
Sql 2012
SELECT [audit_id], [name], [audit_guid], [create_date], [modify_date], [principal_id], [type], [type_desc], [on_failure], [on_failure_desc], [is_state_enabled], [queue_delay], [predicate] FROM sys.server_audits

Back to Top


sys.server_file_audits

Contains extended information about the file audit type in a SQL Server audit on a server instance. For more information, see SQL Server Audit (Database Engine)1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
audit_id   int ID of the audit.
name   sysname Name of the audit.
audit_guid   uniqueidentifier GUID of the audit.
create_date   datetime UTC date when the file audit was created.
modify_date   datatime UTC date when the file audit was last modified.
principal_id   int ID of the owner of the audit as registered on the server.
type   char(2) Audit type:
0 = NT Security event log
1 = NT Application event log
2 = File on file system
type_desc   nvarchar(60) Audit type description.
on_failure   tinyint On Failure condition:
0 = Continue
1 = Shut down server instance
on_failure_desc   nvarchar(60) On Failure to write an action entry:
CONTINUE
SHUTDOWN SERVER INSTANCE
is_state_enabled   tinyint 0 = Disabled
1 = Enabled
queue_delay   int Suggested maximum time, in milliseconds, to wait before writing to disk. If 0, the audit will guarantee a write before the event can continue.
max_file_size   bigint Maximum size, in kilobytes, of the audit:
0 = Unlimited/Not applicable to the type of audit selected.
max_rollover_files   int Maximum number of files to use.
reserve_disk_space   int Amount of disk space to reserve per file.
log_file_path   nvarchar(260) Path to where audit is located. File path for file audit, application log path for application log audit.
log_file_name   nvarchar(260) Base name for the log file supplied in the CREATE AUDIT DDL. An incremental number is added to the base_log_name file as a suffix to create the log file name.
predicate       nvarchar(8000) Predicate expression that is applied to the event.
max_files       int Maximum number of files to use without the rollover option.

TSQL

Sql 2008
SELECT [audit_id], [name], [audit_guid], [create_date], [modify_date], [principal_id], [type], [type_desc], [on_failure], [on_failure_desc], [is_state_enabled], [queue_delay], [max_file_size], [max_rollover_files], [reserve_disk_space], [log_file_path], [log_file_name] FROM sys.server_file_audits
Sql 2008 R2
SELECT [audit_id], [name], [audit_guid], [create_date], [modify_date], [principal_id], [type], [type_desc], [on_failure], [on_failure_desc], [is_state_enabled], [queue_delay], [max_file_size], [max_rollover_files], [reserve_disk_space], [log_file_path], [log_file_name] FROM sys.server_file_audits
Sql 2012
SELECT [audit_id], [name], [audit_guid], [create_date], [modify_date], [principal_id], [type], [type_desc], [on_failure], [on_failure_desc], [is_state_enabled], [queue_delay], [predicate], [max_file_size], [max_rollover_files], [max_files], [reserve_disk_space], [log_file_path], [log_file_name] FROM sys.server_file_audits

Back to Top


sys.server_permissions

server-level permission.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
class tinyint Identifies class of thing on which permission exists.
100 = Server
101 = Server-principal
105 = Endpoint
class_desc nvarchar(60) Description of class on which permission exists.
SERVER

SERVER_PRINCIPAL

ENDPOINT
major_id int ID of the securable on which permission exists, interpreted according to class. For most, this is just the kind of ID that applies to what the class represents. Interpretation for non-standard is as follows:
100 = Always 0
minor_id int Secondary ID of thing on which permission exists, interpreted according to class.
grantee_principal_id int Server-principal-ID to which the permissions are granted.
grantor_principal_id int Server-principal-ID of the grantor of these permissions.
type char(4) Server permission type. For a list of permission types, see the next table.
permission_name nvarchar(128) Permission name.
state char(1) Permission state:
D = Deny
R = Revoke
G = Grant
W = Grant With Grant option
state_desc nvarchar(60) Description of permission state:
DENY
REVOKE
GRANT
GRANT_WITH_GRANT_OPTION

TSQL

Sql 2005
SELECT [class], [class_desc], [major_id], [minor_id], [grantee_principal_id], [grantor_principal_id], [type], [permission_name], [state], [state_desc] FROM sys.server_permissions
Sql 2008
SELECT [class], [class_desc], [major_id], [minor_id], [grantee_principal_id], [grantor_principal_id], [type], [permission_name], [state], [state_desc] FROM sys.server_permissions
Sql 2008 R2
SELECT [class], [class_desc], [major_id], [minor_id], [grantee_principal_id], [grantor_principal_id], [type], [permission_name], [state], [state_desc] FROM sys.server_permissions
Sql 2012
SELECT [class], [class_desc], [major_id], [minor_id], [grantee_principal_id], [grantor_principal_id], [type], [permission_name], [state], [state_desc] FROM sys.server_permissions

Back to Top


sys.server_principals

Contains a row for every server-level principal.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Name of the principal. Is unique within a server.
principal_id int ID number of the Principal. Is unique within a server.
sid varbinary(85) SID (Security-IDentifier) of the principal. If Windows principal, then it matches Windows SID.
type char(1) Principal type:
S = SQL login
U = Windows login
G = Windows group
R = Server role
C = Login mapped to a certificate
K = Login mapped to an asymmetric key
type_desc nvarchar(60) Description of the principal type:
SQL_LOGIN
WINDOWS_LOGIN
WINDOWS_GROUP
SERVER_ROLE
CERTIFICATE_MAPPED_LOGIN
ASYMMETRIC_KEY_MAPPED_LOGIN
is_disabled bit 1 = Login is disabled.
create_date datetime Time at which the principal was created.
modify_date datetime Time at which the principal definition was last modified.
default_database_name sysname Default database for this principal.
default_language_name sysname Default language for this principal.
credential_id int ID of a credential associated with this principal. If no credential is associated with this principal, credential_id will be NULL.
owning_principal_id       int The principal_id of the owner of a server role. NULL if the principal is not a server role.
is_fixed_role       bit Returns 1 if the principal is one of the fixed server roles. For more information, see Permissions of Fixed Server Roles (Database Engine)1.

TSQL

Sql 2005
SELECT [name], [principal_id], [sid], [type], [type_desc], [is_disabled], [create_date], [modify_date], [default_database_name], [default_language_name], [credential_id] FROM sys.server_principals
Sql 2008
SELECT [name], [principal_id], [sid], [type], [type_desc], [is_disabled], [create_date], [modify_date], [default_database_name], [default_language_name], [credential_id] FROM sys.server_principals
Sql 2008 R2
SELECT [name], [principal_id], [sid], [type], [type_desc], [is_disabled], [create_date], [modify_date], [default_database_name], [default_language_name], [credential_id] FROM sys.server_principals
Sql 2012
SELECT [name], [principal_id], [sid], [type], [type_desc], [is_disabled], [create_date], [modify_date], [default_database_name], [default_language_name], [credential_id], [owning_principal_id], [is_fixed_role] FROM sys.server_principals

Back to Top


sys.sql_logins

Returns one row for every SQL Server authentication login.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
name sysname sys.server_principals Name of the principal. Is unique within a server.
principal_id int sys.server_principals ID number of the Principal. Is unique within a server.
sid varbinary(85) sys.server_principals SID (Security-IDentifier) of the principal. If Windows principal, then it matches Windows SID.
type char(1) sys.server_principals Principal type:
S = SQL login
U = Windows login
G = Windows group
R = Server role
C = Login mapped to a certificate
K = Login mapped to an asymmetric key
type_desc nvarchar(60) sys.server_principals Description of the principal type:
SQL_LOGIN
WINDOWS_LOGIN
WINDOWS_GROUP
SERVER_ROLE
CERTIFICATE_MAPPED_LOGIN
ASYMMETRIC_KEY_MAPPED_LOGIN
is_disabled bit sys.server_principals 1 = Login is disabled.
create_date datetime sys.server_principals Time at which the principal was created.
modify_date datetime sys.server_principals Time at which the principal definition was last modified.
default_database_name sysname sys.server_principals Default database for this principal.
default_language_name sysname sys.server_principals Default language for this principal.
credential_id int sys.server_principals ID of a credential associated with this principal. If no credential is associated with this principal, credential_id will be NULL.
is_policy_checked bit   Password policy is checked.
is_expiration_checked bit   Password expiration is checked.
password_hash varbinary(256)   Hash of SQL login password.
owning_principal_id       int sys.server_principals The principal_id of the owner of a server role. NULL if the principal is not a server role.
is_fixed_role       bit sys.server_principals Returns 1 if the principal is one of the fixed server roles. For more information, see Permissions of Fixed Server Roles (Database Engine)1.

TSQL

Sql 2005
SELECT [name], [principal_id], [sid], [type], [type_desc], [is_disabled], [create_date], [modify_date], [default_database_name], [default_language_name], [credential_id], [is_policy_checked], [is_expiration_checked], [password_hash] FROM sys.sql_logins
Sql 2008
SELECT [name], [principal_id], [sid], [type], [type_desc], [is_disabled], [create_date], [modify_date], [default_database_name], [default_language_name], [credential_id], [is_policy_checked], [is_expiration_checked], [password_hash] FROM sys.sql_logins
Sql 2008 R2
SELECT [name], [principal_id], [sid], [type], [type_desc], [is_disabled], [create_date], [modify_date], [default_database_name], [default_language_name], [credential_id], [is_policy_checked], [is_expiration_checked], [password_hash] FROM sys.sql_logins
Sql 2012
SELECT [name], [principal_id], [sid], [type], [type_desc], [is_disabled], [create_date], [modify_date], [default_database_name], [default_language_name], [credential_id], [owning_principal_id], [is_fixed_role], [is_policy_checked], [is_expiration_checked], [password_hash] FROM sys.sql_logins

Back to Top


sys.symmetric_keys

Returns one row for every symmetric key created with the CREATE SYMMETRIC KEY statement.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Name of the key. Unique within the database.
principal_id int ID of the database principal who owns the key.
symmetric_key_id int ID of the key. Unique within the database.
key_length int Length of the key in bits.
key_algorithm char(2) Algorithm used with the key:
R2 = RC2
R4 = RC4
D = DES
D3 = Triple DES
DX = DESX
A1 = AES 128
A2 = AES 192
A3 = AES 256
algorithm_desc nvarchar(60) Description of the algorithm used with the key:
RC2
RC4
DES
Triple_DES
DESX
AES_128
AES_192
AES_256
create_date datetime Date the key was created.
modify_date datetime Date the key was modified.
key_guid uniqueidentifier Globally unique identifier (GUID) associated with the key. It is auto-generated for persisted keys. GUIDs for temporary keys are derived from the user-supplied pass phrase.
key_thumbprint   sql_variant SHA-1 hash of the key. The hash is globally unique. For non-Extensible Key Management keys this value will be NULL.
provider_type   nvarchar(120) Type of cryptographic provider:
CRYPTOGRAPHIC PROVIDER = Extensible Key Management keys
NULL = Non-Extensible Key Management keys
cryptographic_provider_guid   uniqueidentifier GUID for the cryptographic provider. For non-Extensible Key Management keys this value will be NULL.
cryptographic_provider_algid   sql_variant Algorithm ID for the cryptographic provider. For non-Extensible Key Management keys this value will be NULL.

TSQL

Sql 2005
SELECT [name], [principal_id], [symmetric_key_id], [key_length], [key_algorithm], [algorithm_desc], [create_date], [modify_date], [key_guid] FROM sys.symmetric_keys
Sql 2008
SELECT [name], [principal_id], [symmetric_key_id], [key_length], [key_algorithm], [algorithm_desc], [create_date], [modify_date], [key_guid], [key_thumbprint], [provider_type], [cryptographic_provider_guid], [cryptographic_provider_algid] FROM sys.symmetric_keys
Sql 2008 R2
SELECT [name], [principal_id], [symmetric_key_id], [key_length], [key_algorithm], [algorithm_desc], [create_date], [modify_date], [key_guid], [key_thumbprint], [provider_type], [cryptographic_provider_guid], [cryptographic_provider_algid] FROM sys.symmetric_keys
Sql 2012
SELECT [name], [principal_id], [symmetric_key_id], [key_length], [key_algorithm], [algorithm_desc], [create_date], [modify_date], [key_guid], [key_thumbprint], [provider_type], [cryptographic_provider_guid], [cryptographic_provider_algid] FROM sys.symmetric_keys

Back to Top


sys.system_components_surface_area_configuration

executable system object that can be enabled or disabled by a surface area configuration component. For more information, see Surface Area Configuration1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
component_name sysname Component name. This will have the keyword collation, Latin1_General_CI_AS_KS_WS. Cannot be NULL.
database_name sysname Database that contains the object. This will have the keyword collation, Latin1_General_CI_AS_KS_WS. Must be one of the following:
master
msdb
mssqlsystemresource
schema_name sysname Schema that contains the object. This will have the keyword collation, Latin1_General_CI_AS_KS_WS. Cannot be NULL.
object_name sysname Name of the object. This will have the keyword collation, Latin1_General_CI_AS_KS_WS. Cannot be NULL.
state tinyint 0 = Disabled
1 = Enabled
type char(2) Object type. Can be one of the following:
P = SQL_STORED_PROCEDURE
PC = CLR_STORED_PROCEDURE
FN = SQL_SCALAR_FUNCTION
FS = CLR_SCALAR_FUNCTION
FT = CLR_TABLE_VALUED_FUNCTION
IF = SQL_INLINE_TABLE_VALUED_FUNCTION
TF = SQL_TABLE_VALUED_FUNCTION
X = EXTENDED_STORED_PROCEDURE
type_desc nvarchar(60) Friendly name description of the object type.

TSQL

Sql 2005
SELECT [component_name], [database_name], [schema_name], [object_name], [state], [type], [type_desc] FROM sys.system_components_surface_area_configuration
Sql 2008
SELECT [component_name], [database_name], [schema_name], [object_name], [state], [type], [type_desc] FROM sys.system_components_surface_area_configuration
Sql 2008 R2
SELECT [component_name], [database_name], [schema_name], [object_name], [state], [type], [type_desc] FROM sys.system_components_surface_area_configuration
Sql 2012
SELECT [component_name], [database_name], [schema_name], [object_name], [state], [type], [type_desc] FROM sys.system_components_surface_area_configuration

Back to Top

No comments:

Post a Comment

Total Pageviews