The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Server security setup

SQL Server security for developers

I am trying to organise our SQL Server security and want to see if anyone has any advice for me.

<< Our different types of users >>
1. apps - web apps & other apps that need to connect to the db
2. developers - they need to develop on the dev server, deploy to the staging server, deploy and do emergency bug fixes on the live server
3. office users - write ad hoc queries against the data. make changes to the data, but not the schema.
4. admin user - sys admin type stuff that shouldn't be used that often

<< Current setup >>
ApplicationUser - SQL login that all apps use. It only has access to the stored procedures. Works very well and not looking to change this.
AdministratorUser - all our other users use this. It is dbo for every db and member of the sysadmin server role.
BUILTIN\Administrators - users are also members of the Windows Administrator group and connect using this. Like AdministratorUser, this is a member of the sysadmin server role.

<< What I want >>
I would like to keep ApplicationUser and AdministratorUser (with their permissions), but stop using AdministratorUser for general use. I want developers and office users to connect through Windows authentication and them have the appropriate permissions.

What I'm after is some advice on how to do this. Do I group users into Windows groups and then assign permissions to those Windows groups in SQL Server? Do I use SQL Server roles? What is a good way to group users permissions in SQL Server?

To put things into perspective, we are a team of 3 developers with an extra 2 office users that write ad hoc queries.

Any advice would be really appreciated.
Craig Send private email
Monday, June 23, 2008
Map windows group to SQL role
Give roles permissions.

I'd say
"Office" role has select permissions

"Developer" role has at least dbo permissions, but it may need additional stuff for things like taking a backup prior to deployng changes.  Restoring datbases, etc.
Roger Willcocks Send private email
Friday, July 18, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz