Post reply

database roles or NT groups?

  • 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?
     
     

  • May 25, 2006 at 1:30 pm

    #640167

    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]

  • May 25, 2006 at 1:40 pm

    #640169

    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!
     
     

  • May 25, 2006 at 1:49 pm

    #640172

    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]

  • May 26, 2006 at 7:12 am

    #640297

    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

  • May 28, 2006 at 7:04 am

    #640496

    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...?

  • May 28, 2006 at 9:38 am

    #640500

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

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

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