Granting CONTROL on a database–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to grant a login the CONTROL permission on a database. This wasn’t a simple as I expected. I had a login (JoeDev) with no user mappings or server roles, and a database (EmptyFileTest) that I wanted to grant them permissions on.

My first attempt was this:

GRANT CONTROL on EmptyFileTest to JoeDev

This didn’t work.

2018-07-03 11_20_21-SQLQuery3.sql - (local)_SQL2016.master (PLATO_Steve (60))_ - Microsoft SQL Serve

I’m a sysadmin, so it’s not permissions. Maybe it’s qualification. The database isn’t found as an object, so let’s fix that.


Aha, I’m making progress.

2018-07-03 11_21_22-SQLQuery3.sql - (local)_SQL2016.master (PLATO_Steve (60))_ - Microsoft SQL Serve

The database is found, but the user isn’t. That implies this is an internal permission in the database that can’t be granted to the login. One more try:



2018-07-03 11_24_41-SQLQuery3.sql - (local)_SQL2016.master (PLATO_Steve (60))_ - Microsoft SQL Serve

Let’s add the user.


Now we’ll try this again.


As you can see, the context is the master database. This won’t work.

2018-07-03 11_26_07-SQLQuery3.sql - (local)_SQL2016.master (PLATO_Steve (60))_ - Microsoft SQL Serve

Let’s change the context and execute this again. Once I do that, things work.

2018-07-03 11_27_29-SQLQuery3.sql - (local)_SQL2016.EmptyFileTest (PLATO_Steve (60))_ - Microsoft SQ

We can see this worked in the database properties for our database, under the Permissions tab.

2018-07-03 11_28_29-Database Properties - EmptyFileTest


Security and permissions are important. A series of short pieces on different aspects of managing, assigning, or using security will teach you a lot and show that you’re cognizant of the need for security for your databases.

This took about 10 minutes to write. Note that I’m showing my process of learning and progress, not just writing about what is required. Think about writing your story, not just the information.

About way0utwest

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

4 Responses to Granting CONTROL on a database–#SQLNewBlogger

  1. LondonDBA says:

    Nice post Steve. Why did you have to grant Control to the database user ? What actions were they trying to perform that they couldn’t without CONTROL permission ?

    • way0utwest says:

      That’s a great question. I want to say I was testing the limits of permissions, but not completely sure. This was definitely a test, but not sure if I was trying to see if something worked, or just see where the boundaries were for this permission.

      As I can, I’m trying to submit PRs to clear/clarify/correct BOL.

  2. johnmarkdemoss says:

    Thanks for the post, Steve. I appreciate the way focusing on a single narrow topic and personal experience lower the perceived expertise barrier for beginning a blog.

    In that vein, it may not always be necessary to explain the why, but I’m left wondering under what conditions the database wouldn’t be found as an object.

    • way0utwest says:

      In this case, it’s about the context of the database and the login. The rules of the engine apparently just don’t allow this to be granted from master. Other than that, I have no idea why it shouldn’t work, but there may be some security reason.

Comments are closed.