SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Homework – February 2018 – Work with security

homework-clipart-homework-alert-free-images-at-vector-clip-art-onlineThis 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?


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...