October 12, 2007 at 5:27 pm
Hi,
I would like non dbo user to be able to create table as dbo (I don't want the table created as [USERNAME].[TABLENAME])
I can accomplish this easily by:
grant alter on schema::dbo to [USERNAME]
grant create table to [USERNAME]
but the problem is this also allow [USERNAME] to delete any table owned by dbo.
Is there anyway to overcome this problem ?
I want a regular user to be able to create table under dbo schema but i don't want to grant them DELETE rights.
Explicitly denying DELETE did not help e.g.
deny delete to [USERNAME]
Thanks
October 13, 2007 at 11:29 am
Hello Andy,
you might want to try DDL triggers.
ALTER TRIGGER dTrgDenyDropTable ON DATABASE FOR DROP_TABLE
AS
SELECT EVENTDATA()
PRINT 'Not allowed!'
ROLLBACK;
GO
This is just a small sample to show you the basics. You can check for a specific username inside EVENTDATA() to disallow only specific users.
Besides that, is there a reason why you only want dbo objects? In SQL2K5 i dont see a reason for this anymore.
Cheers!
Best Regards,
Chris Büttner
October 16, 2007 at 8:50 pm
did u try creating table name using dbo.TableName
October 17, 2007 at 1:41 pm
The CREATE TABLE right is going to grant DROP TABLE rights. In this case, the DDL solution proposed is your best bet. However, you may want to put some logic to see who is doing the DROP. While a db_owner role member can disable the trigger, unless you want it active for everyone, you can check who the executing user is, if it's dbo, allow the drop to proceed. Otherwise, block it.
K. Brian Kelley
@kbriankelley
October 17, 2007 at 10:11 pm
thanks for the reply
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply