How to add a SQL Server role as a member of another role?

Question

I would like to roll-up permissions. Is it possible to add a role as a member of another role in a Microsoft SQL Server 2016 database? If so, then how?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Yes, it is possible. simply by using sp_addrolemember, or the GUI as you'd normally add a user to a role:

exec sp_addrolemember 'BigRole', 'LittleRole'

Note that SQL Server will prevent you from making a role a member of itself, so if "BigRole" is already a member of "LittleRole", the above will fail.

How to add a SQL Server role as a member of another role?

Sign up