Introduction 🚀

Securing your SQL Server environment involves a meticulous examination of permissions granted to various entities. In this blog post, we’ll explore a T-SQL script designed to query if endpoints have been granted connect permissions. Understanding and managing these permissions is crucial for maintaining a secure and well-configured SQL Server infrastructure.

Requirements 🛠️

Before using the T-SQL script, make sure you have the following prerequisites:

1. SQL Server Endpoints: The script is designed for environments with configured endpoints.
2. Permissions: The account executing the script should have sufficient permissions to query the necessary system views (sys.server_permissions, sys.server_principals, and sys.tcp_endpoints).

T-SQL Script 📜

Use the following T-SQL script to determine if endpoints have connect permissions:

SELECT

    perm.class_desc,

    prin.name,

    perm.permission_name,

    perm.state_desc,

    prin.type_desc as PrincipalType,

    prin.is_disabled

FROM

    sys.server_permissions perm

LEFT JOIN

    sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id

LEFT JOIN

    sys.tcp_endpoints tep ON perm.major_id = tep.endpoint_id

WHERE

    perm.class_desc = 'ENDPOINT'

    AND perm.permission_name = 'CONNECT'

    AND tep.type = 4;

This script retrieves information about connect permissions on endpoints, including details about the principal, permission state, and principal type.

Conclusion 🎉

Checking connect permissions on SQL Server endpoints is a critical step in ensuring a secure database environment. By utilizing the provided T-SQL script, database administrators can easily identify whether connect permissions have been granted to specific entities.

Regularly auditing and managing these permissions is essential for maintaining the integrity of your SQL Server infrastructure. May your endpoints always be secure, and your data remain protected! 🔒

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.