Sql-docs: Fixed Database Role Permissions

[BeginTry]

Since sp_dbfixedrolepermission is in maintenance mode, what are the corresponding system stored procs (or DMVs) that should be used instead for current or future development work?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 631b7a9d-b7c7-9ad4-b3ec-903fc0bb9e1a
• Version Independent ID: 35474195-6dc4-ddf9-ec0c-9c0473c68b61
• Content: sp_dbfixedrolepermission (Transact-SQL) - SQL Server
• Content Source: docs/relational-databases/system-stored-procedures/sp-dbfixedrolepermission-transact-sql.md
• Product: sql
• Technology: system-objects
• GitHub Login: @stevestein
• Microsoft Alias: sstein

[msebolt]

@BeginTry Thank you for the question, assigning this to the author for review.

[rothja]

@BeginTry Thanks for the feedback. @stevestein is out of the office, so I'd like to ask @VanMSFT to take a look this week. We might pull other people in as needed as well. Thank you for your patience!

[rothja]

reassign:vanmsft

[VanMSFT]

Hi @AndreasWolter - Can you please help answer this question? Thanks!

[AndreasWolter]

So after a short exchange with StevenGo:
You can identify what permissions the fixed database role has with the has_perms_by_name and fn_my_permissions functions

• that is really all we have right now

[srutzky]

@AndreasWolter , et al.:

You can identify what permissions the fixed database role has with the has_perms_by_name and fn_my_permissions functions

No, not really:

• HAS_PERMS_BY_NAME (emphasis added):

  Evaluates the effective permission of the _current user_ on a securable.

• sys.fn_my_permissions (emphasis added):

  Returns a list of the permissions effectively granted to the principal on a securable.
  ...
  This table-valued function returns a list of the effective permissions held by the _calling principal_ on a specified securable.

Since it is not possible to impersonate / "execute as" a role (server or db), there is no (easy) way to use those functions to get their permissions.

If we look at what the deprecated system proc is doing via:

EXEC sp_helptext N'sp_dbfixedrolepermission';

we see that for getting permissions for _all_ db roles, it executes:

select distinct 'DbFixedRole' = usr.name, 'Permission' = spv.name collate catalog_default
    from sys.database_principals usr, sys.role_permissions spv
    where usr.principal_id >= 16384 and usr.principal_id <= 16393 and
        spv.type = 'DBR' and spv.low > 0 and
        ((usr.principal_id = 16384 and spv.number >= 16384 and spv.number < 16392) or
        (usr.principal_id <> 16384 and usr.principal_id = spv.number))
    order by usr.name, spv.name collate catalog_default

Of course, we can't run that ourselves because sys.role_permissions appears to be a system table that is not accessible by us regular folk.

You might want to take a look at the Database-Level Roles page as there is an image that lists all of the permissions for all of the db roles. I would use that info.

The documentation for sp_dbfixedrolepermission states that it was not updated for changes made in SQL Server 2005. Looking at the results for db_backupoperator, db_datareader, and db_datawriter they do seem to match the permissions image, but db_accessadmin is different. That one returns sp_dropuser, sp_grantdbaccess, and sp_revokedbaccess from the proc, yet shows as having CREATE SCHEMA, ALTER ANY USER, and CONNECT in the image.

So, perhaps a reference to the permissions image on the Database-Level Roles page could be added to this page (i.e. sp_dbfixedrolepermission )?

Take care,
Solomon...
https://SqlQuantumLift.com/
https://SqlQuantumLeap.com/
https://SQLsharp.com/

[VanMSFT]

@srutzky - Hi again Solomon! Thanks for your input! I'll go ahead and link the Database-Level Roles page.

@AndreasWolter - Andreas, please let us know if you have any additional input based on Solomon's comments. Thanks!

[AndreasWolter]

Hello @srutzky You are right about the fact that one cannot directly use the role to check its permissions. Which is why I said "that's all we have"
On db_accessadmin: Although I have not been around when this decision was made, I can guess that sp_dropuser, sp_grantdbaccess, and sp_revokedbaccess have been left out of the image because they are deprecated. SO I will leave it as it is.
Yes, @VanMSFT , go ahead . Also in the top paragraph please ad a space here: "seePermissions (Database Engine)."

Thank you both