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.