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.

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.

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.

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.

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

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

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.