When you first approach the area of security in SQL Server 2005, it can seem very complicated. If you search on the internet you will find thousands of sites who all seem to assume that you mostly know what you're doing but if you're like me, it is hard to know where to start.
Well here is a brief and hopefully newbie-friendly guide to SQL Server security.
Firstly the 'why'. Many programmers appear to take the lazy route when setting up security, you take the path of least resistance and highest privilege so that your application simply works and doesn't hassle you with security errors. What is wrong with that approach? Well if you are the only person who works on the system and it is on a private network, that might be the most practical solution causing the least work but what if your system starts being used by more people? What if you want to expose it to the www? You can retro-fit security, but it is much more time consuming than thinking about it at the start. The 'why' in the bigger sense comes down to the following:

  1. You do not want to accidentally change data in the DB

  2. You do not want somebody to do more (i.e. higher privilege) than they are supposed to do either accidentally or deliberately

  3. You do not want anauthorised people to do anything in your system

For 1. you might have some configuration data for your system stored in a table. Although you are strictly allowed to change it, you generally do not want to and do not want to accidentally change it. You could set the security to be 'select' only. For 2. you might have a table of 'items' which must only be updated by an elevated user and selected by normal users, you would need to set 'select' for a normal user and perhaps 'select, update, delete, insert' for an elevated user. For 3. you need to ensure that everyone who accesses the database is authenticated and no-one who is not can therefore do anything. This might include people who work for your company but are not allowed into this specific database.

The first design you need to undertake is to decide what schemas you want to create. A schema is a logical way to group objects (tables, views, procs) so that you can set permissions on the whole group rather than on individual items. The definition of a schema is a group that does or might need different access permissions depending on the user/user role of the currently logged in user. Do not create too many since this can make maintenance messy. I have used schemas like admin (select for everyone, CRUD for admin users), writeonly (for error logs and the like, only allow select and insert), general (anyone can have CRUD access), readonly (items pulled in from elsewhere and which are not updated in this DB also for views) locked (admin tables that are important and unlikely to have rows deleted so have no delete permission) and cache (tables that are dropped and recreated for caching things). If you notice, each of this has a unique set of permissions applied both for system resilience and for security.

Once I have designed my set of schemas, I need to create them for the database under /security/schemas and then I either need to transfer over all the objects I foolishly created in the dbo schema or create new objects into these new schemas.

I now have to consider the sort of database roles for my users. This is NOT an authorisation system for a web application so I don't need to distinguish at too low a level (e.g. user A can access table A and B but user B can only access table B) otherwise I end up with a lot of maintenance for no benefit, the authorisation at page level should be done in the web app. What I might have however are roles for admin, general, readonly and perhaps a couple of specials like finance who might be able to read stuff that most people can't. These are created under /security/roles/database roles. Once you have created the roles, you can if you want select the schemas and add permissions for the newly created roles. For instance you might given the schema cache full permissions for everyone whereas the admin schema gives full access to the admin role and only 'select' to general and readonly users.

Everybody who logs into the database needs a login. You can have a single login and get your application to login as this single user. The problem is then you lose the ability to make the most of roles and permissions because everybody appears as one 'person' to the DB which will then need access to everything for all users and which defeats the purpose. Ideally you should use windows integrated security if you can and remove the anonymous login ability on the web server. You will then need to create a login for each user on the database server under /security/logins that includes the domain name e.g. DOMAINNAME\username which will connect into active directory and which avoids the need to manage user security in your app. Failing the ability to use windows, you can use SQL server logins which will still need creating and which you will then need to manage in your web app including a secure login page to ensure the login details cannot be 'sniffed'. You create the login in the same place but select "SQL Server Authentication" rather than "Windows Authentication". You would then need to set the password policy information which again is important if you have opted for SQL Server logins.

In order to use these logins in your roles, you need to create a user IN EACH DATABASE to link to the login. The database user can then be added to a role when it is created. Under /security/users, right-click and select "New User". This will bring up a simple dialog which allows you to give the user a name (related to the database, it does not have to match the login), tell it what login this user related to e.g. DOMAINNAME\username and then tick what roles this user is in at the bottom. In more complex setups you might assign the user to own certain schemas, giving them the ability to control and alter these schemas but for now ignore that. You do not have to add the user to a role at this point.

So for user BrinerL you might have:

server login: MYDOMAIN\brinerl
database user: brinerl -> MYDOMAIN\brinerl
belongs to role: admin
admin has permissions: Insert/Update/Select/Delete/Execute on all objects.

It's quite straight-forward really. You can automate the adding of the logins with a stored proc if required so that perhaps the first time somebody logs into the web site, it creates them a login and user and gives them the basic readonly role. If they want more then a DB admin would need to either move their roles in Management Studio or log in to a page that can carry out the necessary work from some sort of front end.