1

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? Thanks.

Improve this question
2
  • 1
    It is hard to get the meaning of the sentence without a context, and I guess there is also no generally accepted definition of sign-in vs. login. However in the context of SQL Server a login typically refers to a record related with a system user or sql-authentication credentials and they typically "log in" on connection open. I have never read the action of associating a login with a database user referred to as sign-in, however. – eckes Mar 2 '17 at 21:01
  • @eckes :Sorry, this refers to Active Directory and its administration of Groups and Users. Is that enough? – MSIS Mar 2 '17 at 21:02
1

You sign in (or log on) to SQL Server using a login. Without a login you don't get to connect to SQL Server.

A 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.

Improve this answer
4
  • Sorry, I forgot to include the fact that these are users and groups managed by Active Directory. Could you please explain what this means? – MSIS Mar 2 '17 at 21:03
  • Thanks for your reply.So logins are used only to grant access to SQL server ( either through Windows or SQL Server authentication). How is access granted to specific databases or securables, principals? – MSIS Mar 4 '17 at 22:16
  • yes, logins are a way to provide authentication via either "SQL Server authentication" with a password, or via connectivity with a Windows account. Logins can be granted access to everything inside SQL Server; including databases, and all objects contained in a database. – Hannah Vernon♦ Mar 5 '17 at 1:20
  • Thanks again, very helpful. I am torn between two very good and thorough answers on whose answer to accept. – MSIS Mar 8 '17 at 3:42
2

I think arguing about login vs sign-in is sematics.

Sql server allows you to log in via 2 modes.

  1. SQL authentication: means using a user id and password which is not the same as your domain credentials.

Or.

  1. 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.

Improve this answer
4
  • Thank you. Just how is it one is granted access to databases and other data objects? Is it through roles, by granting the title of user, etc? – MSIS Mar 4 '17 at 22:11
  • You can grant access via a role, or you can grant specific privileges such as select, insert, update, delete, execute to a user or group. msdn.microsoft.com/en-us/library/ms187965.aspx – Sir Swears-a-lot Mar 4 '17 at 23:21
  • Thanks, Peter, but then SQL server login is used for non-windows systems? If not, how could someone even log on to SQL server within Windows without first having logged on to Windows? – MSIS Jun 4 '17 at 22:42
  • LDAP is not exclusively windows. Lookup kerberos and samba: technet.microsoft.com/en-us/library/2008.12.linux.aspx – Sir Swears-a-lot Jun 5 '17 at 3:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.