2

Is there a way to distinguish between a system role/user and a user defined role/user? As system role/user I understand a role/user which is installed/assigned implicitly at the time the database has been created. As user defined role/user I understand a role/user created "manually" afterwards.

The only way I have found is by looking at the result of

select * from sys.database_principals order by create_date, is_fixed_role desc

or by comparing the result with a freshly created database. Is there another way?

1 Answer 1

5

The column 'is_fixed_role' on sys.database_principals is your answer, check the MS document:

If 1, this row represents an entry for one of the fixed database roles: db_owner, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader, db_denydatawriter.

Just use

SELECT * FROM sys.database_principals WHERE [type]='R' and [is_fixed_role]=1
2
  • These are the fixed roles. However the attribute is_fixed_role seems not to be exhaustive: what about the standard users like dbo or guest? What about sys or public? I want to detect them too.
    – Claude
    Mar 21, 2015 at 9:52
  • Right. There's nothing that distinguishes those. You probably need to pre-identify them and add them in a where clause.
    – Salvador L
    Oct 10, 2015 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.