create custom role equivalent to sysadmin

Question

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

Answer 1

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.

Answer 2

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.