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:
- While CONTROL SERVER gives you all permissions, there are still only actions that can be performed by a member of the sysadmin role.
- 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.