How to grant db_owner permissions to an application role?

Question

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 16, State 1, Line 23
Cannot find the object 'DatabaseName', because it does not exist or you do not have permission.

Research Effort

I'm investigating using SQL Server Application Roles.

• it's like a user (in that it has a username and password)
• and it's like a role

Once connected to the server, your application runs a stored procedure to "login" itself as the application:

EXECUTE sp_SetAppRole @rolename = 'Contoso.exe', @password =
'Tm8gaSBkaWRuJ3QganVzdCBiYXNlNjQgZW5jb2RlIGEgcGFzc3dvcmQuIEl0J3Mgb25seSBhbiBleGFtcGxlIQ==';

Permissions of db_owner

Normally the application logs in as a user who is a member of the db_owner fixed role. The db_owner role has permission:

• to everything
• on every table
• every view
• every stored procedure, function
• for all existing objects
• and all objects that will exist in the future

And while:

• you can place a user into a database role
• you can place a user into an application role

You cannot place an application role into a database role

So, the question then: how to grant my application role all permissions (i.e. to do everything)?

Permissions for a role

So, now is the time to grant permissions to the role. Following this page's suggestions:

GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO [Contoso.exe];

That's interesting and all, but it doesn't grant all privileges - it only grants SELECT, INSERT, UPDATE, and DELETE. I want to grant everything - especially when I don't know what all the privileges are (or could be).

I blindly try:

GRANT ALL ON Users to [Contoso.exe];

and the following appeared in the "Messages" tab:

The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity.

Ok, so granting ALL doesn't grant ALL. That's...terrifying.

So I'm back to:

GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO [Contoso.exe];

Except that doesn't grant everything. For example, I happen to know there's an ability to then go on to grant privileges to others (a privilege that db_owner has). So I have to change my statement to:

GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO [Contoso.exe] WITH GRANT OPTION;

Ok, so that's closer, but it only applies to one table.

I need something that applies to all tables:

EXECUTE sp_msForEachTable 'GRANT SELECT, INSERT, UPDATE, DELETE ON ? TO [Contoso.exe] WITH GRANT OPTION;';

Although, it turns out I missed some privileges:

• SELECT ✔️
• INSERT ✔️
• UPDATE ✔️
• DELETE ✔️
• REFERENCES ❌
• ALTER ❌

Sure, I can update my script:

EXECUTE sp_msForEachTable 'GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER ON ? TO [Contoso.exe] WITH GRANT OPTION;';

But, rather than this cat-and-mouse guessing game: I want to grant ALL permissions.

ALL is nearly all

In the warning above, SQL Server notes that ALL doesn't grant all. But they do document what all does grant:

| Permission | Table | View | SP | Scalar UDF | Table UDF |
|------------|-------|------|----|------------|-----------|
| SELECT     |  ✔️   |  ✔️ |    |            |     ✔️    |
| INSERT     |  ✔️   |  ✔️ |    |            |     ✔️    |
| UPDATE     |  ✔️   |  ✔️ |    |            |     ✔️    |
| DELETE     |  ✔️   |  ✔️ |    |            |     ✔️    |
| REFERENCES |  ✔️   |  ✔️ |    |    ✔️      |     ✔️    |
| EXECUTE    |       |      | ✔️ |    ✔️     |            |
| ALTER      |  ❌   |  ❌ | ❌ |    ❌     |      ❌   | 

CONTROL all permissions

Turns out that they were all of them deceived. For another permissions was created. One permission to rule them all:

• CONTROL

Confers ownership-like capabilities on the grantee. The grantee effectively has all defined permissions on the securable. A principal that has been granted CONTROL can also grant permissions on the securable. Because the SQL Server security model is hierarchical, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope. For example, CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database.

They go on to enumerate the permissions that are implied when you have CONTROL:

• ALTER
• CONTROL
• DELETE
• EXECUTE
• INSERT
• RECEIVE
• REFERENCES
• CONTROL
• TAKE OWNERSHIP
• UPDATE
• VIEW CHANGE TRACKING
• VIEW DEFINITION

That's much better. Rather than having to know all the permissions, I just grant one. And rather than knowing which permissions are applicable to what kinds of objects, I grant just one. And because of the line:

A principal that has been granted CONTROL can also grant permissions on the securable.

I don't have to GRANT WITH GRANT:

-- when you have CONTROL you also get WITH GRANT for free
EXECUTE sp_msForEachTable 'GRANT CONTROL ON ? TO [Contoso.exe];';

To all objects

