1

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 found out that to run this command, I can't just grant permission to the Application Role to perform this action, I need to be a member of the db_owner role.

Things I've Tried:

1: In SQL Management Studio, ticking the db_owner box in the properties of MyAppRole for this database. However I get the same exception.

2: Add db_owner role.

exec sp_addrolemember 'db_owner', 'MyAppRole'

But now when I run the application I get this exception at the point of doing the DBCC CHECKDB:

The server principal "MYDOMAIN\MyWindowsUsername" is not able to access the database "MyDatabase" under the current security context.

which is especially confusing because in SQL Managment Studio, I can easily run the command, after logging in using Windows Authentication.

3: creating a stored procedure with execute as Owner

CREATE PROCEDURE sp_dbcccheck WITH EXECUTE AS OWNER
AS
BEGIN
    DBCC CHECKDB (MyDatabase) WITH TABLERESULTS
END
GO

... and given my MyAppRole execute Permission. But I still get the exception:

The server principal "MYDOMAIN\MyWindowsUsername" is not able to access the database "MyDatabase" under the current security context.

...which I also get if I try to execute this stored procedure from SQL Management Studio.

4: Give Application Role "CONTROL" permission (some of these would seem to defeat the purpose of an application role but anyway, grasping at straws)

GRANT CONTROL ON DATABASE::MyDatabase TO MyAppRole

but this results in the original error when the application tries to run the check.

User 'MyAppRole' does not have permission to run DBCC checkdb for database 'MyDatabase'

How do I correctly give my application role permission to do this check?

komodosp
  • 3,316
  • 2
  • 30
  • 59

2 Answers2

0

From MSDN

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

The issue you are having as a result of step 1 might be because the windows user is not mapped to the database you are connecting to.

Kami
  • 19,134
  • 4
  • 51
  • 63
  • When I view the properties of this (Windows) Login, I see that "Map" is ticked for this database, but the "User" is dbo. If I try to change this I get an error saying `Cannot alter the user 'dbo'`. BTW the Windows login is a sysadmin on the server. – komodosp Oct 02 '18 at 11:15
  • @colmde Try - https://stackoverflow.com/questions/8471124/t-sql-to-list-all-the-user-mappings-with-database-roles-permissions-for-a-login/9356725#9356725 to confirm list of roles/permissions. You need to grant the permissions to the correct mapped user/group. – Kami Oct 02 '18 at 12:08
0

I'm not SQL Server DBA expert, but I'm posting my experience.

According to docs DBCC CHECKDB commnad requires sysadmin fixed server role or the db_owner fixed database role. Since application role can't be a member of sysadmin role we can consider only the second option.

We want to operate with a current database, hence we don't need server-level permissions and database-level permissions should be enough. Thus db_owner membership should do the trick and it works perfectly for the database user but for some reason it doesn't work for the application role.

I tried to add all other db roles for app role, checked user mappings that looked good, even tried to execute DBCC TRACEON (4616, -1); that is mentioned here, but it also didn't help, server continued to throw error from your second paragraph.

I managed to make it work only by enabling guest user for current DB, but it breaks the idea of the application role.

I made a conclusion (not sure it is correct) that it is not possible and used regular db role and db users instead

AlbertK
  • 11,841
  • 5
  • 40
  • 36