Always Use Roles–#SQLNewBlogger

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

2 Responses to Always Use Roles–#SQLNewBlogger

  1. Chris Harshman says:

    Nice, short, and to the point. I’d add that it’s probably the most straightforward to setup your database roles for the work that needs to be done, not by the department or organizational group that people belong to. E.G. I typically make a role that allows people to perform various DDL commands. That role would have the same permissions in development, test, production databases but different members in each environment.

  2. way0utwest says:

    I agree it’s usually better to set roles based on the job, rather than the group. Usually I find the same job is done by multiple groups, so I just add them from AD into the same role.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.