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.

I just entered a development project where the developers use the same SQL Server login to connect to a central database for their work. I have never come across this before, as each dev usually has their own login.

Are there any benefits to using this model?

Is there a limit to the number of simultaneous connections for the same SQL Server login?

for all work or is this a connection string that the application itself will use to connect to the database?
–
swasheckApr 11 '12 at 19:18

3

The advantage is it lets the DBA be lazy and not manage individual access.
–
JNK♦Apr 11 '12 at 19:28

3

Shared logins have been prohibited as a matter of policy @ most places I've worked.
–
Eric HigginsApr 11 '12 at 19:45

2

I have seen a lot of this in my experience and I do not recommend it. Consider sp_who2 output with numerous 'generic-sql-login' connections appearing. What happens if there is an issue and a need to id a specific developer? Generic logins are of no use.
–
jl01Apr 11 '12 at 20:15

2

This way when somebody asks 'who changed table Foo?' then you can ask 'login1 did it!'
–
Remus RusanuApr 11 '12 at 20:33

2 Answers
2

While there is no limit on the number of simultaneous connections by a single login, other than the usual @@MAX_CONNECTIONS value, using one login for multiple users or developers is generally frowned on because doing so makes it difficult or impossible to limit permissions on a per-person basis. (You may still be able to identify/contain people based on something like a workstation name, but this would require much hackery on the DBA's part, while simply using seperete logins addresses the issue.) Such "well-known" logins also have a way of getting hard-coded into things, along with the well-known password, and then developers and users become very resistant to ever changing that password. With so many ways to get onto a corporate network these days (a rogue LogMeIn running somewhere, for example) being able to turn database access off via an AD group is beneficial.

Using a single login for a web application, which can have many simultaneous users, is more common. It might not be feasible to give out SQL Server logins or AD logins to individuals for large sites (for example, facebook). In that case, there is a benefit in that IIS can use connection pooling.

I haven't worked in an environment where it was standard operating procedure to use a shared login like that for many years, although there were some smaller/older projects where a shared login was used (and sometimes abused).

For a development environment it's common that everyone has the same privileges. Using active directory this is often done with Security Groups. A poor man's version would be to give everyone the same SQL Auth account. The only benefit really is ease of administration for the DBA. I'm not saying it's the right thing to do; just guessing at motivation. There is a DBA right; or someone who wears that hat?