logins , users, permissions in sql express 2005 , with diagram , flowchart

Question

I want to create logins & users for my c#.net winforms application which uses sql server 2005 express .& upto my knowledge i have built these steps.

So , I want to ask that are these steps correct or am i making a lot of mistakes.

Also, if am missing any step then please add new steps. Also, in the last step, what should i do, do i have to grant permissions to both ,USERS & SCHEMA, OR to only USER, OR to only SCHEMA.

I am seeking improvements in this model or a new model if anybody can provide. Would be a great help.

I want to GRANT & DENY permissions of DATABASE LEVEL such as CREATE, DROP, ALTER DATABASE ; of TABLE LEVEL such as INSERT , DELETE, ALTER, UPDATE & of COLUMN LEVEL.

Also, is creation of schema necessary?

As far as i know: CREATION OF LOGIN is necessary CREATION OF USER for the above created login is necessary CREATION OF SCHEMA is or is not necessary? i don't know.

Answer 1

I'm changing my whole answer. I really think it's not a good idea to grant uses direct login access to your db. The DB is not the right "choke point" for permissions. Create one user with the total db permissions you will need. Then grant the users features in your application based on their role in the application (tracked in a table in your database).

There are a lot of problems with creating 1 login per user in your database. A few are 1. Maintainance nightmare 2. Now your users can log in to your db directly if they can get connected to it with Management studio or some other tool.

1. To elaborate . . . create one login for your application. Name it something like MyApplicationUser.
2. Create a table in your application to track the roles that each user could be part of. This can also track the features they are allowed to have in your application (though probably in a different table)
3. When the user starts the application, the application is opend using the same db credentials (MyApplicationUser)
4. Now you know who the user is (based on NTID, or application login), and you can give them the features that are appropriate to them in the application)