Solved

Defining Application Roles

Posted on 2014-12-15
4
93 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
[X]
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
  • 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 49

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 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