By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,463 Members | 3,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,463 IT Pros & Developers. It's quick & easy.

Securing local MSDE applications

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
> 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.
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

"Danny Liberty" <dl******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com... 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

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.