SQL Server How can we grant user to do anything on his schema

gblwokeq  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(105)

We have observed that we need to grant create table permission at database level to user if they need to create objects however we just need to give them permissions at schema level as it's production environment and we cannot grant create table permission at database level.

icomxhvb

icomxhvb1#

Having CREATE TABLE permissions at the database level doesn't grant the user the ability to actually create a table. In addition they must have permissions on the target schema.

The correct grants for a user to be able to create tables in a schema is to grant the user or a role they are in ownership of the schema, eg

create schema foo
go
create role foo_owners 
go
alter authorization on schema::foo to foo_owners
go

grant create table to foo_owners;

create user a_foo_owner without login

alter role foo_owners add member a_foo_owner

go

You don't just grant privileges on a schema owned by another user, or they could create objects with intact ownership chains to other schemas owned by the schema owner.

相关问题