How to grant Schema Owner to a user ?

Question

Hi,

There is a request for becoming Schema Owner for "dbo" for a particular database.

Currently, he already gets the following database role

1. db_datareader
2. db_datawriter
3. db_dlladmin

He mentions that without being the Schema Owner, he cannot amend tables with PK column. Is it correct ?

May I ask how to grant the Schema Owner to him OR just give him the dbo_owner right ?

Thanks

Answer 1

that user says that he is a seasoned user.

But he is revealing that is not, when he craves to be schema owner for dbo - because that is effectively the same as owning the entire database.

Anyway, it seems that you can just sit on our hands. Look at the script below, which runs without errors. (The EXECUTE AS statement cause you to impersonate the seasoned user to you can test is permissions.)

CREATE TABLE test (a int NOT NULL)
CREATE USER seasoned WITHOUT LOGIN
ALTER ROLE db_ddladmin ADD MEMBER seasoned
go
EXECUTE AS USER = 'seasoned'
go
ALTER TABLE test ADD id int NOT NULL
go
ALTER TABLE test ADD CONSTRAINT pk PRIMARY KEY (id)
go
REVERT
go
DROP TABLE test
DROP USER seasoned 

Answer 2

The question doesn't make sense - when you create a schema you set it up with an owner. The owner of the schema will either be the same as the schema (usually the same as the user) or to a specific user (generally dbo).

For example - CREATE SCHEMA MyNewSchema AUTHORIZATION dbo;

A user can then be granted permissions to the schema - or will already have permissions if they are a member of the correct role.

You can grant access to the schema specified using: GRANT ... ON schema::MyNewSchema TO {user/role};

You probably do not want to grant db_owner to the user in this case. What you probably want to do is to make sure the schema is owned by dbo, then grant specific permissions to the schema (SELECT, EXECUTE, DELETE, INSERT, UPDATE) as needed by that user.