Why Does A/D Account Need SQL Server Login?

Question

I need some help on understanding the differences between Windows and SQL Server Authentication:

We have a SQL Server 2008 set to SQL Server and Windows Authentication Mode.

There is one A/D user -- munis -- that can log into our SQL Server. That user also has a login in SQL Server with Public and Sysadmin roles.

There is another user -- cnorton -- who cannot log into the SQL Server and has no local login in the SQL Server.

I thought that Windows authentication allowed A/D (domain) users to log into a SQL Server. User cnorton has more A/D privileges than does user munis, like Domain Admin and Sysadmin.

So my question is does Windows and SQL Server Authentication mode require a local SQL Server login, and why?

Solution 1

No, mixed mode does not require both, obviously - it requires either one.

Your mistake is in thinking that AD credentials automatically allow SQL server access; they don't, you still have to GRANT this access. Otherwise, how would Sql Server know how much access they have?

Create a new Login for DOMAIN\cnorton and map the user to some databases.

CREATE LOGIN 'DOMAIN\cnorton' FROM WINDOWS
GO
GRANT ALL PRIVILEGES ON YourDatabase To 'DOMAIN\cnorton'
GO

Alternatively, you can add this user to a fixed database role:

USE YourDatabase
GO
ALTER ROLE db_owner ADD MEMBER 'DOMAIN\cnorton'

Solution 2

SQL Server is an application. Windows Authentication and SQL authentication are two different methods for authenticating to SQL Server as an application. Both types require a user principal to be defined/created with some level of access. That's what a login is. You can create a login of either type but you must create the login and grant it some level of access.

Solution 3

You still need to create a login in SQL server for the AD user 'cnorton', but you do not need to create a password for him.

In SSMS, select the server instance you need, and then under logins, right click and hit "New login..."

In the dialog you can press the search button, and search your AD instance for names:

You can see in this case I've selected a test user from my AD instance. This user will be able to login to SSMS or make connections based on the SQL Server permissions you grant to that login just by being logged into Windows with that user.