Using SQL Server 2014 Express. I'm new to SQL Server (more familiar with Oracle). I have a schema defined and a user defined as the schema owner. I cannot seem to find a way to allow the user to create tables inside the schema without granting db_owner role.
使用SQL Server 2014 Express。我是SQL Server的新手(更熟悉Oracle)。我定义了一个模式,并将用户定义为模式所有者。我似乎无法找到一种方法来允许用户在架构内创建表而不授予db_owner角色。
This will not work for my DB because there will be multiple schemas. With this database level role, ie db_owner, the user (schema owner) can create tables in any schema.
这对我的数据库不起作用,因为会有多个模式。使用此数据库级角色(即db_owner),用户(架构所有者)可以在任何架构中创建表。
My goal is to have a schema owner user able to log into the DB and create tables and manage the schema and ONLY their own schema.
我的目标是让架构所有者用户能够登录数据库并创建表并管理架构,并且只管理自己的架构。
Can someone help me with this? I'm quite frustrated.
有人可以帮我弄这个吗?我很沮丧。
Thanks in advance.
提前致谢。
1 个解决方案
#1
1
After much research, I was able to figure out the answer to my own question:
经过大量研究,我能够找到自己问题的答案:
"Create table" is only a database level permission, cannot be assigned at the schema level.
“创建表”仅是数据库级别权限,无法在架构级别分配。
A user can be defined as the schema owner. If the user has "Create Table" permissions at the database level and is a schema owner, all tables will be created in the schema he/she owns.
用户可以定义为架构所有者。如果用户在数据库级别具有“创建表”权限并且是架构所有者,则将在他/她拥有的架构中创建所有表。
A user cannot see other schemas unless granted "select" permission at the schema level by the schema owner (or someone with higher level authority). If the user has "Select" at the database level, he/she will see all schemas.
除非模式所有者(或具有更高级别权限的某个人)在模式级别授予“select”权限,否则用户无法查看其他模式。如果用户在数据库级别具有“选择”,则他/她将看到所有模式。
#1
1
After much research, I was able to figure out the answer to my own question:
经过大量研究,我能够找到自己问题的答案:
"Create table" is only a database level permission, cannot be assigned at the schema level.
“创建表”仅是数据库级别权限,无法在架构级别分配。
A user can be defined as the schema owner. If the user has "Create Table" permissions at the database level and is a schema owner, all tables will be created in the schema he/she owns.
用户可以定义为架构所有者。如果用户在数据库级别具有“创建表”权限并且是架构所有者,则将在他/她拥有的架构中创建所有表。
A user cannot see other schemas unless granted "select" permission at the schema level by the schema owner (or someone with higher level authority). If the user has "Select" at the database level, he/she will see all schemas.
除非模式所有者(或具有更高级别权限的某个人)在模式级别授予“select”权限,否则用户无法查看其他模式。如果用户在数据库级别具有“选择”,则他/她将看到所有模式。