SQL Homework – February 2018 – Work with security


February 5, 2018 by Kenneth Fisher

This month let’s create some security!

  • Create logins: GUI 5 pts/Script 5 pts
    Create a SQL login and a Windows authenticated login. Make sure you can connect to the instance with each. It’s best if you understand the difference.
  • Create users: GUI 5 pts/Script 5 pts
    In a user database create a user for each of the above logins. Make sure that you can connect to the database with each of these users. What permissions do you have at this point? Think about the difference between a login and a user.
  • Create a server role: GUI 10 pts/Script 15 pts
    What might a server role be used for? They certainly aren’t common but it’s a good idea to at least be familiar with the concept. Grant your role a few permissions, perhaps what a junior DBA would need.
  • Create a database role: GUI 10 pts/Script 15 pts
    In the same database you created your users create a developer role. Grant it permissions that you would feel appropriate to a developer. Think about what permissions you might need as a developer. Once done review what you did. Did you add this role as a member of another role? Or did you only grant database level permissions? Did you grant anything more granular than permissions at the database level? What are some other common needs that could be met with roles?
  • Bring it together: GUI 15 pts/Script 15 pts
    Add your logins and users to the appropriate (server/database) roles that you created. Test to make sure you have the permissions you expect. Can you see how this would be easier than granting individual permissions to each login/user? Are there any other benefits you can think of?

Just to answer a few questions I commonly get:

  • No, if you are an expert in security and do this every day I don’t expect you to do it again for this. That said, if you are an expert in security who’s never created/thought about server roles before do I think you should do that part? Yes.
  • Do I think it’s important that you know both how to script and use the GUI? Yes. If you are a Jr DBA who’s only ever used the GUI you need to learn to script. It’s faster, easier and there are things you can do with scripts you can’t in the GUI. If you are a Sr level DBA who can’t use the GUI how are you going to help a Jr DBA who is trying to use the GUI and runs into a problem?

One thought on “SQL Homework – February 2018 – Work with security

  1. […] few years back (wow time flies) I had you Work with Security. It’s been a while so if you aren’t completely comfortable with how SQL Server security […]

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 )

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.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: