Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can DDL Admin be assigned at the schema level instead of the DB level?


Can DDL Admin be assigned at the schema level instead of the DB level?

Author
Message
jkelly
jkelly
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 207
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 ...



Jim McLeod
Jim McLeod
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 Visits: 1121
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


jkelly
jkelly
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 207
Thank you, Jim, & you're correct it is seemingly a simple issue. I'll check it out in the A.M.

Thanks again,

Joe



jkelly
jkelly
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 207
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



rtelgenhoff
rtelgenhoff
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 356
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search