August 17, 2010 at 4:47 am
How Deny DROP access to a db_ddbladmin user. The user needs to create tables/views/sp's etc
August 17, 2010 at 5:38 am
balasach82 (8/17/2010)
How Deny DROP access to a db_ddbladmin user. The user needs to create tables/views/sp's etc
you cannot modify the built in roles in any way;
what you want to do is create a new role of your own, and then add or subtract permissions to that role.
there's an implied permissions idea that basically says if you created it, you can destroy it since it's your object.;
so what you are asking for is tough...if someone has the ability to CREATE/ALTER objects, they generally get the ability to drop it so they can recreate it; AFAIK it's not possible to do that via permissions; DROP permissions are not seperated from CREATE or ALTER.
what you can do, however, is create a database level trigger that prevents the DROP of any object;
they get an error that states this, but the error you create gets logged to the SQL log:
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
here's an example i just tested:
CREATE TRIGGER [TR_DB_NO_DROPPING_OBJECTS]
on DATABASE
FOR
DROP_PROCEDURE,DROP_FUNCTION,DROP_VIEW,DROP_TABLE
AS
BEGIN
IF --only two accounts allowed to drop stuff
suser_name() NOT IN('sa','BobTheSupervisor' )
--and only from two specific machines on the network
OR host_name() NOT IN('auth1','auth2')
BEGIN
--raise an error, which goes to the error log
RAISERROR('Unauthorized use of drop object from inpermissible host.', 16, 1)
--prevent the drop
ROLLBACK
END
--if it got to here, it was the "right" user from the "right" machine (i hope)
END
Lowell
August 17, 2010 at 6:03 am
Its ok if the user can delete his own tables, But other than the using trigger is there no way to stop the user from dropping other's tables?
August 17, 2010 at 6:10 am
balasach82 (8/17/2010)
Its ok if the user can delete his own tables, But other than the using trigger is there no way to stop the user from dropping other's tables?
that is correct;
if you give someone ddl_admin, they can drop any object in the database.
the owner of the object i was refering to was if you were using multiple schemas, so the user bob, when he creates the table 'example', actually creates the table bob.example instead of dbo.example ; in that case, you could remove ddl_admin from the default schema, and give the user ddl_admin just tot he 'bob' schema, so he would be unable to affect the 'jane' or 'dbo' schemas.
It's rare to see anyone using that kind of situation, though.
Lowell
August 17, 2010 at 7:17 am
Have confusion in schema/user for a long time;
I am going to Create a new schema:
General tab:Test (specify Test as schema owner)
Permissions: search and select the User ( left the default options in the below explicit tab)
Then "remove ddl_admin from the default schema";
give the user ddl_admin to the schmema (say Test)
How to do this?
September 10, 2010 at 8:23 am
Is there a way to restrict ddl_admin role members for insert,update,delete and drop on some tables but they still have to be ddl_admin on the whole database. I want to restrict them only for few tables which are standard in all databases of the server.
September 10, 2010 at 8:40 am
Tara i saw the other thread , and yes it's possible, but you need to decide on some way to identify the table...specific name, maybe from a table in master, or a naming convention.
this works perfectly on my machine: i tested this as sa/dbowner, as well as a plain old role with ddl_admin; it stops everyone, but of course a sysadmin could disable or drop the trigger...you could put in additiona exemptions like i did witht eh trigger in this thread...allowing it for specific users or from specific machines.
--DROP TRIGGER [TR_DB_NO_DROPPING_OBJECTS] ON ALL SERVER
CREATE TRIGGER [TR_DB_NO_DROPPING_OBJECTS]
on ALL SERVER
FOR
DROP_PROCEDURE,DROP_FUNCTION,DROP_VIEW,DROP_TABLE
AS
BEGIN
SET NOCOUNT ON
--declare some variables
DECLARE
@eventData XML,
@DATABASENAME SYSNAME,
@EVENTDATE DATETIME,
@USERNAME SYSNAME,
@SYSTEMUSER VARCHAR(128),
@CURRENTUSER VARCHAR(128),
@ORIGINALUSER VARCHAR(128),
@HOSTNAME VARCHAR(128),
@APPLICATIONNAME VARCHAR(128),
@SCHEMANAME SYSNAME,
@OBJECTNAME SYSNAME,
@OBJECTTYPE SYSNAME,
@EVENTTYPE VARCHAR(128),
@COMMANDTEXT VARCHAR(max),
@NAMEFORDEFINITION VARCHAR(261)
--Load Variables from the xml
SET @eventData = eventdata()
SELECT
@DATABASENAME = db_name(),
@EVENTDATE = GETDATE(),
@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@SYSTEMUSER = SUSER_SNAME(),
@CURRENTUSER = CURRENT_USER,
@ORIGINALUSER = ORIGINAL_LOGIN(),
@HOSTNAME = HOST_NAME(),
@APPLICATIONNAME = APP_NAME(),
@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
--print @OBJECTTYPE
IF @OBJECTTYPE ='TABLE'
BEGIN
IF @OBJECTNAME LIKE 'SEC%'
RAISERROR('SEC tables are protected and cannot be dropped', 16, 1)
--prevent the drop
ROLLBACK
END
IF @OBJECTTYPE ='VIEW'
BEGIN
IF @OBJECTNAME LIKE 'VW_%'
RAISERROR('VIEWS starting with [VW_] are protected and cannot be dropped', 16, 1)
--prevent the drop
ROLLBACK
END
END
then i did this:
create table secbananas(bananaid int,bananname varchar(30) )
drop table secbananas
Msg 50000, Level 16, State 1, Procedure TR_DB_NO_DROPPING_OBJECTS, Line 46
SEC tables are protected and cannot be dropped
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply