CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008
Claudio Ferraro
Claudio Ferraro

Reputation: 4721

How to configure correctly Application Roles in SQL Server

I added an Application Role on a SQL Server database and granted to the role the access to one table of the database. From the client I can correctly login with the credentials of the Application Role.

I'm using Windows Authentication. The odd thing is that even without the sp_setapprole statement on the client side the client can without problem read the table. But I want to avoid this.

I'm using this connection string:

Data Source=.\SQLEXPRESS;Initial Catalog=WTC_Database;Pooling=false;Integrated Security=SSPI;

What can be the problem? How to configure the Application Role properly, to deny the other users not logged in the Application Role to access to the table?

Upvotes: 0

Views: 416

Answers (1)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

Since your connection string specifies to use Integrated Security, we know the client's credentials are being used for authentication. The client may have a datareader role or possibly the table is public select.

You need to revoke access to the table to everyone except who should have access. Roles should always grant access, not revoke access. Your system should grant little or no access to data without granting someone roles.

Upvotes: 2

Related Questions