Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

migrated from stackoverflow.com Mar 5 '13 at 12:55

This question came from our site for professional and enthusiast programmers.

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 '13 at 13:09

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.