HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL Server db user mapped to windows login not in SQL Server logins

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
mappedsqluserloginloginswindowsservernot

Problem

I am on a SQL Server 2008 where I login with DOMAIN\USER_A

On my database I can see this login is mapped to the user dbo of that database.
What I don't understand is that DOMAIN\USER_A is nowhere to be found in the SQL Servers Security Logins.

CURRENT_USER reports dbo
SYSTEM_USER reports DOMAIN\USER_A as do SUSER_NAME

I can't find DOMAIN\USER_A in the sys.syslogins? What is going on? Can a database user have its own login circumventing the SQL Server?

Solution

The only way a login has rights to a server is if those rights have been granted. The question is likely: How did these rights get granted?

If DOMAIN\USER_A is a member of some Windows group, then through the rights granted to the group the DOMAIN\USER_A login exists in the server. This is true even if there is no individual entry in sys.database_principals or sys.server_principals for that login.

You can look for the permission paths used by a login as follows:

EXEC xp_logininfo 'DOMAIN\USER_A', 'all'


You might find several permission paths for a login.

Code Snippets

EXEC xp_logininfo 'DOMAIN\USER_A', 'all'

Context

StackExchange Database Administrators Q#48731, answer score: 4

Revisions (0)

No revisions yet.