Why does assigning sysadmin role affect database user roles in SQL Server?

Data and Databases
1

I’m having trouble understanding SQL Server user roles. Here’s my situation:

I created a login called ‘dbuser’ and mapped it to a database user with the same name. This user has a database role called ‘customrole’. I can see this role when I run a query to check user roles.

But here’s the weird part. When I give the ‘dbuser’ login the ‘sysadmin’ server role, the query doesn’t show the ‘customrole’ anymore. If I take away the ‘sysadmin’ role, everything goes back to normal.

I’m confused. Why does adding the ‘sysadmin’ role make the database role disappear? Am I missing something? Can someone explain what’s going on here?

Here’s a sample query I’ve been using to check roles:

SELECT u.user_id, u.name, CASE WHEN IS_ROLEMEMBER(u.name) = 1 THEN 'Yes' ELSE 'No' END AS is_member
FROM sys.database_principals u
WHERE u.type_desc = 'DATABASE_ROLE'
AND IS_ROLEMEMBER(u.name) = 1

Any help would be appreciated!

2

yo, sysadmin is like the boss role in SQL server. When u give it to a login, it kinda overrides other roles. thats why ur query doesnt show the custom role anymore. sysadmin has all perms by default, so the database doesnt bother checking other roles. its not gone, just hidden behind the sysadmin powers. hope that helps!

3

The sysadmin role in SQL Server is a powerful server-level role that grants unrestricted access to all server and database operations. When a login is assigned this role, explicit database role memberships, such as a custom role, become redundant because sysadmin members inherently hold all permissions.

This is why the query using IS_ROLEMEMBER() does not return the custom role for sysadmin users. Their full access is not a result of explicit role assignments but of the elevated privileges that come with the sysadmin role. It is advisable to examine actual permissions when evaluating sysadmin accounts.

4

hey there! i’m curious, have u tried checking the actual permissions instead of just the roles? sysadmin is like a super-user, so it might not show up in regular role checks. maybe try looking at what the user can actually do in the database? that could give u a better picture of whats going on. what do u think?