March 15, 2011 at 5:00 pm
Specialized User / Role / Schema setups
We don't want users to be able to modify objects in other schemas, just a special public schema that was set up for them (but still have RO to objects in the other schemas).
This is the only way that I have gotten it to work:
UserA + UserB + UserC >> Role1 >> Schema1 (ownership no difference)
for Role1 assign ddl_admin
Remove rights for Role1 on other schemas
Thing is members of Role1 can still create objects in other schemas but cannot modify them.
Hence: DDL Admin on a schema basis? Assigning schema ownership to the role made no difference, w/o ddl_admin they still could make no changes ...
March 15, 2011 at 7:19 pm
Users with db_ddladmin rights on a database have the following (from http://msdn.microsoft.com/en-us/library/ms189612.aspx):
Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES
You probably don't want all of those for a schema-limited permission.
The following allows a user to create tables in the database (only on schemas that they have ALTER rights to), and rights to the schema.
GRANT CREATE TABLE TO [User1]
GRANT ALTER ON SCHEMA::MySchema TO [User1]
Note that they can create the objects, but can't access it if they don't own the schema.
Sample Code!
USE master
GO
DROP DATABASE TestDB
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE SCHEMA TestSchema AUTHORIZATION dbo
GO
CREATE USER [User1] WITHOUT LOGIN
GO
-- Fails - CREATE TABLE permission denied in database 'TestDB'.
EXECUTE AS USER = 'User1'
CREATE TABLE TestSchema.UserTable (i int IDENTITY)
GO
REVERT
GO
GRANT CREATE TABLE TO User1
-- Fails - The specified schema name "TestSchema" either does not exist or you do not have permission to use it.
EXECUTE AS USER = 'User1'
CREATE TABLE TestSchema.UserTable (i int IDENTITY)
GO
REVERT
GO
GRANT ALTER ON SCHEMA::TestSchema TO USER1
-- Works!
EXECUTE AS USER = 'User1'
CREATE TABLE TestSchema.UserTable (i int IDENTITY)
GO
REVERT
GO
-- DBO adds a new row
SELECT * FROM TestSchema.UserTable
INSERT INTO TestSchema.UserTable DEFAULT VALUES
SELECT * FROM TestSchema.UserTable
-- Fails - The SELECT permission was denied on the object 'User1', database 'TestDB', schema 'TestSchema'.
EXECUTE AS USER = 'User1'
SELECT * FROM TestSchema.UserTable
GO
REVERT
GO
USE master
GO
--DROP DATABASE TestDB
March 15, 2011 at 9:18 pm
Thank you, Jim, & you're correct it is seemingly a simple issue. I'll check it out in the A.M.
Thanks again,
Joe
March 17, 2011 at 3:30 pm
Thanks Jim!
That led to
"GRANT Database Permissions (Transact-SQL)" http://msdn.microsoft.com/en-us/library/ms178569.aspx
which by far was the simplest answer.
Was not familiar with the granularity available (and it helps to know how to ask the correct question). :w00t:
Thanks again - Joe
October 15, 2012 at 2:53 pm
The problem I was having is that I wanted to give a user CONTROL permissions on only one schema of the database. The client software will not allow a schema.tablename convention, it can only specify the tablename when creating/altering/deleting a table. By using Jim's suggestion of
GRANT CREATE TABLE TO [User1]
GRANT CONTROL ON SCHEMA::MySchema TO [User1]
and adding
ALTER USER [User1] WITH DEFAULT_SCHEMA=[MySchema]
when the client issues a CREATE TABLE abcd, it allows the operation, but defaults to MySchema.abcd, same with DROP TABLE.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply