June 6, 2002 at 3:36 pm
My developers have built an application that requires a table to be truncated. I don't give them that ability. I tried to build a job to do it and they claim that it is adhoc and the table gets built when the end users complete some processes at month end. This process is not on a consistent date or time so scheduling a job was not the solution.
They claim they need ddladmin authority since I am the only DBA and that makes me a bottle neck. How can I get this table truncated without granting them ddladmin or doing it manually myself.
June 6, 2002 at 3:42 pm
Hi Micheal,
One of the approaches I have used is to make a dts package as the sql services domain account user. Then I save the package as a structured file to a local production server dts packages share.
From there I run dtsrunui and create an encrypted run file. Run the interface and you will see the options that I believe include a run as...
The command line it produces at the bottom of the encryption block, I copy and paste to a 'bat' file that others can execute.
I have used this to deploy silent sql user installs on mobile laptops. The techs run the 'job' and I don't have to be involved.
Good luck,
Jody
June 6, 2002 at 4:21 pm
I agree that they dont need access. Job makes sense. No reason they cant start the job, if nothing else indirectly by having the job poll a table periodically, they insert a row to tell the job to continue.
That said, sometimes you have to live with trade offs. I've got a small util app that does some stuff that requires sysadmin access. I compiled it with an obfuscated login/password AND limited its use to a few people based on their NT login (within the app that is). Your situation is harder since you'd have to give them the password and there is no controlling it from that point.
Ultimately they can just do a delete and incur the cost - the table would have be pretty substantial for it to matter. So they do have an option.
Andy
June 6, 2002 at 8:24 pm
I like your original idea but their point can be handled. But take a look at sp_start_job to allow them to run the process you mention.
Excerpt From SQL 2K BOL
quote:
Execute permissions default to the public role in the msdb database. A user who can execute this procedure and is a member of the sysadmin fixed role can start any job. A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.When sp_start_job is invoked by a user who is a member of the sysadmin fixed server role, sp_start_job will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin fixed server role, sp_start_job will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, sp_start_job will fail. This is only true for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and sp_start_job is always executed under the security context of the Windows 9.x user who started SQL Server.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 7, 2002 at 6:42 am
Hi,
I'm new on this busines, but if you put the user like owner of this table. In this way they can truncate the table.
I don't know, its a good idea?
Takeda.
June 7, 2002 at 7:42 am
Nope, not a a good idea. For production you don't people being able to do things like truncate the employees table by mistake.
Andy
June 7, 2002 at 7:59 am
I agree with Antrares and Andy. As a DBA who's been doing this for 9 years, I'd never give a develop access to the production box (except read only). It leads to problems.
Use sp_start_job or the .bat solution. They will be simpler and keep things controlled.
Steve Jones
June 8, 2002 at 6:36 am
Hi
db_ddladmin privs?? you have got to be joking! 🙂 my developers are lucky to get CRUD let alone anything else. Get them to write up and DTS, you transfer then schedule it. You need to clearly spell out right from the go that this sort of thing is bad practice and must be re-coded, simple as that (you may laugh, but programmers need a right kick sometimes as many are lazy and all expect admin privs to run their code). Many times Ive delt with similar senarios and lets face it, mistakes will cost you and your business a lot of time and money.
Cheers
Chris.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply