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.