Post reply

Testing Database Roles in development environment

  • June 25, 2008 at 3:37 am

    #96048

    I need to test the security of the database roles I have created for a .net application by adding myself to a role and trying to access the data from the application.

    I have the dev db on my PC, therefore I am accessing the local sql server 2005 as part of the BUILTIN\Administrators group. This naturally gives me the sysadmin role. To get round this I have then added my windows account as a logon and mapped it to the database with the public role, then logging on as sa I remove BUILTIN\Administrators from sysadmin.

    Problem #1: I can still access all the data! I thought this would stop me accessing all the data as my only role membership is public.

    Problem #2: If I add myself to db_denydatareader, I get the permission denied error. Which is expected. I then add myself to one of the database roles I created, I still get the permission denied error!

    How can use my own login to test the data access for the db role?

    Thanks,

    Lee.

  • June 25, 2008 at 7:45 am

    #833958

    Personally, I'd create a separate login, make sure it is part of the domain groups that you want to test against, and not part of any other domain groups, add that to the DB group, and then log in using that. You can do that by using Run As when you fire up Management Studio, or by creating a virtual machine on your test box, and logging into that with the login you want to test. (Using a virtual machine allows you to test even more thoroughly, and it's actually quite easy to set up and convenient to use.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 7, 2008 at 2:16 am

    #838722

    Thanks for the reply. I thought I would let you know how I managed to do this.

    I changed the connection string to use a SQL Server login and then just changed the db role this login belonged to to test each set of permissions.

    I agree creating a test nt account would be most useful, which is something I am going to set up for the future.

    Thanks,

    Lee.

Viewing 3 posts - 1 through 2 (of 2 total)

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