Defining Application Roles

Posted on 2014-12-15
Medium Priority
Last Modified: 2014-12-24
Please experts:
in that scenario I can use an application role?

Application roles are used to enable permissions for users only while they are running particular applications
Question by:enrique_aeo
  • 2

Assisted Solution

Ray earned 2000 total points
ID: 40501838
10775 - I guess you're referring to the MS course - Administering Microsoft SQL Server 2012 Databases

I guess we'll need more details for the scenario to address your specific questions.  

In general, if you want your users to work with SQL Server data only when they run a specific application, application roles can be useful tools. For example, suppose you have an inventory-control database and you've built an application that lets users work with inventory. You might not want your users to connect directly to the inventory-control database or to modify sales data. By granting permissions to an application role, rather than to users or database roles, you ensure that users can access inventory data only when running the application.

Application roles can also simplify security administration. Suppose you have many users who access SQL Server through only one application and you don't want to have to manage individual logins and enable database access for each user. An application role requires only one login, which many users can share.

Author Comment

ID: 40501857
I need users to connect from excel; but not using the user name and password to connect from SSMS.

Accepted Solution

Ray earned 2000 total points
ID: 40501903
Private mcnn As ADODB.Connection
Private mrst As ADODB.Recordset

Private Sub Form_Load()
  Dim strConn As String

' Create the connection.
  strConn = "Provider=sqloledb;" & _
    "Data Source=(local);" & _
    "Initial Catalog=Products;" & _
    "OLE DB Services = -2;" & _
    "Integrated Security=SSPI"

  ' Open the connection, and activate the application role.
  Set mcnn = New ADODB.Connection
  mcnn.Open strConn
  mcnn.Execute "sp_setapprole 'ProductApprole', 'password'"
End Sub
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 40504951
Yes you can create an Application role for let the users connect to a database through an application only.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

600 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