I am trying to understand a paragraph from my notes, re Users and Groups in Active Directory. The phrase is:
Users and Groups Sign in, they do not login.
Now, what I (think I) understand is that logins give access to SQL server, Users are given access to specific databases. But I have not been able to find anything that seems reliable (or barely anything) about sign ins. Could someone please explain to me or give me a ref on what signing in is about and how it is different from logins?
You sign in (or log on) to SQL Server using a
login. Without a
login you don’t get to connect to SQL Server.
login is a SQL Server principal or account, managed by SQL Server, that is used to grant and deny access to objects.
If the login is created using
FROM WINDOWS, the SQL Server login is still an object in SQL Server that can be granted or denied access. The only change is the password for the login is maintained and managed by Windows (or Active Directory), and the account can be disabled, or locked out, etc, without any interaction with SQL Server. This allows separation of duties such that the server or security team manages passwords and access to the network, while the SQL Server team manages what those logins can do inside SQL Server.
I think arguing about login vs sign-in is sematics.
Sql server allows you to log in via 2 modes.
- SQL authentication: means using a user id and password which is not the same as your domain credentials.
- Windows authentication: This can be granted to an individual domain user or a domain group.
Domain groups are a nice way of granting access without having to grant access to individual users each time. For example if a department or team should all have access to a system (e.g HR or Payroll) you can grant access to the team as a whole. As users are added to the team in AD they automatically inherit access to the db via their membership of the group.
This can be overridden by denying individuals access if required. Eg all of HR have access except user xyz.
If SQL is configured to use both modes this referred to as “mixed mode” authentication.
A login gives you access to the SQL instance. You also need to be granted access to individual dbs. Once you have access to sql the method becomes irrelevant. Once you’re in, you’re in.
Technically there is third method related to contained databases. But in my experience this is rare.