Custom Role Needed

Question

Custom Role Needed

Jason Tontz

SSCommitted

Points: 1957
More actions
May 4, 2011 at 10:54 am

#147943

Hello all.
I need to create a role that allows members to create tables in the database. I want to nest db_datareader, db_datawriter as well. This will allow db developers to create and populate tables, but I need to make it so they cannot drop tables.
Thank You In Advance.
JT

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Jason Tontz (5/4/2011)
Hello all.
I need to create a role that allows members to create tables in the database. I want to nest db_datareader, db_datawriter as well. This will allow db developers to create and populate tables, but I need to make it so they cannot drop tables.
Thank You In Advance.
JT

if the role ddl_admin is granted so they can create objects, they can drop them too. to do what you want(prevent dropping of tables) you'd have to use a database DDL trigger to rollback a command based on DROP_TABLE.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jason Tontz SSCommitted Points: 1957 More actions · Answer 2

What about creating a new role called "db_tablecreator" and issuing a "GRANT CREATE TABLE TO db_tablecreator"? Does this allow them to drop tables?

I will implement the trigger anyway for general security measures, but to explicitly deny dropping of tables with the create is needed.

Thanks for the quick reply.

Lowell SSC Guru Points: 323518 More actions · Answer 3

yeah, whatever you create, you have the right to destroy..that's what can be scary about granting db_owner...they can drop the database with that right/role.

so with ddl_admin, if you create a table, you would have the right to destroy it too.

here's a DDL trigger example to stop the drops:

Msg 50000, Level 16, State 1, Procedure TR_NO_OBJECT_DROPS, Line 14
No Objects May Be Dropped from This Database
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

use SandBox

GO

CREATE TRIGGER [TR_NO_OBJECT_DROPS]

ON DATABASE

WITH EXECUTE AS 'dbo'

FOR

DROP_PROCEDURE,

DROP_FUNCTION,

DROP_VIEW,

DROP_TABLE,

DROP_TRIGGER

AS

BEGIN

SET NOCOUNT ON

RAISERROR('No Objects May Be Dropped from This Database', 16, 1)

ROLLBACK

END --DB TRIGGER

GO

ENABLE TRIGGER [TR_NO_OBJECT_DROPS] ON DATABASE

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!