create custom role equivalent to sysadmin

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.