September 1, 2010 at 9:25 am
I am working with a database that currently only uses a dbo schema. We need to allow users to CREATE tables within this schema, and allow them to delete database from within tables, but not allow them to DROP tables. Is this a security combination thats even possible?
I have tried creating a role, granting control on schema and deny delete, I have tried not granting control and only granting create. And everything I try I either stop the user from being able to create a table, or they can create a table and drop the table.. any ideas?
September 1, 2010 at 12:30 pm
This does not even make sense
We need to allow users to CREATE tables within this schema, and allow them to delete database from within tables, but not allow them to DROP tables
One, you should never allow an end user to create a table. That is just poor database design.
Second, What is "delete database from within tables"??
Please clarify.
Andrew SQLDBA
September 1, 2010 at 12:41 pm
.. edit .. I meant allow them to delete data from within the table .. not delete database from within the table. And unfortunately its a database that has been around for literally a decade and they have their "process" set in stone, the database group just recently was given the database to maintain and we are trying to reign things in where possible. Their process involves moving data that is entered into a table daily to a new "monthly" table which they use to report off of then purging the data from the original table so new data for the current month could be populated. And there are a ton of "Non-Best-Practices" going on here but we are trying to get what we can without killing the end-users overall process. I am trying out granting control over the schema to a database role then putting in a trigger that if part of that role will rollback and spit out a warning that their access can't do it .. and it appears its working .. I just hoped there was a more clean way of doing with just access control..
September 1, 2010 at 3:15 pm
Use a stored procedure that will Delete certain rows, depending on the criteria. You give the group execute only to the sproc.
Why not do that with a SSIS package? That would be much easier than all this other stuff that you are talking about.
Andrew SQLDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply