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

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

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

Loading comments...