database roles or NT groups?

Question

database roles or NT groups?

joshcsmith13

Hall of Fame

Points: 3381
More actions
May 25, 2006 at 1:19 pm

#366270

I'm trying to decide how to implement security for a group of users (consultants actually) who will be developing some applications for existing databases. They will need data read/write access and even the ability to create sprocs. There is some debate over whether it is easier to just use the Windows Group as a Login and assign all necessary permissions to the Group/User directly, or to create a database role and then stick the NT group in the database role, allowing for future members outside of the NT group to have the same access. It sounds like creating the database role will take more effort to maintain, but I'm not sure.
What does your experience say? Does anybody really use database roles when NT [group] authentication is available?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

I'd suggest that if you are only putting them in that group for the sake of SQL sercurity and have no other security or distribution access levels controlled by that NT group membership then I'd suggest using the NT group. That way later on you can just add more people to that group when they need access to the database. However, if it is used as a distribution list and other security settings like network shares, printing security, etc. you need to think that someday you may want to give someone right to the database but not the network shares you should use the role and give that group the role.

When you speak of a windows group do you mean a domain one, or a local machine group? you could get away with creating a local machine group, granting that access to whatever you need then you could add other groups and users to that local group. Think back to the old days of resource domains and such and do it that way.

To help us help you read this[/url]For better help with performance problems please read this[/url]

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 2

Thanks for the response Luke! I am referring to a Domain group, and I think that the (current) sole purpose would be to administer SQL access. The slight complication there is that I only control the SQL environment - not the network/domain, so I have to work through another person. Sorry, but you lost me with the resource domains comment. I probably haven't been doing this long enough to know what that was. Thanks again!

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

Resource domains came from Windows NT and were things that most companies started to get away from once active directory came around. If you're interested you could read about them here look for the section about the Four domain models and why to use what when.

To help us help you read this[/url]For better help with performance problems please read this[/url]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Something that you touched upon is not having control over the membership of the Windows group. If you grant a Windows group access to your SQL server, bear in mind that your network administrator can then decide who gets access. If you don't want that, it's best not to use Windows groups.

John

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 5

I did do this set up one sql login...not nt and when i registered to sql for this login...i could see all the databases...when i only gave him one....if you click on the other databases thats ok it said deny...

is there a way that they cannot see the databases listed just their one...?

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 6

Use Windows Groups and place in a role. You never know if you'll hire one of these consultants and need the same access. Then you'll need to potentially redo permissions.

Or another group may need these SQL permissions with different Windows permissions and the same thing happens. It's one more step, create the role. Not worth debating. If you never need it, it's not much work wasted.