sql server (alternate): Hi,

I am writing an application that uses MSDE to store data.
Both application & MSDE run on the same computer.
I want to regulate the operations done on the DB by the user. For
example, I don't want to allow "standard" users to delete records,
update certain fields, etc...
I can regulate these rules within my program, but what if the user runs
MSDE query for example on the DB and fetches the list of users &
passwords from the DB ?
In other words, I need to make sure only the application has access to
the DB. This seems like a common type of problem but I haven't been
able to find any solutions.
Any suggestions would be greatly appreciated.

Danny

[quoted text, click to view]

One approach is to employ application roles. This allows users to connect
using their regular login but with limited permissions until the application
role is activated.

You can create an application role using sp_addapprole and grant the needed
permissions:

USE MyDatabase
EXEC sp_addapprole 'MyAppRole', 'MyAppRolePassword'
GRANT ALL ON MyTable TO MyAppRole

You can then enable an app role from within your application.

EXEC sp_setapprole 'MyAppRole', 'MyAppRolePassword'

With this approach, users are limited to their regular SQL Server
permissions outside your application. Another method is to create and use a
standard SQL login for database connectivity within your app that is unknown
to your users.

See the Books Online for more information.


--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]