4

I'm using SQL Server 2016 for these tests.

The following did not allow me to create a table in schema S for user U

USE [D];
GRANT CONTROL ON SCHEMA :: [S] TO [U];

But this did:

USE [D]
GRANT ALTER ON SCHEMA :: [S] TO [U];
GRANT CREATE TABLE TO [U];

What is the GRANT CONTROL supposed to do, if you have to run these two additional commands to allow the user to create a table in a specific schema?

This description is nice, but what kind of a use case can I run that can confirm (with success or failure) the GRANT CONTROL command physically affected the user's security on the database with and without this schema, before and after running the command?

https://blogs.msdn.microsoft.com/lcris/2009/08/11/basic-sql-server-security-concepts-ownership-control-take-ownership/

CONTROL permission: The CONTROL permission can be used to easily grant all permissions on an entity to some principal. It's the next best thing after ownership of the entity, but it's not quite as powerful as ownership. The main difference is that a grantee of CONTROL can still be denied some other permissions on the entity. For example, I can be granted CONTROL on a table, while at the same time I can be denied SELECT on that table, preventing me from selecting from it - this can never happen to the owner, because the owner cannot be granted or denied permissions.

3
  • A Deny to a account with the SA group in SQL Server doesn't over ride their permissions as it would with any other user. CONTROL generally let's you do everything except make others SA. Giving the user CONTROL privs lets them have full access, but in cases where they do need to be denied, the explicit deny will override the grant control so it's more in line with the rest of SQL Security. Nov 22, 2017 at 7:04
  • I found this quote from another forum. "Grantees of CONTROL permission on a schema can grant any permission on any object within the schema. " So it seems as though this user could have granted themselves GRANT ALTER permissions and GRANT CREATE TABLE for this schema they had CONTROL permission on. I'll have to test to make sure... Nov 22, 2017 at 7:29
  • That would fall in line with that it has all permissions of SA outside of not being able to promote users to SA while still being able to be denied access, but they can still override that. All of these nuances would fall under CONTROL. Nov 22, 2017 at 7:59

2 Answers 2

8

To see what CONTROL on schema brings to you, create a test user without any permission, grant CONTROL on schema to it and check what permissions he has after it:

create user test;
grant control on schema::dbo to test;

execute as user = 'test';
select *
from sys.fn_my_permissions('dbo', 'schema');
revert;

Here is what you'll get:

enter image description here

When you want to CREATE TABLE you should open BOL article CREATE TABLE (Transact-SQL) and scroll down to PERMISSIONS:

enter image description here

Now you see that even if your table is not supposed to have columns of CLR user-defined type, you still need CREATE TABLE permission to grant to your user to make him be able to create a table.

Test user has already ALTER SCHEMA implied by CONTROL on schema, but CREATE TABLE should be granted (as well as REFERENCE permission in case of CLR user-defined type).

0

CONTROL confers god-like permissions to a "securable" object (e.g. database, etc.) in SQL Server.

https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-2017

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.