|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:04 PM
Points: 338,
Visits: 188
|
|
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 ...
|
|
|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: 2 days ago @ 4:40 AM
Points: 687,
Visits: 1,074
|
|
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
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:04 PM
Points: 338,
Visits: 188
|
|
Thank you, Jim, & you're correct it is seemingly a simple issue. I'll check it out in the A.M.
Thanks again,
Joe
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:04 PM
Points: 338,
Visits: 188
|
|
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). 
Thanks again - Joe
|
|
|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Friday, May 03, 2013 6:06 AM
Points: 699,
Visits: 272
|
|
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.
|
|
|
|