Solved

Defining Application Roles

Posted on 2014-12-15
4
91 Views
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
0
Comment
Question by:enrique_aeo
  • 2
4 Comments
 
LVL 7

Assisted Solution

by:Ray
Ray earned 500 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.
0
 

Author Comment

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

Accepted Solution

by:
Ray earned 500 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
0
 
LVL 47

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.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server insert 12 30
Query group by data in SQL Server - cursor? 3 34
SQL - Update field defined as Text 6 17
Insert query into temp tables using Coldfusion 3 22
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

832 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