Posted on 2010-08-31
I have a C# Windows application written to access tables and stored procedures in SQL Server database. The table permissions (Select, Insert, Update, and Delete) and stored procedure (Execute) permissions for the user when accessing the database through the C# application.
However, if the user accesses the same table another way (like Microsoft Access), the permission for the table is Select only.
I was thinking that I need to accomplish this by setting up:
1) Application Role (in SQL Server) for the C# application table (Select, Insert, Update, and Delete) and stored procedure (Execute) permissions.
2) Database Role for the other ways to access the tables with Select only permissions.
Is setting up Application Role and Database Role accurate? If yes, my C# application uses many datasets throughout the application, how do I use sp_setapprole for the whole application? If no, what are the suggestions?