{"id":84051,"date":"2019-04-23T20:49:26","date_gmt":"2019-04-23T20:49:26","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=84051"},"modified":"2021-04-29T15:29:51","modified_gmt":"2021-04-29T15:29:51","slug":"introduction-to-sql-server-security-part-5","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/security\/introduction-to-sql-server-security-part-5\/","title":{"rendered":"Introduction to SQL Server Security \u2014 Part 5"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. Introduction to SQL Server Security \u2014 Part 1<\/a><\/li>\n
  2. Introduction to SQL Server Security \u2014 Part 2<\/a><\/li>\n
  3. Introduction to SQL Server Security \u2014 Part 3<\/a><\/li>\n
  4. Introduction to SQL Server Security \u2014 Part 4<\/a><\/li>\n
  5. Introduction to SQL Server Security \u2014 Part 5<\/a><\/li>\n
  6. Introduction to SQL Server Security\u00a0\u2014 <\/span>Part 6<\/a><\/li>\n
  7. \u00a0<\/li>\n<\/ol>\n\n

    SQL Server provides a number of tools for helping you ensure that your environment and data remain secure. Some of the most important of these come in the form of catalog views, dynamic management views (DMVs), and built-in system functions. By incorporating them into your T-SQL statements, you can retrieve important security-related information about your SQL Server environment, such as which principals are enabled, what permissions are assigned to those principals, or how auditing is implemented on database objects.<\/p>\n

    In this article, I cover a number of these views and functions and provide examples of how you can use them to retrieve details about your system\u2019s security. I created the statements in SQL Server Management Studio (SSMS), using SQL Server 2017, although most of the information I cover can apply to SQL Server editions going back to 2014 or earlier.<\/p>\n

    Microsoft divides security-related catalog views into server-level views, database-level views, encryption-related views, and auditing-related views. I\u2019ve taken the same approach here, providing a section for each type. Because there are fewer security-related DMVs and system functions, I\u2019ve provided only a single section for each of them.<\/p>\n

    In all cases, I cover only a portion of the available views and functions, but enough to give you a sense of how you can use them when administering a SQL Server instance. Just be aware that Microsoft provides plenty of others, so be sure to refer to SQL Server documentation as necessary to learn about those views and functions I haven\u2019t covered here.<\/p>\n

    Server-Level Catalog Views<\/h2>\n

    SQL Server offers numerous security-related catalog views that operate at the server level. Two of the most useful are sys.server_principals<\/code> and sys.server_permissions<\/code>. The sys.server_principals<\/code> view returns a row for each existing server principal, and the sys.server_permissions<\/code> view returns a row for each assigned server permission.<\/p>\n

    You can join these views together to see the permissions granted on specific principals. For example, the following SELECT<\/code> statement returns the permissions assigned to SQL logins that have been enabled:<\/p>\n

    SELECT pr.principal_id, pr.name, pe.state_desc, pe.permission_name\r\nFROM sys.server_principals pr INNER JOIN sys.server_permissions pe \r\n  ON pr.principal_id = pe.grantee_principal_id\r\nWHERE pr.type_desc = 'SQL_LOGIN' AND pr.is_disabled = 0;<\/pre>\n
    The sys.server_principals<\/code> view includes the type_desc<\/code> column, which indicates the principal type. SQL Server supports six types of server principals, including SQL_LOGIN<\/code>, WINDOWS_LOGIN<\/code> and SERVER_ROLE<\/code>. The view also returns the is_disabled<\/code> column, which indicates whether a principal is enabled (0<\/code>) or disabled (1<\/code>). Both columns are specified in the WHERE<\/code> clause to filter out all but enabled SQL logins. Figure 1 shows the results returned by the SELECT<\/code> statement on my system, which includes only a few test logins.<\/p>\n
    <\/p>\n
    Figure 1. Permissions granted on SQL logins<\/p>\n
    You can also use the sys.server_principals<\/code> view in conjunction with the sys.server_role_members<\/code> view, which returns a row for each member of a fixed or user-defined server role. Because the sys.server_principals<\/code> view includes all principals, including roles and logins, you can join the view to itself and to the sys.server_role_members<\/code> view to retrieve the members of a specific role, as shown in the following example:<\/p>\n
    SELECT member.principal_id, member.name\r\nFROM sys.server_role_members rm\r\nJOIN sys.server_principals role  \r\n  ON rm.role_principal_id = role.principal_id  \r\nJOIN sys.server_principals member  \r\n  ON rm.member_principal_id = member.principal_id\r\nWHERE role.name = 'sysadmin' AND member.type_desc = 'SQL_LOGIN';  <\/pre>\n
    The statement limits the results to the sysadmin<\/code> role and to SQL logins. Self-joining the sys.server_principals<\/code> view makes it possible to retrieve both the role and login names. Figure 2 shows the results I received on my system, which indicate that the only two SQL logins are members of the sysadmin<\/code> role, one of which is the sa<\/code> account.<\/p>\n
    <\/p>\n
    Figure 2. SQL logins in the sysadmin role<\/p>\n
    Another interesting catalog view is sys.system_components_surface_area_configuration<\/code>, which returns a row for each executable object that can be enabled or disabled through SQL Server\u2019s surface area configuration features. For example, the following statement uses the view to return a list of stored procedures included in the SMO<\/code> and<\/code> DMO<\/code> XPs<\/code> component:<\/p>\n
    SELECT database_name, schema_name, object_name\r\nFROM sys.system_components_surface_area_configuration\r\nWHERE state = 1 AND component_name = 'SMO and DMO XPs'\r\n  AND type_desc = 'SQL_STORED_PROCEDURE';<\/pre>\n
    The type_desc<\/code> column in the WHERE<\/code> clause specifies the object type (SQL_STORED_PROCEDURE<\/code>), and the state<\/code> column indicates that the object should be enabled (1<\/code>), rather than disabled (0<\/code>). Figure 3 shows the results returned on my system.<\/p>\n
    <\/p>\n
    Figure 3. Stored procedures in the surface area configuration<\/p>\n
    The database_name<\/code> value indicates which database contains the object. The possible values include master<\/code>, msdb<\/code>, and mssqlsystemresource<\/code> (a read-only database that contains all the system objects).<\/p>\n
     <\/p>\n
    Database-Level Catalog Views<\/h2>\n
    Many of the database-level catalog views work much like their server-level counterparts, except that they\u2019re specific to the current database. To try out some of these views, first create the Test1<\/code> database and then add the Sales<\/code> schema (which will be used in later examples):<\/p>\n
    USE master;\r\nGO\r\nCREATE DATABASE Test1; \r\nGO\r\nUSE Test1;\r\nGO\r\nCREATE SCHEMA Sales; \r\nGO<\/pre>\n
    After you create the database, you can run the catalog views within the context of that database. For example, you can use the sys.database_principals<\/code> view to retrieve details about the existing database principals, and you can use the sys.database_permissions<\/code> view to retrieve details about assigned database permissions. Similar to what you saw at the server level, you can join these views to see the permissions assigned to specific database principals. For example, the following SELECT<\/code> statement returns certain types of permissions granted to the public<\/code> role:<\/p>\n
    SELECT pm.state_desc, \r\n  pm.permission_name\r\nFROM sys.database_permissions pm INNER JOIN sys.database_principals pr \r\n  ON pm.grantee_principal_id = pr.principal_id\r\nWHERE pr.name = 'public' AND pm.class_desc = 'DATABASE';<\/pre>\n
    The statement limits the results to permissions in the DATABASE<\/code> class. However, the view can also return permissions for such classes as OBJECT_OR_COLUMN<\/code>, SCHEMA<\/code>, or DATABASE_PRINCIPAL<\/code>, depending on what permissions have been assigned at the database level. Figure 4 shows the results that the SELECT<\/code> statement returned on my system.<\/p>\n
    <\/p>\n
    Figure 4. Database permissions granted to the public role<\/p>\n
    One way to check effective permissions without writing scripts yourself is to use Redgate\u2019s <\/span>SQL Census<\/a>. It creates a report of who has access to what on your SQL Servers and makes best practice recommendations, like disabling SA accounts. It\u2019s still in development but it\u2019s a good starting point to check on your SQL Server permissions and undertake any necessary cleaning tasks.<\/span><\/p>\n
    SQL Server also provides database-level catalog views that do not have a server counterpart (and vice versa). For example, you can use the sys.master_key_passwords<\/code> view to retrieve information about the database master key password, if the password was added by using the sp_control_dbmasterkey_password<\/code> stored procedure.<\/p>\n
    To see how this works, start by creating a database master key in the Test1<\/code> database, as shown in the following example (using a stronger password than the one I\u2019ve included here):<\/p>\n
    CREATE MASTER KEY \r\nENCRYPTION BY PASSWORD = 'tempPW@56789';\r\nGO<\/pre>\n
    After you\u2019ve created the master key, use the sp_control_dbmasterkey_password<\/code> stored procedure to add a credential that specifies the same password as the one used when creating the database master key:<\/p>\n
    EXEC sp_control_dbmasterkey_password \r\n  @db_name = N'Test1',   \r\n  @password = N'tempPW@56789', \r\n  @action = N'add';  \r\nGO<\/pre>\n
    When SQL Server tries to decrypt the database master key, it first attempts to use the service master key. If this doesn\u2019t work, SQL Server searches the credential store for a master key credential, using that if it exists.<\/p>\n
    After you\u2019ve created the master key credential, you can use the sys.master_key_passwords<\/code> view to retrieve information about that credential:<\/p>\n
    SELECT * FROM sys.master_key_passwords;<\/pre>\n
    The statement returns only the credential_id<\/code> and family_guid<\/code> values, as shown in Figure 5. The family_guid<\/code> column displays the unique ID assigned to the database when it was originally created.<\/p>\n
    <\/p>\n
    Figure 5. Configured database master key password<\/p>\n
    You can also use the sys.credentials<\/code> view to retrieve credential information, as shown in the following example:<\/p>\n
    SELECT credential_id, name, credential_identity\r\nFROM sys.credentials;<\/pre>\n
    Although the sys.credentials<\/code> view is a server-level catalog view, you can use it to see credentials created for database master keys. On my system, the SELECT<\/code> statement returns the results shown in Figure 6.<\/p>\n
    <\/p>\n
    Figure 6. Credential associated with the Test1 database master key<\/p>\n
    The results indicate that I have only one credential created on my SQL Server instance, the one for the Test1<\/code> database master key. Notice that the credential_id<\/code> value shown here is the same value shown in Figure 5.<\/p>\n
    SQL Server also provides the sys.database_scoped_credentials<\/code> view, which returns a row for each database-scoped credential in the database. The following SELECT<\/code> statement uses the view within the context of the Test1<\/code> database:<\/p>\n
    SELECT * FROM sys.database_scoped_credentials;<\/pre>\n
    The SELECT<\/code> statement returns no rows for Test1<\/code>. This is because the credential created for the database master key exists at the server level, not the database level.<\/p>\n
    Encryption-Related Catalog Views<\/h2>\n
    The security-related catalog views also include about a dozen specific to SQL Server\u2019s encryption features. One of these views is sys.certificates<\/code>, which you can use to retrieve details about the certificates that exist in a database. To see the view in action, first create a certificate named Cert1<\/code> in the Test1<\/code> database, using the subject customer<\/code> credit<\/code> cards<\/code> (or whatever subject you want to use):<\/p>\n
    USE Test1;\r\nGO\r\nCREATE CERTIFICATE Cert1\r\nWITH SUBJECT = 'customer credit cards';<\/pre>\n
    When you create a certificate without specifying a password, SQL Server uses the database master key to encrypt the certificate, which means that a master key must already exist. (You created the key in the previous section.) You can now use the sys.certificates<\/code> view to retrieve information about the certificate:<\/p>\n
    SELECT name CertName,\r\n  certificate_id cert_id,\r\n  pvt_key_encryption_type_desc encrypt_type,\r\n  issuer_name\r\nFROM sys.certificates;<\/pre>\n
    Figure 7 shows the results returned on my system. Notice that the encryption type is ENCRYPTED_BY_MASTER_KEY<\/code>.<\/p>\n
    <\/p>\n
    Figure 7. Cert1 certificate in the Test1 database<\/p>\n
    SQL Server also lets you add asymmetric and symmetric keys to your database. If you\u2019ve added either type of key, you can use the sys.asymmetric_keys<\/code> or sys.symmetric_keys<\/code> view to return details about them. For example, suppose you create the following asymmetric key in the Test1<\/code> database:<\/p>\n
    CREATE ASYMMETRIC KEY Akey1\r\n  WITH ALGORITHM = RSA_2048   \r\n  ENCRYPTION BY PASSWORD = 'tempPW@56789';   \r\nGO<\/pre>\n
    The statement adds an asymmetric key named Akey1<\/code>, using RSA encryption and password protection. You can now use the sys.asymmetric_keys<\/code> view to retrieve information about the new key:<\/p>\n
    SELECT name key_name,\r\n  pvt_key_encryption_type_desc encrypt_type,\r\n  algorithm_desc,\r\n  key_length\r\nFROM sys.asymmetric_keys;<\/pre>\n
    Figure 8 shows the results returned by the SELECT<\/code> statement.<\/p>\n
    <\/p>\n
    Figure 8. Akey1 asymmetric key in the Test1 database<\/p>\n
    The SELECT<\/code> statement returns the key name, encryption type, algorithm, and key length, all of which were specified when creating the asymmetric key.<\/p>\n
    Auditing-Related Catalog Views<\/h2>\n
    The final category of security-related catalog views includes those specific to SQL Server\u2019s auditing features. If you\u2019ve implemented auditing, these views can be particularly handy, especially the sys.server_audits<\/code> and sys.database_audit_specifications<\/code> views. The sys.server_audits<\/code> view returns information about server audit objects, and the sys.database_audit_specifications<\/code> view returns information about database audit specifications.<\/p>\n
    To see both views in action, start by creating and enabling the SrvAudit1<\/code> audit and the DbSpec1<\/code> database specification in the Test1<\/code> database:<\/p>\n
    USE master;  \r\nGO  \r\nCREATE SERVER AUDIT SrvAudit1  \r\nTO FILE (FILEPATH = 'C:\\DataFiles\\audit\\');  \r\nGO  \r\nALTER SERVER AUDIT SrvAudit1  \r\nWITH (STATE = ON);  \r\nGO  \r\nUSE Test1;\r\nGO  \r\nCREATE DATABASE AUDIT SPECIFICATION DbSpec1 \r\nFOR SERVER AUDIT SrvAudit1\r\nADD (SCHEMA_OBJECT_CHANGE_GROUP),\r\nADD (SELECT, INSERT, UPDATE, DELETE \r\n  ON Schema::Sales BY public)  \r\nWITH (STATE = ON);  \r\nGO<\/pre>\n
    After you\u2019ve created your server audit object, you can use the sys.server_audits<\/code> view to view that object, specifying the audit name in your WHERE<\/code> clause, as shown in the following example:<\/p>\n
    SELECT audit_id,\r\n  name audit_name,\r\n  create_date,\r\n  type_desc,\r\n  is_state_enabled is_enabled\r\nFROM sys.server_audits\r\nWHERE name = 'SrvAudit1';<\/pre>\n
    The statement returns the results shown in Figure 9. Notice that the type_desc<\/code> value is FILE<\/code>, indicating that the audit log is saved to the file system rather than to the Security or Application log. The figure also indicates that the audit is enabled. (The is_enabled<\/code> value is 1<\/code>.)<\/p>\n
    <\/p>\n
    Figure 9. SrvAudit1 audit created on the server<\/p>\n
    You can then use the sys.database_audit_specifications<\/code> view to view information about the database audit specification:<\/p>\n
    SELECT database_specification_id dbspec_id,\r\n  name spec_name,\r\n  create_date,\r\n  is_state_enabled is_enabled\r\nFROM sys.database_audit_specifications;<\/pre>\n
    The statement returns the results shown in Figure 10.<\/p>\n
    <\/p>\n
    Figure 10. DbSpec1 database audit specification<\/p>\n
    SQL Server also provides several other catalog views specific to auditing, but their use depends on how you\u2019ve configured auditing on your SQL Server instance. For example, if you create server audit specifications, you can use the sys.server_audit_specifications<\/code> view to retrieve information about those specifications.<\/p>\n
    Dynamic Management Views<\/h2>\n
    As with catalog views, SQL Server offers DMVs specific to auditing. One of these views is sys.dm_audit_actions<\/code>, which lets you retrieve event-related information about audit actions and audit groups. For example, the following SELECT<\/code> statement uses the view to return the IDs and names of the actions or groups with a class_desc<\/code> value of LOGIN<\/code> and a covering_parent_action_name<\/code> value of LOGIN_CHANGE_PASSWORD_GROUP<\/code>:<\/p>\n
    SELECT action_id,\r\n  name action_name\r\nFROM sys.dm_audit_actions\r\nWHERE class_desc = 'LOGIN' AND\r\n  covering_parent_action_name = 'LOGIN_CHANGE_PASSWORD_GROUP';<\/pre>\n
    The class_desc<\/code> column refers to the object class that the audit action applies to. The covering_parent_action_name<\/code> column is the audit action or group that contains the row\u2019s audit action. On my system, the SELECT<\/code> statement returned the results shown in Figure 11.<\/p>\n
    <\/p>\n
    Figure 11. Audit actions in the audit log<\/p>\n
    Another DMV specific to auditing is sys.dm_server_audit_status<\/code> view, which returns information about server audit objects. In the following example, the SELECT<\/code> statement uses the view to retrieve the ID, name, status, and file size of each defined audit:<\/p>\n
    SELECT audit_id,\r\n  name audit_name,\r\n  status_desc,\r\n  audit_file_size\r\nFROM sys.dm_server_audit_status;<\/pre>\n
    The only audit I had defined on my system when I ran this statement was the one created in the previous section, giving me the results shown in Figure 12.<\/p>\n
    <\/p>\n
    Figure 12. SrvAudit1 audit status<\/p>\n
    Security-related DMVs are not limited to auditing. SQL Server also provides several views specific to encryption, such as the sys.dm_database_encryption_keys<\/code> view, which returns details about a database\u2019s encryption state and its encryption keys.<\/p>\n
    You can see how the view works by setting up Transparent Data Encryption (TDE) on the Test1<\/code> database. For this, you need to take the following steps:<\/p>\n
      \n
    1. Create a database master key in the master<\/code> database, if the key doesn\u2019t already exist.<\/li>\n
    2. Create a certificate in the master<\/code> database for securing the master key.<\/li>\n
    3. Create a database encryption key in the Test1<\/code> database.<\/li>\n<\/ol>\n
      Normally, there would be an additional step to enable TDE on the Test1<\/code> database, but that\u2019s not necessary to demonstrate how the sys.dm_database_encryption_keys<\/code> view works.<\/p>\n
      To create a database master key in the master<\/code> database, run the following CREATE<\/code> MASTER<\/code> KEY<\/code> statement, providing a much more robust password, of course:<\/p>\n
      USE master;\r\nGO\r\nCREATE MASTER KEY \r\nENCRYPTION BY PASSWORD = 'tempPW@56789';\r\nGO<\/pre>\n
      You can then use the sys.symmetric_keys<\/code> catalog view to view information about the master database key:<\/p>\n
      SELECT name mkey_name,\r\n  symmetric_key_id mkey_id,\r\n  key_length,\r\n  algorithm_desc\r\nFROM sys.symmetric_keys;<\/pre>\n
      Figure 13 shows the results returned by the SELECT<\/code> statement on my system. When you create a database master key in the master<\/code> database, SQL Server also adds a service master key.<\/p>\n
      <\/p>\n
      Figure 13. Database and service master keys<\/p>\n
      The next step is to create a certificate in the master<\/code> database for securing the master key. You can do this easily enough by running the following CREATE<\/code> CERTIFICATE<\/code> statement:<\/p>\n
      CREATE CERTIFICATE TdeCert\r\nWITH SUBJECT = 'TDE certificate';\r\nGO<\/pre>\n
      To verify that the certificate has been created, you can use the sys.certificates<\/code> view, as shown in the following example:<\/p>\n
      SELECT name cert_name,\r\n  certificate_id,\r\n  pvt_key_encryption_type_desc encrypt_type,\r\n  issuer_name\r\nFROM sys.certificates\r\nWHERE issuer_name = 'TDE certificate';<\/pre>\n
      Because the master<\/code> database on my system includes only one certificate, the SELECT<\/code> statement returns only one row, which is shown in Figure 14.<\/p>\n
      <\/p>\n
      Figure 14. TdeCert certificate in the master database<\/p>\n
      The final step is to create a database encryption key in the Test1<\/code> statement. For this, you can use the following CREATE<\/code> DATABASE<\/code> ENCRYPTION<\/code> KEY<\/code> statement, specifying the TdeCert<\/code> certificate you created in the master<\/code> database:<\/p>\n
      USE Test1;\r\nGO\r\nCREATE DATABASE ENCRYPTION KEY\r\nWITH ALGORITHM = AES_256\r\nENCRYPTION BY SERVER CERTIFICATE TdeCert;<\/pre>\n
      When you run this statement, you should receive the following warning about backing up the certificate:<\/p>\n
      Warning: The certificate used for encrypting the database encryption key \r\nhas not been backed up. You should immediately back up the certificate \r\nand the private key associated with the certificate. If the certificate\r\never becomes unavailable or if you must restore or attach the database\r\non another server, you must have backups of both the certificate and \r\nthe private key or you will not be able to open the database.<\/pre>\n
      This completes the steps you need to take to prepare your database to try out the sys.dm_database_encryption_keys<\/code> view. The following SELECT<\/code> statement uses the view to retrieve the encryption state, key algorithm, key length and encryption type of the database encryption key:<\/p>\n
      SELECT encryption_state,\r\n  key_algorithm,\r\n  key_length,\r\n  encryptor_type\r\nFROM sys.dm_database_encryption_keys\r\nWHERE DB_NAME(database_id) = 'Test1';<\/pre>\n
      If you\u2019ve been following along, the statement should return the results shown in Figure 15.<\/p>\n
      <\/p>\n
      Figure 15. Database encryption key for the Test1 database<\/p>\n
      I realize that this is a long way to go about testing the sys.dm_database_encryption_keys<\/code> view, but SQL Server supports only a handful of security-related DMVs, and I wanted to be sure you got to see some of them in action. That said, the TDE example I used here is actually based on one I created for the Simple Talk article Encrypting SQL Server: Transparent Data Encryption (TDE)<\/a>, which provides more specific information about enabling TDE on a SQL Server database.<\/p>\n
      Security-Related Functions<\/h2>\n
      In addition to the catalog views and DMVs, SQL Server provides a number of security-related system functions. For example, you can use the sys.fn_builtin_permissions<\/code> table-valued function to return details about the server\u2019s built-in permission hierarchy or a subset of that hierarchy, as shown in the following SELECT<\/code> statement:<\/p>\n
      SELECT class_desc,\r\n  covering_permission_name,\r\n  parent_class_desc\r\nFROM sys.fn_builtin_permissions(DEFAULT)\r\nWHERE permission_name = 'DELETE';<\/pre>\n
      In this case, the sys.fn_builtin_permissions<\/code> function takes DEFAULT<\/code> as an argument, which means the function will return a complete list of built-in permissions. However, the WHERE<\/code> clause limits those results to the DELETE<\/code> permission. Figure 16 shows the results that were returned on my system.<\/p>\n
      <\/p>\n
      Figure 16. Built-in DELETE permissions on the SQL Server instance<\/p>\n
      SQL Server also provides a number of system functions for working with user and login accounts. To demonstrate how some of these work, first create the sqllogin1<\/code> login and then create the sqluser1<\/code> user in the Test1<\/code> database, based on the sqllogin1<\/code> login:<\/p>\n
      USE Test1;\r\nGO\r\nCREATE LOGIN sqllogin1 \r\nWITH PASSWORD = 'tempPW@56789';\r\nGO\r\nCREATE USER sqluser1 FOR LOGIN sqllogin1;\r\nGRANT SELECT, INSERT, DELETE\r\nON SCHEMA::Sales TO sqluser1;  \r\nGO<\/pre>\n
      The script also includes a GRANT<\/code> statement that assigns the SELECT<\/code>, INSERT<\/code>, and DELETE<\/code> permissions to sqluser1<\/code> on the Sales<\/code> schema. You can verify these permissions by running the fn_my_permissions<\/code> table-valued function within the security context of sqluser1<\/code>:<\/p>\n
      EXECUTE AS USER = 'sqluser1'; \r\nSELECT permission_name \r\nFROM fn_my_permissions ('Sales', 'SCHEMA');  \r\nREVERT;  \r\nGO <\/pre>\n
      The EXECUTE<\/code> AS<\/code> USER<\/code> statement changes the security context to sqluser1<\/code>, and the REVERT<\/code> statement changes the security context back to the original user. Because the SELECT<\/code> statement runs under the context of sqluser1<\/code>, it returns the results shown in Figure 17, which verify the permissions assigned to that user.<\/p>\n
      <\/p>\n
      Figure 17. Permissions granted to sqluser1<\/p>\n
      SQL Server also provides a number of scaler functions for verifying a user\u2019s identity, including the following:<\/p>\n
        \n
      • The SUSER_NAME<\/code> function returns the user\u2019s login identification name.<\/li>\n
      • The SUSER_ID<\/code> function returns the user\u2019s login identification number.<\/li>\n
      • The SUSER_SID<\/code> function returns the user\u2019s login security identification number (SID).<\/li>\n
      • The USER_NAME<\/code> function returns the user\u2019s database user account name.<\/li>\n
      • The USER_ID<\/code> function returns the user\u2019s database user identification number.<\/li>\n<\/ul>\n
        For each of these functions, you can provide a parameter value or you can provide no value, in which case the function uses the current user or login account. You can test this out by calling the functions within the security context of sqluser1<\/code>:<\/p>\n
        EXECUTE AS USER = 'sqluser1'; \r\nSELECT SUSER_NAME() login_name,\r\n  SUSER_ID() login_id,\r\n  SUSER_SID() login_sid,\r\n  USER_NAME() dbuser_name,\r\n  USER_ID() dbuser_id;\r\nREVERT;  \r\nGO <\/pre>\n
        Figure 18 shows the results I received on my system. Notice that the results list the correct user name and the login name associated with that user.<\/p>\n
        <\/p>\n
        Figure 18. User and login names and IDs for sqluser1<\/p>\n
        SQL Server also provides the IS_MEMBER<\/code> scalar function for verifying whether the current user is the member of a specified group or role. For example, the following SELECT<\/code> statement uses the function to determine whether sqluser1<\/code> is a member of the db_owner<\/code> role:<\/p>\n
        EXECUTE AS USER = 'sqluser1'; \r\nSELECT USER_NAME() dbuser_name,\r\n  CASE\r\n    WHEN IS_MEMBER('db_owner') = 1 THEN 'member'\r\n    WHEN IS_MEMBER('db_owner') = 0 THEN 'not member'\r\n    WHEN IS_MEMBER('db_owner') = NULL THEN 'not valid'\r\n  END AS is_member;\r\nREVERT;  \r\nGO<\/pre>\n
        The IS_MEMBER<\/code> function can return only one of three values:<\/p>\n
          \n
        • If 0<\/code> is returned, the user is not<\/em> a member of the specified group or role.<\/li>\n
        • If 1<\/code> is returned, the user is<\/em> a member of the specified group or role.<\/li>\n
        • If NULL<\/code> is returned, the group or role is not valid.<\/li>\n<\/ul>\n
          Figure 19 shows the results returned by the SELECT<\/code> statement. As expected, the user is not a member of the db_owner<\/code> role (unless you added the user to the role).<\/p>\n
          <\/p>\n
          Figure 19. Verifying sqluser1 membership<\/p>\n
          Another fun built-in function is PWDCOMPARE<\/code>, which lets you compare an existing password to a specified password. In this way, you can test for blank passwords or inadequate or common passwords, such as pa$$word<\/em>.<\/p>\n
          To try out the function, first create the sqllogin2<\/code> login with a blank password (not in a production environment):<\/p>\n
          CREATE LOGIN sqllogin2 \r\nWITH PASSWORD = '' , CHECK_POLICY = OFF;\r\nGO<\/pre>\n
          Next, run the following SELECT<\/code> statement, using the PWDCOMPARE<\/code> function in the WHERE<\/code> clause to return any logins with a blank password:<\/p>\n
          SELECT principal_id, \r\n  name login_name\r\nFROM sys.sql_logins\r\nWHERE PWDCOMPARE('', password_hash) = 1;<\/pre>\n
          The first argument passed into the PWDCOMPARE<\/code> function is the unencrypted password, which in this case is an empty string. The second argument tells the function to use the password encryption hash. The function returns a 1<\/code> if the specified password matches the user\u2019s actual password. Otherwise, the function returns 0<\/code>. In this case, the WHERE<\/code> clause specifies that the function must return 1<\/code> for the row to be returned. Figure 20 shows the results I received on my system.<\/p>\n
          <\/p>\n
          Figure 20. Comparing login passwords<\/p>\n
          The SELECT<\/code> statement should return only the sqllogin2<\/code> login. If your results include other logins, you might want to reevaluate your current security strategy.<\/p>\n
          SQL Server Security Views and Functions<\/h2>\n
          SQL Server provides a number of security-related catalog views, DMVs, and system functions in addition to what I covered here, and you certainly should take the time to learn about what\u2019s out there. These views and functions can be very useful when trying to understand and troubleshoot security on a SQL Server instance. The more familiar you are with what\u2019s available, the easier it will be for you to do your job and the more effectively you can ensure the security of your data.<\/p>\n
           <\/p>\n","protected":false},"excerpt":{"rendered":"
          SQL Server contains many built-in views and functions that can be used to understand what is going on \u201cunder the hood,\u201d and security is no exception. In this article, Robert Sheldon explains the security related catalogue views, dynamic management views, and system functions.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143514,53,143530],"tags":[68855],"coauthors":[6779],"class_list":["post-84051","post","type-post","status-publish","format-standard","hentry","category-data-privacy-and-protection","category-featured","category-security","tag-sql-provision"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84051","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=84051"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84051\/revisions"}],"predecessor-version":[{"id":84073,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84051\/revisions\/84073"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=84051"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=84051"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=84051"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=84051"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}