Stack Overflow
Stack Overflow

Questions tagged [application-role]

Application Role in Databases, especially in MSSQL Server

An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application.

19 questions
8
votes
2 answers

How to grant db_owner permissions to an application role?

How can grant all the rights and privileges of the db_owner fixed database role to an application role? Short Version The command: GRANT CONTROL ON [DatabaseName] TO [ApplicationRoleName]; would be what I want, but it fails with: Msg 15151, Level…
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
4
votes
8 answers

How can I detect condition that causes exception before it happens?

I had no luck with this question so I've produced this simple-as-possible-test-case to demonstrate the problem. In the code below, is it possible to detect that the connection is unusable before trying to use it? SqlConnection c = new…
Ed Guiness
  • 34,602
  • 16
  • 110
  • 145
3
votes
2 answers

JDBC set_approle

I am trying to set an application role through a JDBC connection using a prepareCall. It seems to work ok (i.e. syntax wise) but SQL server 2008 returns this error: Application roles can only be activated at the ad hoc level I am not firing this…
Ayyoudy
  • 3,641
  • 11
  • 47
  • 65
3
votes
3 answers

SQL Server Application Role vs. regular logins and users

What is the advantage of using a SQL Server application role to manage permissions vs. using standard logins/users and granting the necessary permissions to said users? We have been using application roles which require the following…
markltx
  • 689
  • 8
  • 16
3
votes
2 answers

how to enable SQL Application Role via Entity Framework

I'm now developing big government application with entity framework. at first i have one problem about enable SQL application role. with ado.net I'm using below code: SqlCommand cmd = new SqlCommand("sys.sp_setapprole"); cmd.CommandType…
Ehsan Farahani
  • 31
  • 1
  • 3
2
votes
1 answer

Is an application role active on a SQL server connection?

How can i check, if an (and which) application role is active for a connection (DbConnection object) to the sql server? My problem is, that all database objects have access granted for my application role, but when executing a select statement i get…
okrumnow
  • 2,346
  • 23
  • 39
2
votes
0 answers

Reseting SQL Application role after the connection has been closed

I'm using sql application roles from a .net application. I have an issue which occurs when the connection is lost. So as an example I have this block of code which opens a connection, sets the app role, does a select from the database and the…
Sun
  • 4,458
  • 14
  • 66
  • 108
2
votes
1 answer

NLog database target and SQL Server application role

We are replacing a custom logging "framework" with NLog in an existing application. Trouble is the application makes use of SQL Server application roles, which means that every time it opens a connection to the SQL Server it executes the…
bernhof
  • 6,219
  • 2
  • 45
  • 71
1
vote
4 answers

Detecting unusable pooled SqlConnections

When I attempt to set an application role on a SqlConnection with sp_setapprole I sometimes get the following error in the Windows event log... The connection has been dropped because the principal that opened it subsequently assumed a new security…
Ed Guiness
  • 34,602
  • 16
  • 110
  • 145
1
vote
2 answers

Allow application role to perform DBCC CHECKDB

My (C#) application runs, connecting to an SQL server database with an Application Role. However, I need it to run a DBCC CHECKDB, but I get an exception: User 'MyAppRole' does not have permission to run DBCC checkdb for database 'MyDatabase'. I…
komodosp
  • 3,316
  • 2
  • 30
  • 59
1
vote
1 answer

snapshot isolation error using sp_setapprole on a read-only Availability Group replica

I am investigating SQL Server Always On Availability Groups and ran into a problem when setting an application role on the read-only replica database. What really irritates me is the behavior, and I don't know how to interpret the error message. All…
TRue
  • 11
  • 3
1
vote
0 answers

SQL Server cannot set application role after changed password

Originally, my application can change application role by command. exec sp_setapprole 'ARole', {ENCRYPT N'PASS01'} And after I changed the application role's password to 'PASS02', I found exec sp_setapprole 'ARole', {ENCRYPT N'PASS02'} wouldn't…
Eric Yung
  • 733
  • 2
  • 6
  • 12
0
votes
0 answers

Reset SQL Server Application Role Password with Encrypted or Hash Password

I need to automate reset password of database application role. Password must be encrypted or hash form due to security reason. I tried to convert password into hashbytes form and reset application role password but didn't work. SELECT …
SQLHoncho
  • 27
  • 6
0
votes
0 answers

How can I select from sys.dm_tran_commit_table view as an application role user in SQL Server?

We use application roles in our application for accessing SQL Server. We call sp_setapprole to set the app role before we do any other queries from the database. We need to select from sys.dm_tran_commit_table view, for which we need to grant select…
user20130987
  • 1
  • 1
0
votes
1 answer

Constrain LINQ2SQL Datacontext to specific SQL Application Role

In SQL Server you are able to have application role security, through which you are able to for example give specific permissions that originate from specific applications. You can execute sp_SetAppRole() to set the application role but was…
Ray Booysen
  • 28,894
  • 13
  • 84
  • 111
1
2