Server roles and DTS packages

[John Shahan]

Short of system administrator, does anyone know what server role a login must be assigned to in order to execute DTS package in Query Analyzer?

[rmiao]

You have to use xp_cmdshell to call dtsrun, anyone has exec permission on xp_cmdshell can do that.

[John Shahan]

My stored procedure was running sp_OACreate which is an extended stored procedure which requires sysadmin priviledges.

I wish I understood why those are nailed down so tight.

[Rawhide]

In SQL 2000, xp_cmdshell execute rights are granted only to members of the sysadmin server role. So unless someone grants you rights to it, you have to be a member of the sysadmin role.

[Rawhide]

Originally posted by John Shahan
I wish I understood why those are nailed down so tight.

For security. So that general users can't create or execute malicous or dangerous code or programs on the server.

[John Shahan]

So what do you do if you want a regular user to be able to execute a stored procedure that calls one of these?

[Rawhide]

In general, I don't let regular users execute these.

However, if they have a recurring task that can be and needs to be run on a regular schedule then I will set up a SQL Server job to run under the sa account. These instances are approved on a case by case basis.

Another option is to provide a SQL Server login for the application/website to use for the execution of this.

[John Shahan]

While I'm not really pleased with the available options, I need to say that I am very grateful for the benefit of your experience and willingness to share it.

I thank you.

[rmiao]

As said you can grant whatever permission to users with security risk.

[Rawhide]

rmiao brings up a good point.

In my company, all developes have sa permissions in the development environment. Our applications and websites use a SQL Server account that is granted extra permissions (though not sa) in production. So they can develop and test procedures that require sa permissions. In production, if their code requires permissions beyond the application role login, it has to be approved and scheduled by me to go into production.