User management in MSSQL

Question

Hello,

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 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!

Answer 1

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.