Application Permissions
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?
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?
ASKER
Thanks for the reply, but my question is should I be setting up the Application and Database Roles to do what I need (see above) or can I do it some other way in the database?
No,You will need to set up Roles accordingly
ASKER
I know how to setup the roles in the database, but I am not sure how to use the application role through the whole C# Windows application without keep executing sp_setapprole for each time I open a database connection and how do I do it for datasets since I don't need to open the database connection?
i am not good with C#..but her's my thought
you do not need to execute the proc every time..once you set up a role with user id and password..Any one who logins with those credentials will have the privileges..i believe you will need to establish a Connection using these credentials
you do not need to execute the proc every time..once you set up a role with user id and password..Any one who logins with those credentials will have the privileges..i believe you will need to establish a Connection using these credentials
ASKER
When you say "Any one who loggins with those credentials" do you mean trying to connect using the credentials of the Application Role or the user?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Through the Windows application, I will try logging in as the user and see if the privileges of the Application Role take effect.
ASKER
Resolution: Instead of setting up the Application and Database Role, we created a new UserID/Password in the Database Security/Logins with sysadmin privileges and changed the C# application's connection string: removing the Integrated Security=True and setting UserID/Password to the new one created.
ASKER
Even though we setup the Application Roles, it did not fix the problem, but we do appreciate the assistance.
In the database -->security --> Roles --> application role ---> new application role --> create a user and password --select the role needed ---> in securables ---Click on search --> specify objects --> Ok ---> object Types --> select the object types and click ok and then using the browse button you can select the stored procedures,tables etc that you want to..
check these links
http://articles.techrepublic.com.com/5100-10878_11-5068954.html
http://www.sqlteam.com/article/using-application-roles