Security Question->User need to Alter tables, but objets must remain current dbo
-
We have a situation where users must go in and Alter tables. If they login without logging in as 'dbo' or not being a member of sys admins role for the server, the ownership of the modifyed object transfers to their current login.
Are there any other ways around this, say not having to give 'sys admin' permission or having them to login as 'dbo' to carry out their tasks?
We are running SQL Server 2000 SP4.
Appreciate replies
-
What happens if they run this : ALTER PROCEDURE dbo.SpName ....?
-
The fixed database role db_ddladmin allows for this. However, they must specify the owner when touching (creating or altering) the object (as Remi indicates in his post). Otherwise you'll get the object ownership change.
K. Brian Kelley
@kbriankelley -
Users altering tables
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: -
I got tired of asking why and starting a war everytime I see this. But you have a good point.
Why in %/? name do the users have to change the schema (assuming in production)... if you need more programmers in the system, hire one.
Fake rant over
. -
Aye, Remi, I am with you on this one. It's generally not a good idea and if the system is in any way sensitive and subject to SOX, SAS70, or SysTrust here in the US, it'll get squashed fast once the auditor rolls through. But most of the time the DBA has his or her hands tied. Management has decided to accept the risk that a developer will do something crazy like DROP CRITICAL TABLES IN PRODUCTION. Seen it happen. Had to restore the backup.
K. Brian Kelley
@kbriankelley -
How about this one... the dude makes a mistake in a delete statement and deletes 1000 rows when he meant to delete 500 on a 1M rows table. He doesn't notices and now you get a dozen reports crapping out garbage data (even if only 0.05 % incorrect). Then 6 months later someone realizes the problem because something just doesn't seem right even if the numbers seem to pretty much add up (500 rows on 1M is not much, but add in a few inner joins and the problem may be easier to spot). Anyone could recover from this with your current dr plan / backup strategy?
-
Possibly...it depends on:
- How often the data gets updated.
- When the correct but deleted rows were captured in a backup. (Or if?)
- What your backup retention strategy is.
K. Brian Kelley
@kbriankelley -
Or if you have audit on the tables and those audit tables are never deleted... but you see another gotcha situation where you may run with invalid data for a few months and make bad business decisions based on that bad data. Assuming the problem is ever caught.
And then again I don't really see myself keeping 1 year worth of backups just in case. But I have never worked in a big company so I never ahd a need for that
. -
We're required to keep "monthly" backups for quite some time. "Yearly" backups as well. In a large company it's probably more feasible than in a small company. However, this level of permissions is more likely to be seen in a small company. So you can't win.
K. Brian Kelley
@kbriankelley -
small company or "old" application. seems like as they age, more and more people get rights to do this stuff.
I'm a "DBAs only" for schema changes. Developers can run them on their own machines, but they don't go into the central development machine, QA, or production without a DBA.
-
Can't wait to be able to do this here
. -
Fortunately I am the only DBA here so what I say goes!! (As long as I listen to myself
)OOps just been told to give myself access to the systables.....
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: -
You should really get some help for those voices you're hearing. My voices keep telling me I should, too, but I keep putting them off.
K. Brian Kelley
@kbriankelley -
It's ok as long as they don't start arguing. Then it gets too loud.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply