0

I am adding a DB Admins LDAP group to multiple servers with sysadmin access. In this group is my domain account. I use this domain account to log in to a server via SSMS. My understanding is that a sysadmin can see all and do all automatically, without any further set up. I'm coming across more and more servers with no apparent databases outside of the defaults, and some databases with empty tables. Both of these scenarios are more than plausible, and probably simply show incomplete installations. Most are SQL Server Express, but some are SQL Server Standard.

Because I'm coming across this kind of stuff more and more, I'm beginning to doubt my server role access.

  1. Is there a way to positively confirm exactly what roles/permissions a user is logged in with considering that they may be in multiple groups with different roles?
  2. If a database is super-duper triple top secret is there a way to block access to a freshly created sysadmin account?

1 Answer 1

2

My understanding is that a sysadmin can see all and do all automatically, without any further set up.

Your understanding is correct, a sysadmin role member can do all and cannot be denied anything on the instance, as you can see on the Fixed server-level roles doc:

Members of the sysadmin fixed server role can perform any activity in the server.

You can use the IS_SRVROLEMEMBER function to verify that your login is a member of the sysadmin server role:

SELECT IS_SRVROLEMEMBER('sysadmin', 'YourDomain\YourLogin');

If you really are a member of the sysadmin server role, you shouldn't be denied anything, as you can see on the Permission Statements doc:

DENY revokes a permission so that it cannot be inherited. DENY takes precedence over all permissions, except DENY does not apply to object owners or members of sysadmin. If you DENY permissions on an object to the public role it is denied to all users and roles except for object owners and sysadmin members.

Members of the sysadmin fixed server role and object owners cannot be denied permissions.

You can also use fn_my_permissions to list effective permissions on the server.

4
  • So I've performed this IS_SRVROLEMEMBER select and it shows that 'MyDomain\MyGroup' is a sysadmin, but it doesn't confirm that 'MyDomain\MyUser' is acting as a sysadmin, (returns NULL). There is no login for MyUser as it's part of the group. I am logged in as MyUser, so I would assume I would be a sysadmin, but I want to be able to confirm this. I can add another sysadmin when logged in as MyUser which certainly implies sysadmin access, but is there any way to definitively show this? Commented Jan 10, 2023 at 20:08
  • @DaveHarding, You can list the permissions you have using the function fn_my_permissions. You could verify if that limitation you noticed also applies to the sa login if it is enabled.
    – Ronaldo
    Commented Jan 10, 2023 at 20:42
  • It looks like fn_my_permisions does what I need. Thanks! Commented Jan 10, 2023 at 21:30
  • @DaveHarding, glad I could help. I have updated the answer to include it. Please, remember to mark the answer as correct if it answered your question.
    – Ronaldo
    Commented Jan 10, 2023 at 21:36

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.