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
Indsendt af kl.
Etiketter: , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Total Pageviews