Testing Database Roles in development

[itlee]

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.

itlee. MCP\Analyst\Programmer\SQL\.NET\VB\C#

 

[Qik3Coder]

You can't create a test account and have your app use the test account.

the db_denydatareader/writer supercedes all the others all will not allow you reads/writes.

you can't test with just one account.
create several different ROLES then add a test account to each role. Then you can log into the database with the test account(s), to verify permissions. And if your db connection string isn't in a config file then you just found an icky setup issue.

-Sometimes the answer to your question is the hack that works

 

[itlee]

Thanks for your thoughts on this. 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.

Lee.

itlee. MCP\Analyst\Programmer\SQL\.NET\VB\C#