0

What is the difference between

  • adding a domain account login to an SQL Server (2008 R2) instance then add a corresponding user to a database
  • adding a domain account user to a database without adding as a login first

Is there any advantage, benefits, etc of one over the other. I notice when the latter strategy is used (i.e. user only), sp_helpuser has a NULL LoginName

When the former is used (i.e. login and user) apart from say being able to assign an instance level role (as opposed to simply a database role) is there any problem with just adding a user without login (the latter)

Improve this question
1
  • 1
    I don't see any other problem except you won't be able to login without a login ;). What's the purpose of a user if not to login and have permissions? If it's only for ownership than schemas are used, anyway, so users are an unnecessary layer.
    – Marian
    Mar 5, 2013 at 13:09

1 Answer 1

Reset to default
2

Unless you're referring to contained databases (which are new in SQL Server 2012), you can't use the second approach. Whenever a person authenticates to SQL Server, they are authenticating against a particular server login (or possibly multiple logins if you're creating logins for domain groups). This server login can then be mapped to one or more database users to allow access to different databases.

Unless you're doing some tricks with EXECUTE AS or using application roles, there's no way for somebody to authenticate directly as a database user without a server login being mapped to it.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.