none
How to identify "special principal" in sys catalogs

    Question

  • Is there a property to which can be used to identify "special principals" in sys catalogs?

    I have trouble deploying databases using visual studio database edition with msbuild.

    Somehow, objects gets created in the middle of deployment which alter the design and prevent crash deployment.

    The solution I found is to remove access to db users to prevent the creation of those objects during the deployment.

    Using the following query:

    select rolep.name, memp.name from sys.database_role_members rm
    JOIN sys.database_principals rolep ON rm.role_principal_id = rolep.principal_id
    JOIN sys.database_principals memp ON rm.member_principal_id = memp.principal_id

    I can extract all users and then generate calls to sp_droprolemember to remove their access.

    Of course, this does not work for the user "dbo" which yields the error

    "Msg 15405, Level 11, State 1, Procedure sp_droprolemember, Line 76

    Cannot use the special principal 'dbo'."

    Aside of relying on the role names, Is there a property which can be used to identify "Special principals"?


    • Edited by Antoine F Friday, May 23, 2014 7:40 PM
    Friday, May 23, 2014 7:39 PM

Answers

  • You would need to adapt your code to the situation. In many cases, you need to exclude dbo. If you want to drop logins in general, you can use the type column in sys.database_principals, so that you don't attempt to drop logins tied to certificates. (Unless you want drop them that is.) You should probably also keep principals of which the name start with ##, as they are various built-ins.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 23, 2014 9:20 PM
  • If it was not clear: you will need to hard-code for dbo in many situations. But what is a "special principal" depends on the context. For instance, you can easily drop guest as member from a role, but you can't drop guest from the database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Antoine F Thursday, May 29, 2014 9:09 PM
    Monday, May 26, 2014 9:09 PM

All replies

  • You would need to adapt your code to the situation. In many cases, you need to exclude dbo. If you want to drop logins in general, you can use the type column in sys.database_principals, so that you don't attempt to drop logins tied to certificates. (Unless you want drop them that is.) You should probably also keep principals of which the name start with ##, as they are various built-ins.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 23, 2014 9:20 PM
  • Thank you Erland,

    The type is the same for "dbo" than my window users.  Reading your answer, I will see if I can rely on certificates to figure some kind of rule to determine which users are "special".

    If I can't find a property to identify those kind of users, maybe I could rely on some documentation to identify them.  The term "special principals" used in the error messages does not seem to be used in documentations.

    Monday, May 26, 2014 1:52 PM
  • If it was not clear: you will need to hard-code for dbo in many situations. But what is a "special principal" depends on the context. For instance, you can easily drop guest as member from a role, but you can't drop guest from the database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Antoine F Thursday, May 29, 2014 9:09 PM
    Monday, May 26, 2014 9:09 PM
  • Thank you Erland,

    I hear you, "special permission" is the term the error message throws when attempting to drop roles for dbo but it does not seem to correspond to anything when searching for documentation.

    I will do what you propose.  If I find something more elegant, I'll let you know.

    Thanks again.

    Thursday, May 29, 2014 9:08 PM