How It Works: Creating An EndPoint Adds An Entry To SysLogins

  • Article

My SQL Server does not have individual windows users established as separate logins. Instead it has the DomainSQLUsers group established as a WINDOWS GROUP login. You can review your mappings using the following DMVs.

select * from syslogins
select * from sys.server_principals
select * from sys.server_permissions

When I used the following CREATE ENDPOINT statement the DomainUserName appeared in syslogins and server_principals.

CREATE ENDPOINT endpoint_test_tsql
AS TCP ( LISTENER_PORT = 5033 )
FOR TSQL()

Principals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).

During CREATE ENDPOINT the AUTHORIZATION is used to establish the ownership of the ENDPOINT object at the server level.

A valid SQL Server or Windows login that is assigned ownership of the newly created endpoint object. If AUTHORIZATION is not specified, by default, the caller becomes owner of the newly created object.

This results in the principal and HasAccess = 0 syslogin entry creation. The Windows User DomainUserName is not given direct login permissions, login permissions are still handled by the encompassing DomainSQLUsers group that DomainUserName belongs to. However, this windows user is the owner of the endpoint and is allowed to control the permissions for the endpoint.

Think of this like the dbo in a database. 

                Machineadministrators - Login permissions and mapped to SQL Administrator

                Machinerdorr - DOES NOT EXIST in syslogins or sys.server_principals

create database dbTest

Show ownership of database

sp_helpdb dbTest

dbTest 2.73 MB Machinerdorr 7 Aug 29 2008 ...

Still no entry in syslogins but select * from sys.database_principals maps the dbo to Machingrdorr SID. The database requires an owner principal just like the endpoint requires a server level principle.

Bob Dorr
SQL Server Principal Escalation Engineer