create custom role equivalent to sysadmin

Heisenberg 221 Reputation points
2023-01-20T21:15:32.0233333+00:00

hi Folks,

I have a weird requirement. Is it possible to create a custom role that has all the permissions like sysadmin ? Basically i want to create a custom role like sysadmin but by excluding certain permissions like "alter database" "control" permission etc.

Thank you

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,238 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 98,636 Reputation points
    2023-01-20T22:26:20.83+00:00

    No, it is not possible. Santhi's example above have several errors. One is that the role is added to sysadmin. Once you are in sysadmin, all performance checks are voided. So you cannot revoke or deny anything from sysadmin.

    There is also the permission CONTROL SERVER, which gives you all permissions on the box, and in this case you can do:

    USE master
    go
    CREATE LOGIN TestAdmin WITH PASSWORD = '==)Y/BNBÖ*ÄSADEääsdb'
    GRANT CONTROL SERVER TO TestAdmin
    DENY ALTER ANY DATABASE TO TestAdmin
    go
    EXECUTE AS LOGIN = 'TestAdmin'
    go
    ALTER DATABASE model SET RECOVERY FULL -- Results in permission error.
    go
    REVERT
    
    

    However, there are two problems:

    1. While CONTROL SERVER gives you all permissions, there are still only actions that can be performed by a member of the sysadmin role.
    2. It is very easy for TestAdmin to circumvent the DENY
    EXECUTE AS LOGIN = 'TestAdmin'
    go
    REVOKE ALTER ANY DATABASE TO TestAdmin
    go
    CREATE LOGIN ExtraUser WITH PASSWORD = 'HahaSaidTheClown'
    GRANT CONTROL SERVER TO ExtraUser
    go
    EXECUTE AS LOGIN = 'ExtraUser'
    go
    ALTER DATABASE model SET RECOVERY FULL
    go
    REVERT
    go
    REVERT
    
    DROP LOGIN TestAdmin
    DROP LOGIN ExtraUser
    
    

    That is, TestAdmin can create a new login, grant that login CONTROL SERVER, impersonate that login and then perform the forbidden action.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. SanthiSwaroopNaikBukke-4908 595 Reputation points
    2023-01-20T21:18:59.2233333+00:00

    Yes, it is possible to create a custom role in SQL Server that has similar permissions to the sysadmin role, but with certain permissions excluded.

    Here is an example of how you can create a custom role with all sysadmin permissions except "ALTER DATABASE" and "CONTROL" permissions:

    Copy code
    CREATE ROLE custom_sysadmin 
    GO
    
    -- Grant all sysadmin permissions to custom_sysadmin role
    EXEC sp_addsrvrolemember 'custom_sysadmin', 'sysadmin'
    GO
    
    -- Revoke ALTER DATABASE and CONTROL permissions from custom_sysadmin role
    REVOKE ALTER DATABASE TO custom_sysadmin
    REVOKE CONTROL TO custom_sysadmin
    GO
    

    This code creates a new role called custom_sysadmin and grants all permissions of the sysadmin role to it. Then it revokes "ALTER DATABASE" and "CONTROL" permissions from the custom_sysadmin role.

    Note that you can also use the GRANT statement to grant specific permissions to custom_sysadmin role, you can use DENY statement to deny specific permissions to custom_sysadmin role.

    You can also use the sp_helprotect stored procedure to check the permissions of the role.

    Please be aware that creating a custom role with sysadmin permissions can create a security risk, and should be used with caution. It is highly recommended to test the custom role in a non-production environment before deploying to production.