September 23, 2003 at 3:09 am
Hi!
I need to allow many users to run a number of jobs, that start DTS packages.
What permissions and (or) server/database roles should I give to those users?
/* I really don't want to give them sa role */
Thanx.
September 23, 2003 at 3:13 am
If you are running SQL 2000 sp2 or below, you can give the TargetServerRole (MSDB database).
In sp3/3a this all changed. I'm currently looking into this hence don't have an answer to date.
I'll update this link if I find out.
Steven
September 23, 2003 at 3:23 am
It is MS SQL2000 sp3...
September 24, 2003 at 1:21 am
I've build a workaround to achieve this by having the users raising an alert. SqlAgent then reacts to that alert by launching a job (one per alert) that runs the dts.
This way users don't need more privileges, but you have no control to which user raises which alert.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 24, 2003 at 2:16 am
2alzdba:
quote:
I've build a workaround to achieve this by having the users raising an alert. SqlAgent then reacts to that alert by launching a job (one per alert) that runs the dts.
Can you tell a little more in detail how you all this?
September 24, 2003 at 4:03 am
I only have the alert responding when launched from within a certain database.
the first time, you'll have to stop/start SQLAgent.
Pitfall: When you disable the job which should be launched, you have to disable the alert-job-schema or SQLAgent will log and keep on trying to start the job until your diskdrive is full !!!!
Here's my script :
print @@servername
USE Master
go
Declare @MessageNummer int
Declare @JobNaam varchar(128)
Declare @DbNaam varchar(128)
set @MessageNummer = 60001 -- <-- TO BE REPLACED!!!
set @JobNaam = 'YourJobname' -- <-- TO BE REPLACED!!!
set @DbNaam = 'MyDb' -- <-- TO BE REPLACED!!!
-- We keep message-ranges to be able to support fast recovery to an other server
-- msgnr jobname server - database
-- 60001 - 60500 MyServer1 - MyDb
--
Declare @wrkstr1 varchar(128)
Declare @wrkstr2 varchar(128)
set @wrkstr1 = 'MyCompany Requesting Job ' + @JobNaam + ' to be launched.'
-- message toevoegen in Master
exec sp_addmessage @msgnum = @MessageNummer
, @severity = 10
, @msgtext = @wrkstr1
-- , @lang = 'language'
, @with_log = 'true'
-- , @replace = 'replace'
--go
-- Create Alert in MSDB
-- USE msdb
-- go
set @wrkstr1 = 'MyCompany_Requesting_Job_' + @JobNaam
set @wrkstr2 = 'Requesting Job ' + @JobNaam + ' to be launched.'
-- Alert definiëren in MSDB
exec msdb.dbo.sp_add_alert @name = @wrkstr1
, @message_id = @MessageNummer
, @severity = 0 -- moet zo omdat message_id opgegeven werd
, @enabled = 1 -- 0 = disabled / 1 = enabled
, @delay_between_responses = 0
, @notification_message = @wrkstr2
--, @include_event_description_in = 0
, @database_name = @DbNaam
--, @event_description_keyword ='Requesting Job ' + @JobNaam + ' to be launched.'
, @job_name = @JobNaam
-- , @raise_snmp_trap = raise_snmp_trap] niet in SQL 7.0
-- , @performance_condition = 'performance_condition'
--, @category_name = 'Application Events'
exec msdb.dbo.sp_help_alert
go
--
-- use MyDb
-- go
-- RAISERROR ( 60001 , 10 , 1 )
--
-- go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 24, 2003 at 7:21 am
You can also:
1. Create a user defined role (for example "MyRoleJobExec")in msdb
2. Add the users which you want to have permissions to execute the job to that role
3. Name you the job which you want to delegate permissions to with a distinct prefix not used by other jobs (for example "'MyJobNamePrefix).
4. Add 2 lines to the system view sysjobs_view:
OR ((ISNULL(IS_MEMBER(N'MyRoleJobExec'), 0) = 1)
AND name like 'MyJobNamePrefix%')
The users which have been added to the role will only have permissions to execute jobs with the distinct prefix.
I've mentioned this techinque in several postings and some folks are very much against modifying system views (which I can understand). However I have been using this technique for over 4 years from SQL 7 through the current SQL 2000 service pack w/o issues. I think as long as you document this change and keep in mind the behavior can change from release to release, this is an acceptable workaround.
September 25, 2003 at 2:23 am
How do you want the users to start the jobs? Are they going to have Enterprise Manager installed?
If you have access to a web server running the .NET Framework v1.1, I can send you some code I’ve written which utilizes NET impersonation by allowing the user to navigate to a web page where they can click on a link which executes code that kicks off the job. I use file system ACL’s and Active Directory Domain Local Groups to control who has access to the web page and what they can see when they get there.
It a little more work but this way my users don’t have to learn anything new or install any new software. All they have to know is the URL.
September 25, 2003 at 2:57 am
quote:
How do you want the users to start the jobs? Are they going to have Enterprise Manager installed?If you have access to a web server running the .NET Framework v1.1, I can send you some code I’ve written which utilizes NET impersonation by allowing the user to navigate to a web page where they can click on a link which executes code that kicks off the job. I use file system ACL’s and Active Directory Domain Local Groups to control who has access to the web page and what they can see when they get there.
It a little more work but this way my users don’t have to learn anything new or install any new software. All they have to know is the URL.
- No EM or QA for users! They all are using an app. The new version of this app is web-based. The server has .NET Framework v1.1. So it would be very nice if you send me the code (e-mail: roustam@hotbox.ru)
Thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply