locked
managing permissions for multiple DBs RRS feed

  • Question

  • I have one server that currently has about 25 different DBs that belong to
    about 15 different applications.
     
    Im looking for some advice on how to manage the access to these DBs.
     
    I have 3 different types of access, owner, r/w and read.
     
    Is my best option to just create 3 windows groups upon the creation of a DB
    and assign those groups the  respective rights to the DB and then just add
    members to the groups as needed rather than trying to manage it within the
    DB system?
     
    also are there any tools that would allow me to create folders (SSMS would
    be nice) so that I could group these DBs by application or dept?
     
    Its SQL 08 ENT. Do they allow anything like this in the new SQL 2012?
     
    any suggestions/advice would be greatly appreciated.
     
    Thanks
    Justin
     
     

    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, February 2, 2012 1:16 PM

All replies

  • Hi Justin,

    I used to manage permissions through Windows groups. If you create 3 groups for each application and assign the necesssary permissions on the DB server, that may work for you.

    Grouping databses is not really possible with SSMS right now as you may wish to do it. However, you can create server groups on the Registered Servers tab in SSMS.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Thursday, February 2, 2012 1:31 PM
  • hmm maybe I'll go with the windows groups, might be easiest.
     
    also it’s a single cluster that houses a lot of small DBs. we got sick of
    trying to maintain and backup a lot of small DBs. So in this case I don’t
    think the Server group will work.
     
    It seems weird that they don’t give you a way to group DBs. It seems so
    natural to include grouping.
     
    Im going to leave this open for a bit to see if others have suggestions.
     
    Thanks
     
     

    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, February 2, 2012 1:36 PM
  • For example if the user were granted DENY for some table in some group, and then you added him/her to another group that does some manipulation on the same database/table (another application for example)  the user will not be able to read the data from that table as DENY has prioritization  on GRANT  commands
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 2, 2012 1:38 PM
  • I understand that, and if I were to do deny I would do it directly per user
    rather than group. the groups would be more generic, as I said, 3 basic
    roles and go from there.
     
    wish it was easy to maintain multiple DBs from within SSMS.
     
    are there any other tools that would help maintain a large number of DBs
    from a single console?
     
     

    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, February 2, 2012 1:45 PM
  • No that I am aware of (except SSMS)
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 2, 2012 2:04 PM
  • I understand that, and if I were to do deny I would do it directly per user
    rather than group. the groups would be more generic, as I said, 3 basic
    roles and go from there.

    Just be aware that if you go with groups, and for whatever reason you need to deny a user a permission he has inherited from a group membership, you will have to create a Windows-based login for that user, and then run the deny statements against him. If you have a lot of these, that could be a pain.

    Take care.


    SQL Server Database Administrator
    Thursday, February 2, 2012 4:43 PM
  • hmm so right now I use a mix of windows and sql logins... so that actually
    presents a bit of a problem with using windows groups...
     
     

    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, February 3, 2012 8:28 PM
  • If you use SQL logins as well, you may want to group your users within the SQL database rather than using a Windows group. I would suggest using the built in database roles to manage these three levels of access you want to provide, or create three roles manually that will allow you to specify the levels of access you want to grant more precisely. You would then assign your Windows logins, Windows groups, or SQL logins to the appropriate database role.

    Hope this helps.

    Wednesday, March 14, 2012 8:07 PM