User management in MSSQL

I'm trying to set up security for my application for users. I am not sure about my logic. Is the following possible:

I want to create 1 login for 'all users' in Active Directory. Then I want an admin (in the开发者_C百科 program self) to choose which user is in which server role (e.g. marketing, sales, ...). Then I want to give those roles permissions to the tables in the database.

Some questions about this:

• Is this safe? The admin of the program has to be db owner?
• Is it possible to list the users by name, instead of the login 'All users', the admin has to be able to place a user in a role after it is created in Ad.
• I use the function SUSER_NAME(), will this return the current user or the groupname 'All Users'

If this isn't the right way to make security, please send me in the right direction.

Thanx!

---

Part of the best solution already lies in your desire to use the Active Directory to authenticate your users. Let it authorise your users as well. Marketing isn't just a database role that specifies access to your tables. It's a department of your company with file shares, mailing lists as well as a role to play in your application. Get your domain admin to make an AD group called Marketing and let them add and remove people as appropriate. Likewise for the Sales department and other departments who touch your database.

When that is done, add the MYDOMAIN\Marketing group as a login on your server and as a user in your database. It will behave more or less like the roles you've described so you can still go ahead and create the roles, add that domain group to the role and grant to the role or grant to that user directly since it identifies a group of people. This also gives you the chance to reconsider your role definitions. Are they really about Marketing people and Sales people or are they CustomerEditor, CustomerViewer, ProfitAndLossViewer roles?

User and group management is a standard function of AD administration; let them do what they do best. It probably doesn't need to feature too strongly in your solution unless you get very poor service from the domain admins, in which case, why use your AD to manage your users?

This very small change will probably free up a lot of your dev time and release you from reinventing a user management facility that already exists and is easy to use.

Good luck!

---

here's what i would do:

create two roles on the database, one users, one admin. then create two security groups in ad, SQL.App.Admin, SQL.App.Users (replace app with whatever your app name is). I would then assign those groups to the roles in the database. after that you can drop users and/or security groups into those two groups.