July 14, 2011 at 9:46 am
A user has a role (testrole) through which permissions are granted to her. She is working in the dbo schema, but is not the owner. The role has Create Table and Create View. I also ran the statement, "Grant ALTER on schema::dbo to testrole".
The user can use SSMS to type in "CREATE TABLE test (column1 int)" and the table gets created. But using the navigation pane in SSMS, right-clicking on Tables and clicking New Table fails. Additionally, using Access to transfer an Access table to the dbo schema does not work ("ODBC -- call failed. The INSERT permission was denied on the object test2, database dbtest, schema dbo (#229)").
Ideally, I'd like to let the role create tables in the dbo schema and have all CRUD permissions on any tables/views there, even if the user didn't create them herself.
What are the least set of permissions that will allow this?
Thank you,
Seth
July 14, 2011 at 11:01 am
Grant select, insert, update, delete on object::dbo.test2 to testrole
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 14, 2011 at 11:09 am
yoffes (7/14/2011)
A user has a role (testrole) through which permissions are granted to her. She is working in the dbo schema, but is not the owner. The role has Create Table and Create View. I also ran the statement, "Grant ALTER on schema::dbo to testrole".
This may not do what you think it does. You cannot grant ALTER TABLE or CREATE TABLE permissions on the schema level. Granting ALTER at the schema level opens up some security doors that you may not want open. Please read this article carefully especially the callout labeled "Caution":
http://technet.microsoft.com/en-us/library/ms187940.aspx
Perry has the CRUD stuff covered for a specific object. If you want to grant it for an entire schema try this:
GRANT INSERT, UPDATE, SELECT, DELETE ON SCHEMA::dbo TO testrole
The CREATE TABLE and ALTER TABLE permissions are granted at the database level. See this article:
http://technet.microsoft.com/en-us/library/ms178569.aspx
GRANT CREATE TABLE TO testrole ;
GRANT ALTER TABLE TO testrole ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 14, 2011 at 11:51 am
Thank you for replying. I very much appreciate the time you guys take to help.
My use of "Grant ALTER on schema::dbo to testrole" I found to be necessary. Without it, she can't use SSMS, right-click on tables, and successfully create one. Nor can she upload a table from Access.
It seems the minimum set of grants to create tables in the dbo schema and have all CRUD permissions on any tables/views there, even if the user didn't create them herself is:
GRANT CREATE TABLE TO [testrole]
GRANT CREATE VIEW TO [testrole]
GRANT DELETE ON SCHEMA::[dbo] TO [testrole]
GRANT INSERT ON SCHEMA::[dbo] TO [testrole]
GRANT SELECT ON SCHEMA::[dbo] TO [testrole]
GRANT UPDATE ON SCHEMA::[dbo] TO [testrole]
GRANT ALTER ON SCHEMA::[dbo] TO [testrole]
July 14, 2011 at 1:15 pm
You asked
yoffes (7/14/2011)
What are the least set of permissions that will allow this?
Granting at the table object level is least permission, granting at schema is greater scope!
This looks neater
yoffes (7/14/2011)
GRANT ALTER, INSERT, UPDATE, SELECT, DELETE ON SCHEMA::[dbo] TO [testrole]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 14, 2011 at 2:14 pm
yoffes (7/14/2011)
Thank you for replying. I very much appreciate the time you guys take to help.
You're welcome. Happy you got it sorted 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply