Any granular permissions granted to a system defined role can be revoked from the role to limit the power of that role. See examples below for some typical use cases.
Example 1:
Users with the manage server permissions privilege can restrict users with the sa_role from accessing user databases by revoking the own any database and manage server permissions privileges:
use master revoke own any database from sa_role revoke manage server permissions from sa_role
Example 2:
By default, setuser privilege is granted to the database owner, which enables the database owner access other users’ data by impersonating that user.
Revoke the setuser privilege from the database owners to restrict them from accessing other users’ data. To prevent database owners from granting setuser privileges to themselves, make sure that the manage database permissions privilege is not granted to the database owners in the databases. By default, the manage database permissions privilege is not granted to the database owner.
For example, to revoke setuser privileges from the database owner in database db1:
use db1
revoke setuser from dbo
Change these privileges in the model database to make this the default behavior in any user database created in the future:
use model
revoke setuser from dbo
Example 3:
Any sa_role user may accidentally shut down the server. To prevent this, a system administrator with manage server permissions privilege can revoke shutdown privilege from sa_role and grant it only to the administrators responsible for shutting down the server operation.
For example, to grant users joe and bob (both with the sa_role) the shutdown privilege, and revoke it from all others, a user with the manage server permissions privilege issues:
use master grant shutdown to joe, bob revoke shutdown from sa_role
