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?