Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can DDL Admin be assigned at the schema level instead of the DB level? Expand / Collapse
Author
Message
Posted Tuesday, March 15, 2011 5:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 14, 2014 5:28 PM
Points: 341, Visits: 200
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 ...




Post #1078719
Posted Tuesday, March 15, 2011 7:19 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, July 6, 2014 8:03 PM
Points: 687, Visits: 1,110
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

Post #1078739
Posted Tuesday, March 15, 2011 9:18 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 14, 2014 5:28 PM
Points: 341, Visits: 200
Thank you, Jim, & you're correct it is seemingly a simple issue. I'll check it out in the A.M.

Thanks again,

Joe



Post #1078760
Posted Thursday, March 17, 2011 3:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 14, 2014 5:28 PM
Points: 341, Visits: 200
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





Post #1080079
Posted Monday, October 15, 2012 2:53 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 703, Visits: 321
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.
Post #1372948
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse