Post reply

Using Groups to manage privileges in Sql Server 200* R2

  • March 7, 2015 at 7:31 am

    #316060

    Hi I'm still fairly new to Sql Server 2008 R2 (express) , but am enjoying exploring it.

    I have several databases up and running and now a large number of users in each.

    But I notice whenever I write a stored procedure I have to go in and add each user to give them permission to exec it. When the user numbers are low its not a problem but I now have over 20 users on 1 database and its becoming tiresome.

    I have heard of Active Directory on Windows Server but my database is installed on windows xp professional PC.

    Its running well and we don't have a budget to change it.

    I've been searching the internet to see if there is some way I can create a group within sql server give the group all the necessary permissions/ privileges and then add the users to that group thereafter...

    Sounds simple thing to do but I fear as there is no info in the web bout this is isn't going to be that simple!

    Can anyone advise please...

  • March 7, 2015 at 9:23 am

    #1782353

    Hi Tallboy,

    I think your best solution is going to be managing security permissions through the creation of database roles:

    https://msdn.microsoft.com/en-us/library/bb669058%28v=vs.110%29.aspx

    The following article gives you a little more information on Database roles:

    https://msdn.microsoft.com/en-us/library/ms189121(v=sql.105).aspx

    You should be able to create a database role and grant it execute permissions to the stored procedure/s. Then you can add users to the database role.

    Hope this helps! 🙂

    ~Steve

  • March 7, 2015 at 10:17 am

    #1782356

    Hi Steve,

    Thank you for the prompt reply. I was thinking roles might be the solution, but hadn't looked into it fully.

    Cheers for the help.

  • March 7, 2015 at 10:41 am

    #1782357

    Hi Steve,

    Interesting read Steve.

    I am testing niw on my database.

    I have given my users just datareader and datawriter privileges.

    Then I intend to create a database role called DB_SPUser with all my stored proc objects and EXEc privilege only.

    Then add in the users.

    Looking forward to testing with the users on Monday.

    I have read about the diff between databse roles and application roles but am not sure how to link my vb.net application to the application role as I am using windows authentication.

    All advice apprecaited..

  • March 7, 2015 at 11:19 am

    #1782360

    Hey Tallboy,

    Glad I could help! I don't have much experience with application roles, but I did find a nice article detailing some pretty in-depth information about the topic.

    http://sqlmag.com/business-intelligence/mastering-application-roles

    Hope this helps! 🙂

  • March 7, 2015 at 4:05 pm

    #1782364

    Hey Steve,

    Yes that was very helpful article. They make a good point if you add a windows user into a sql database they can then use other programs other than the application to make a connection if they know what they are doing !

    I think I have all my tables etc well protected they wouls not know the names of them

    All users other the the sa have only dataread, datawrite priviledges.

    Other than sql injections what else could they do ! I use parameterised queries and stored procedures, so think I have most things locked down, but how can anyone b sure?

    kind regards

  • March 8, 2015 at 7:53 am

    #1782387

    You sure about putting all your users into database reader role? What That means is every user will have read permissions over each table, unless you explicitly deny. It does not matter if you grant EXECUTE at store procedure level.

    What if you have a table called payroll, same database , which should be accessed only by people in the HR department?

    So, test your application properly and more important, understand your application security well.

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

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