Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to protect a database from being accessed from Excel?

Posted on 2000-04-07
Medium Priority
Last Modified: 2008-02-01
We have lots of pc based applications developed using VB. The programs resides on the our NT server and accessed by multiple users.  Once a user a allowed to access the program, he has also access to the database location. This exposes the databases since this can be accessed using the excel or any other compatible database.
Question: what options do we have to protect the database from being viewed, amemded deleted and copied.

Question by:elmerpevidal
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

crsankar earned 600 total points
ID: 2695734
This may help you.

Application Security and Application Roles

The security system in Microsoft® SQL Server™ is implemented at the lowest level, the database itself. This is the best, most robust method for controlling user activities regardless of the application used to communicate with SQL Server. However, you may have situations when security controls must be customized to accommodate the special requirements of an individual application, especially when dealing with complex databases and databases with large tables.

Additionally, you may want users to be restricted to accessing data only through a specific application without the ability to access data directly, for example using SQL Server Query Analyzer or Microsoft Excel. This prevents a user from connecting to SQL Server using an application such as SQL Server Query Analyzer and executing a poorly written query, which affects the performance of the whole server.

SQL Server accommodates this situation through the use of application roles. The fundamental differences between standard and application roles are:

Application roles contain no members. Users, Microsoft Windows NT® groups, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user’s connection through a specific application(s). A user’s association with an application role is due to being capable of running an application that activates the role, rather than being a member of the role.
Application roles are inactive by default and require a password to be activated by using the sp_setapprole system stored procedure. The password can be provided by the user, for example, through an application prompt, but it is more likely that the password is incorporated within the application. The password can be encrypted as it is sent to SQL Server.
When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only by virtue of permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of SQL Server.


Important It is necessary for a connection to lose default permissions applied to the login/user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role to ensure that all the functions of the application can be performed. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked for the user to successfully use the application. Application roles overcome any conflicts with user’s default permissions by temporarily suspending the user’s default permissions and assigning them only the permissions of the application role.


Application roles allow the application to take over the responsibility of user authentication, rather than SQL Server. However, because SQL Server still needs to authenticate the application when it accesses databases, the application must provide a password because there is no other way to authenticate an application.

If ad hoc access to a database is not required, users and Windows NT groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, assuming access to the applications is secure, standardizing on one system-wide password assigned to an application role is possible.

There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or the SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to SQL Server, when the role is activated.

When an application user connects to SQL Server using Windows NT Authentication Mode, an application role is a good way to set the permissions the Windows NT user has in a database when using the application. This allows Windows NT auditing of the user account and control over user permissions, while they use the application, to be easily maintained.

If SQL Server Authentication is used and auditing user access in the database is not required, it can be easier for the application to connect to SQL Server using a predefined SQL Server login. For example, an order entry application authenticates users running the application itself, and then connects to SQL Server using the same OrderEntry login. All connections use the same login, and relevant permissions are granted to this login.


Note Application roles work with both authentication modes.


As an example of application role usage, a user Sue runs a sales application that requires SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in database Sales to work, but she should not have any SELECT, INSERT, or UPDATE permissions when accessing the Products or Orders tables using SQL Server Query Analyzer or any other tool. To ensure this, you could create one user-database role that denies SELECT, INSERT, or UPDATE permissions on the Products and Orders tables, and add Sue as a member of that database role. You can then create an application role in the Sales database with SELECT, INSERT, and UPDATE permissions on the Products and Orders tables. When the application runs, it provides the password to activate the application role by using sp_setapprole, and gains the permissions to access the Products and Orders tables. If Sue tries to log in to SQL Server using any tool except the application, she will not be able to access the Products or Orders tables.


Author Comment

ID: 2697499
Adjusted points from 100 to 200

Expert Comment

ID: 2697869

Some of the ruleas are :

1) Never use 'sa' (or dbo) account to connect to SQL server.

2) Create different levels of user groups on database level and assign database users minimum rights in order to perform required tasks. This you will have to coordinate and test with your VB developers.

3) Also it is important to know what type SQL login environment you are having. Is it integrated, standard or mixed. If it is integrated you will also have to involve your NT user administrator.

Hope this helps.

Expert Comment

ID: 2698296
An option which works well is to only use stored procedure. That way users don't have direct access to tables. They do have execute on the stored procedures, but they would need to know the names and what needs to be passed (and most MIS people can't figure that out ;)


Expert Comment

ID: 2705831
Views are used for this purpose.  You can grant permissions to a view and not to the underlying tables.  The views can query just the columns needed.  They are used the same as a table.

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question