My issue is that I need to give CONTROL permission to every object in the database. And any time any new object is added, I have to be sure to go back and add it to the application role.

What I need is the thing hinted to by Microsoft:

For example, CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database.

To restate, if you grant CONTROL on a database, then you will have all permissions:

• on the database
  • all objects
  • all assemblies in the database
  • all schemas

That is what I want. I want to GRANT CONTROL permission on the Grobber database to the [Contoso.exe] application role:

GRANT CONTROL ON Grobber TO [Contoso.exe];

Msg 15151, Level 16, State 1, Line 23
Cannot find the object 'Grobber ', because it does not exist or you do not have permission.

I may have nearly solved my problem, only to be stopped at the 1 yard line.

Or, I may be nowhere near. So I ask on S.O.:

How to grant db_owner permissions to another role?

Edit: Warning: Don't use application roles - it breaks everything

When your client logs into an app role, that identity is a persistent property of that connection. And with connection pooling on (as is the preferred and default option in ADO.net, and ADO, and ODBC) that connection stays open for a long time - even after you close the connection.

When your application (i.e. web-server) tries to open a new connection, it grabs one from the connection pool. The first thing that the SqlConnection does is try to reset the state of the connection back to default (using sp_reset_connection).

One of the things that sp_reset_connection tries to do is undo the fact that you are app role user. That is not allowed (because the server doesn't know who you were before). So by using application roles, you will suddenly get errors when you attempt to connect to the server.

The only way to "fix" it is to disable connection pooling.

Which is something that you don't want to do.

So the solution is to not use application roles in a production setting.

Answer 1

"You cannot place an application role into a database role" appears to be the part that's not correct. An (application) role can be added as a member of another role:

EXEC sp_addrolemember 'db_owner', 'ApplicationRoleName';

(From 2012 onwards, this procedure has been deprecated in favor of the new ALTER ROLE .. ADD MEMBER syntax.)

To GRANT CONTROL on an entire database to a role, the following will do:

USE [DatabaseName];
GRANT CONTROL ON DATABASE::[DatabaseName] TO [ApplicationRoleName];

The USE is necessary to bring the role in scope; the DATABASE:: scope qualifier is always necessary when referencing databases.

Having said all that, an application role is probably not a good candidate to grant the broadest of permissions to. The password for it is passed in plaintext unless care is taken to encrypt the connection itself, monitoring and auditing may overlook it, and it's easy to forget to revert when the permissions are no longer needed. That last part is extra insidious because an unreverted app role activation will persist across pooled connections, leaving a connection "stuck" in admin mode. Alternatives include opening a separate connection with new credentials for arbitrary actions and using stored procedures with EXECUTE AS for permissions that can't be GRANTed effectively.

Answer 2

One of the things that sp_reset_connection tries to do is undo the fact that you are app role user. That is not allowed (because the server doesn't know who you were before).
...
The only way to "fix" it is to disable connection pooling.

Undoing the app role can't be done automatically by sp_reset_connection, but it can be done (starting in SQL Server 2005). It just needs to be done manually via sp_unsetapprole. This requires instructing sp_setapprole to generate a cookie (VARBINARY value), capturing it when you execute that system proc, and storing it somewhere (in SQL Server via CONTEXT_INFO or the newer SESSION_CONTEXT, or in the app layer). Then, you just need to pass that value back to sp_unsetapprole before closing the SqlConnection (i.e. returning that connection to the pool).

It's theoretically possible that using EXECUTE AS to switch security contexts would be something that sp_reset_connection could undo since it only requires executing REVERT in T-SQL (no cookie value required), and does not error if there was no context switch to be reverted (REVERT only throws an error if called while the active database is different from when EXECUTE AS was executed). However, I have no time to test this (and it's not a recommended practice anyway).

Normally the application logs in as a user who is a member of the db_owner fixed role.

Now that is certainly not ideal (especially if the login that owns the database has any admin privileges). Yes, it certainly does make some tasks easier as you are effectively disabling all security so that you never get a permissions error, but this approach is highly unsecure, even if you did add the extra step of the password required to set the app role.

The preferred approach (that is very granular and very secure) is to use Module Signing. By signing your modules (stored procedures, scalar functions, multi-statement TVFs, and triggers), you can give them (the modules) any permissions necessary to perform the code that the encapsulate, with no fear of anyone taking those permissions and doing something unintended with them. In this approach, you are granting permissions to the code, not to users (i.e. people / application logins). I have more details as to why you want to move away from app roles and EXECUTE AS, and switch to module signing: PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining. For specific examples, please see:

• Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level
• Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level