Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Which of these is more complex?

GRANT SELECT ON dbo.Customer TO JoeDev

or

CREATE ROLE Sales
GRANT SELECT ON dbo.Customer to Sales
ALTER ROLE Sales ADD MEMBER JoeDev

The second one, right? What if I change this slightly. I have this code:

GRANT SELECT, INSERT, UPDATE ON dbo.Customer TO JoeDev

GRANT SELECT, INSERT, UPDATE ON dbo.Customer TO SallyDev

GRANT SELECT, INSERT, UPDATE ON dbo.Customer TO SaraDBA

or

GRANT SELECT, INSERT, UPDATE ON dbo.Customer TO Sales

ALTER ROLE Sales ADD MEMBER JoeDev

ALTER ROLE Sales ADD MEMBER SallyDev

ALTER ROLE Sales ADD MEMBER SaraDBA

What if I changed this slighly and told you that between the GRANTs to users, a few months of time had passed and you had to go figure out which rights JoeDev had because the request was “give Sally the same access as Joe.”

That’s the type of thing I’ve done often as a DBA. I’ve often had to move permissions between users, duplicate the access, or quickly remove lots of access from multiple users.
While it seems like there are just two extra statements using roles, there is often lots of time tracking down security and building statements to duplicate rights.

Always use roles and your life will be easier.

Plus you can script the permissions for objects once, log them, and forget about them. From that point forward you’re just adding/dropping users from roles.