How should developers connect to SQL Server?

Question

We are a team of 4 developers who have access to all servers (dev, test, uat, production), and we are trying to choose the best (safe, reliable, simple, etc.) SQL Server connection method (currently 2008 R2 )

What we are considering:

• Windows / SQL Server Authentication
• What roles?
• Is it the same for all servers or different for production servers?

Please let me know what you are doing and what you recommend.

Answer 1

If you all require the same permissions, it is assumed that you are working on a domain, and the servers are also in the domain.

• Create a development team in a Windows domain.
• Add 4 developers to this group.
• Create Login on each sql server mapped to this group. CREATE LOGIN [domain\group] FROM WINDOWS
• Assign permissions for this entry, depending on what you need.

Then, the new developer must be added to the domain group in order to obtain permissions for all servers. Also, any changes to permissions should be made only once for this group.

To answer your thoughts.

• If possible, you should use Windows Authentication through SQL Authentication.
• The roles that you need and whether they should be the same on all servers can only be answered by IMO, but you should use the principle of least privileges and provide only the minimum permissions required by the developers on the server.

Answer 2

What we use:

Dev / Test Servers - Windows Authentication for Convenience and Simplicity

UAT - SQL Server Authentication (because it is usually located in a different domain)

Production - no direct access - you must use RDP. You don’t want anyone to accidentally connect to production when they thought they were connecting to something else.

Answer 3

Development

• Windows Authentication
• Db_owner role member

test

• Windows Authentication
• End User Role Member

Uat

• Windows Authentication
• Db_datareader role member

prod

• No access

We use only Windows authentication, as we set permissions for the domain group.

Answer 4

We use Windows Authentication
The development team under the PDC.
The developer has full access to the development / testing server and only
selects some on the production server (confidential data tables, even if the data is encrypted), although they are not allowed, like a price list, etc.