multiple application roles for a winform application using sql server

my c#.net winforms application which uses sql server 2005 express , would be run by three users, & i want to give differe开发者_StackOverflow社区nt user id & passwords to each of them, so in this case which security feature of SQL SERVER should i use?

1. application roles --can application roles be used for this, or do application roles provide only one credential for the whole application?

2. create a table for userids & passwords

3. create new logins for each user to connect to sql server and grant permissions to each login

my application would be installed on 3 machines, server-SVR , Clients-c-1,c-2. all on LAN with windows xp.

can i create multiple application roles for a single application? i mean that if i have 3 forms in the application. can all 3 forms use different application roles ?

---

You could create roles in SQL Server, let's say YourAppSuperRole, YourAppNormalRole, YourAppReadOnlyRole, and then grant object permissions to each of those roles, e.g. grant insert on sometable to YourAppSuperRole. or grant select on sometable to YourAppReadOnlyRole. Then, however you create users in your database, whether they be SQL Server users or domain users, you add the user to the appropriate role. That's how you'd implement security on the database objects. How you handle the GUI/presentation-layer experience can be disconnected from the back-end. You could identify the Windows domain user, or create application users and require logon using those users, and then govern the GUI behavior accordingly. E.g. you might remove the SAVE RECORD button from the user who you've placed in the Readonly role.

EDIT: I think the simplest way would be to use the Windows domain user. Grant db access to the domain users in the back-end, and add those users to the appropriate back-end roles (to determine what they can see/change in the back-end). Then, in the GUI, adapt the graphical behavior to the domain user who is using your app. If you didn't remove the Save button from the form, the readonly role user could click it but the update would fail. So you'd typically not offer that user the apparent capability to do things they lack permission to do.

---

My guess is you want to have different users run the application with different roles (permissions and abilities and access to features in your app).

I wouldn't use SQL Server security to give them different "application roles". Just define one server user for your application. Then use some way of identifying the user (NTID, or login using some username/password table you have in your database) and use that to configure their experience in your application.