November 19, 2007 at 3:23 am
Hi folks, can you give me advice about one little problem ?
I have production database on SQL server 2005 in compatibility level 80.
This database has only 2 application roles - users and admins. Both roles has desired permissions on database objects (tables, functions, etc.). In this database is only one issued table, which has already rights for select, update, delete ..etc. for both app. roles. In spite of that, when I use windows authentification method in application with user that is only member of users app. role, I cannot select this issued table. (It's only simple select, no fuction was called. I have traced it from SQL profiler)
With admins app. role there is no problem with any operation. As I looked in security properties for app. role - users, it has the same permissions as role - admins. Where is the problem ?
One more noticed thing, as I set the permission "select" for database role - public, the problem disappeared. Strange thing on this issue is that, other tables has no security problems with both app. roles. :crying:
November 20, 2007 at 6:05 pm
If the permissions are assigned to an app role, that app role must be activated via sp_setapprole. However, doing so causes the user's security context to become the app role and that's it. In SQL Server 2000, once an app role is set, the only way to get rid of it is to close the connecton.
It sounds like you want user-defined database roles, not app roles. Those grant the security automatically and don't need to be activated.
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply