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.

GRANT CONTROL ON DATABASE::EmptyFileTest TO JoeDev

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:

GRANT CONTROL ON DATABASE::EmptyFileTest TO LOGIN::JoeDev

Nope.

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

Let’s add the user.

CREATE USER JoeDev FOR LOGIN JoeDev

Now we’ll try this again.

GRANT CONTROL ON DATABASE::EmptyFileTest TO JoeDev

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

SQLNewBlogger

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.

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.