Application Role in SQL Server

To offer better manageability and more security for login and user maintenance Microsoft separated roles into Database roles and Application Roles. Application Roles are mainly used by applications and they do not contain any database users. In this article we will see as how we can create an Application Role in SQL server and use it in a web application.

So let's get started:

First, we need to create an Application Role and provide password to it as below to the database where we need to access the data from:

 

CREATE APPLICATION ROLE approle

WITH PASSWORD = 'aaa.123'

, DEFAULT_SCHEMA = dbo;

GO

The above create approle will be visible under that Database, under Security->Roles->Application Roles

After creation we need to provide the permissions to this role as shown below:

 

Here we will give minimum permissions to the role and see that only those permissions are applied. After clicking on Properties choose Securable from the options. Thereafter click on Search and below will appear:

Choose All objects of the types… and below will appear after clicking on OK

Choose tables from the option and all tables will be visible. In this, we will give permission for Product table under schema dbo for selection some of the columns.

Click on the column permissions and let's give access to some columns (Name, ProductID and StandardCost) for this role

Click OK and this role is all done and has appropriate permissions that we wanted to assign.

In order to access this role we would need to create a login, say we call it New. So under Security->Login we create a new login

Select New Login:

Just for the demonstration I am not enforcing Password policy above, but it is recommended to enforce it.

After this we will give access for AdventureWorks Database to this user as below

USE [Your_DB]

GO

 

CREATE USER [New] FOR LOGIN [New] WITH DEFAULT_SCHEMA=[dbo]

GO

 

We will not assign any schema or give any permission to this user in adventureWorks, as it will use the permission of Application role that we have just created. Once all above is done we need to use this in web.config in web app as below:

We will use the login New to connect as below. Web.config connection code:

<add name="yourConnectionString" connectionString="Data Source=yourIP, 2433;Initial Catalog=AdventureWorks;User ID=New; Password=new.123"
providerName="System.Data.SqlClient" />

For the purpose of demonstration we are just creating a Default.aspx where we will have a GridView as below:

Default.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="
http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>

On the code behind we will assign the role approle that we created above and get the results in gridview.

Default.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string conString=WebConfigurationManager.ConnectionStrings["yourConnectionString"].ConnectionString;
using (SqlConnection conn=new SqlConnection(conString))
{
SqlCommand cmd=new SqlCommand();

//note that
cmd.CommandText = @"EXEC sp_setapprole '
approle','aaa.123'
, @fCreateCookie = true;
Select Name, ProductID, StandardCost from dbo.Products";

//remember over here we did not give access for Product number to this role,

//therefore if we have Select ProductNumber from dbo.Products, it would produce an error.
cmd.Connection = conn;
conn.Open();
GridView1.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection);
GridView1.DataBind();
conn.Close();
cmd = null;
}
}
}

Tags: , , , , ,

SQL Server