CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

  • Comments 1

My SQL Server does not have individual windows users established as separate logins.   Instead it has the Domain\SQLUsers 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 Domain\UserName 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 Domain\UserName is not given direct login permissions, login permissions are still handled by the encompassing Domain\SQLUsers group that Domain\UserName 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. 

 

                Machine\administrators          -               Login permissions and mapped to SQL Administrator

                Machine\rdorr                      -               DOES NOT EXIST in syslogins or sys.server_principals

 

create database dbTest

 

Show ownership of database

sp_helpdb dbTest

dbTest            2.73 MB     Machine\rdorr      7     Aug 29 2008   ...

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

Bob Dorr
SQL Server Principal Escalation Engineer

Leave a Comment
  • Please add 4 and 3 and type the answer here:
  • Post