.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
sivanagamahesh
Post New Web Links

SQL Agent Roles and the Ability of an AD Group to Execute Jobs

Posted By:      Posted Date: November 25, 2010    Points: 0   Category :Sql Server
 
I would like to give an AD group the ability to execute Agent Jobs using the 2005 Agent roles (SQLAgentUserRole, etc) but you can not assign ownership of a job to a group. I have seen some work arounds and the only one I am considering is using a proxy account. Does anyone have any insight on doing this? This is certainly a fairly common thing to do, give support technicians the ability to run a job.  Running sql server 2005 standard ed, sp3 on a windows server 2003 standard r2 ed, thanks


View Complete Post


More Related Resource Links

Errors when using Proxy/Credentials to execute SQL Agent jobs that access SSIS packages

  

Running SQL Server 2008, on Windows Server 2008.

I'm having a problem trying to execute a job.  The job has executed successfully when I configure the SQL Agent account (domain account with sysadmin permissions in the instance) as the owner/executor of the job.  However, I want to tighten down security so I'm trying to use a proxy/credential.

Here are the steps I've followed:

1) create a non-privileged SQL account called "SQLJobOwner" that owns the job.

2) create a stored credential (domain account) that has an account on the local machine where the instance resides, and has "log on as batch" permissions.

3) created a login to the instance for the domain account that is stored as a credential, and granted sysadmin permissions to the account.

3) create a new proxy called "SSISProxy", and granted access to the SSIS subsystem.

4) Added job owner "SQLJobOwner" as a principal in the SSISProxy configuration window.

5) changed the job step that executes the SSIS package to "Run as" the "SSISProxy".

 

When I execute the job, the job fails with the following error:

Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 1).  The step failed.

In addition, I get

sql server agent roles and permissions

  
hi does any one have idea of what kind of roles are needed for creating a SQL server agent job and why those roles are needed?? Please Reply ASAP Because i don't have sysadmin rights.........so that other developers can use the same login for creating the jobs Thanks in advance

How to create SQL-login with permissions for view-only SQL-Agent-Jobs?

  
Could you please help me with resolving next problem: How to create SQL-login with permissions for view -only SQL-Agent-Jobs (he cannot create, modify or delete)? I am using MS-SQL-Server version: 8.00.194 In other words : how to create in version 2000 (8.00.194) the same as 'SQLAgentReaderRole ' in version 2005 (for more details see http://technet.microsoft.com/en-us/library/ms188283.aspx).

How can I get a list of users that are able to start/stop/execute jobs on sql server?

  
I know that the users with sysadmin roles are able to start/stop/execute jobs but I wonder if there are another roles or permissions that enable users to do this.   regards,

Fixed Agent Roles acting different after restore of MSDB

  
SQL Server 2008 v. 10.0.2531 We recently had to expand the C: drive on one of our servers. So we re-installed SQL Server after the expansion & then we just attached all of our user databases, re-mapped logins, & restored the MSDB backup from the old installation. Everything went well but I'm seeing some weird behavior with regards to the Fixed Agent Roles. A user that was a member of all three roles was getting an error when trying to run a job. The error was: User does not have access to   xp_sqlagent_is_starting in mssqlresource please grant execute and xp_sqlagent_notify in mssqlresource please grant execute   I was able to get around it by adding the user to the master DB with public access & granting them execute on these two extended procs but I would really like to know what happened here. It seems those roles should have access to those procs by default if they are needed to perform the tasks they were intended to perfom. Any idea what I may have done wrong here?

Scripting SQL Agent Jobs With Powershell

  
Hi,   I have a Sqlserver 2008 and about 120 Sql Agent jobs. We are planning to create new environments and I need to create the same jobs in all the new environments. Now i can script each job individually and then run them on the new servers but i was wondering if there is a Powershell way of looping though all the jobs and generate scripts for them and put them all in one big Sql, so i can just run that 1 script in any new environments that we add to create all the jobs that we have.   I found the below script online and am trying to make changes to it but cannot find the methods to scripts a existing object   # Load SMO extension [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null; # Get List of sql servers to check $sqlservers = Get-Content "$Env:USERPROFILE\sqlservers.txt";   # Loop through each sql server from sqlservers.txt foreach($sqlserver in $sqlservers) {       # Create an SMO Server object       $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;         # Jobs counts       $totalJobCount = $srv.JobServer.Jobs.Count;       $failedCount = 0;       $successCount = 0;         # For each jobs on the server &n

Sql Server Agent stops executing jobs

  

Hello everyone.  I'm having a problem with the sql server agent occasionally not executing its scheduled jobs, and unless the sql server agent is restarted they will not execute according to their schedule.  I've read many posts on people having problems with running a package manually and then not being able to run it in a job, although that is not my problem since these are jobs that have all run successfully historically, and only until the agent is restarted do they start working again.  It is the strangest thing.....we have many different jobs on this server and all of them will just mysteriously stop being executed.  Two other things of note, the agent and server processes both run on AD accounts, and we receive this error:

 

SQL Server Scheduled Job 'X' - Status: Failed - Invoked on: 2007-10-27 02:00:00 - Message: The job failed.  Unable to determine if the owner (AD\adacct) of job 'X' has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).

 

Any help would be greatly appreciated.

sending mail in SQL using SQL Server Agent Jobs

  

Hi,

 I am using SQL Server 2008 and I want to create one job in that.

 Job should be like that it will run any T-SQL and the output of that query

should be mail to me. But I am not able to do this.

Please help me...


SSIS Jobs VS SQL Server Job Agent

  

Which one of them is better to just run a set of Strored Procedures on a daily basis.

Can I have some Adv and Dis advantages. Please.

Thanks


SSIS Package fails with Execute Process Task when run from SQL Agent Job

  

I have an SSIS 2008 package running on Win 2008 64-bit server. The package has an Execute Process Task that runs a net use command using cmd.exe to make a connection.

This runs successfully when tested from the command prompt and runs successfully when the package is run in Visual Studio 2008. However, when the package is called from a SQLAgent job the package fails with the error (actual servernames and passwords removed):

Failing Task Name = EPT Connect mapped drive Error Code = -1073573551 Error Detail = In Executing "C:\WINNT\system32\cmd.exe" "/C net use
\\servername\foldername /USER:userid password" at "", The process exit code was "1" while the expected was "0".

I have determined that a successfull connection should provide a exit code of 0.

The job runs under a proxy account but as explained when the package has run from within VS 2008 it was also tested to run under the proxy account and is successful.

It is not a 64-bit issue as I have tested it by running the package using the 32-bit version of dtexec.exe and the same problem occurs.

Also I have found that by running a simple command process within the Execute Process Task such as dir *.* it is successful when run from the job indicating that it is in fact something related to the net use command itself when run from

Migrating SQL Server 2005 databases, including SQL Agent jobs and SQL users

  

Hi,

I should begin to migrate all my data from a SQL Server 2005 (x86) SP3 to another server with SQL 2005 (x64) SP3 installed. About moving the Databases, I think that detach and attach them won't create unexpected problems.

The problems that scare me are:

  1. Migrate the SQL Agent processes. I've created so complex jobs that I'd rather swim across an ocean than rewrite them.
  2. Migrate Users. There are near 70 users with integrated security and each has different mappings to the 40 DBs. Unfortunately there's no domain, so I don't know whether it's possible to copy them without changing their GUIDs.

Does anybody know if my goal is within reach?

Can someone tell me which step to make (and in what order)?

Thank you very much.


SQL agent jobs(inside guests) are struck with their past readings

  

Under HyperV - turning off host with guests running, they are saved with no dataloss. However on restarting the HOST, SQL agent jobs(inside guests) are struck with their past readings as seen in JobActivity Monitor and none of the jobs are running automatically unless you; (a) delete and recreate the schedule. (b) manually run the jobs at once. (c) restart the agent service. [So] Is there some thing available for a fix on this. (tested on SQL 9 SP3 & 10). Guest machines are going for SavedState but there should be something in SQL Server to update the detail (timings) for job execution.


yup

SQL agent jobs(inside guests) are struck with their past readings

  
Under HyperV - turning off host with guests running, they are saved with no dataloss. However on restarting the HOST, SQL agent jobs(inside guests) are struck with their past readings as seen in JobActivity Monitor and none of the jobs are running automatically unless you; (a) delete and recreate the schedule. (b) manually run the jobs at once. (c) restart the agent service. [So] Is there some thing available for a fix on this. (tested on SQL 9 SP3 & 10)
yup

Errors when trying to execute output of PowerScript to recreate jobs

  

I am using this code to create (script out) SQL Agent job create code to one file- I use the Advanced tab in the SQL Agent Step (Powerscript step) to output the results to the file.  I get errors from the output file when I try to parse or run it because the variables keep being redefined for each step. Can anyone help me?

Code

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server"
$srv.JobServer.Jobs | foreach {$_.Script()}

Errors

Msg 134, Level 15, State 1, Line 95

The variable name '@ReturnCode' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 134, Level 15, State 1, Line 104

The variable name '@jobId' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 243

The label 'QuitWithRollback' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 245

The label 'EndSave' has already been declared. Label names must be

Give non-sysadmin user access to SQL Agent Jobs

  

Hi, I have a situation where a group of users (domain ad group) need access to create and edit SQL Agent Jobs. The group can not be sys admin. I tried with the server role SQLAgentOperatorRole. but that doesn't give access to edit all SQL Agent Jobs, only the one that the users owns.

Thanks in advance


Pkg (Server) or Agent to Remotely execute another Pkg on workstation (PC)

  

Good day, i need help.

Migrating Servers & SQL from 2005 to 2008.

We have a package that 1.extracts data & 2.process cognos cubes on the old server, do not want to install Cognos on new server as it will be discontinued in a few months. Old server will also be trashed soon.

I have been asked to manually check when 1.extracts data pkg has completed & then Manually 2.process cognos cubes on my PC.

I do not want want this, Is there a way from within the one pkg or agent to Remotly execute another pkg on my PC?

Please Assist, really would be great if i could do this.


executing bat files in SQL Server Agent Jobs

  

hi.

there are 2 batfiles running in daily maintenance plans. one, compressing Backups using GZIP and the other deletes old Backup Files.

the delete.bat uses "forfiles" command and successfully deletes files. recenlt the maintenance plan continued to failed and to investigate i foundout that when there are no files met for  the bat files' criteria, it is being recorded as an error and job faliure in SQL Server.

for example, when there are no files to be deleted, the bat file returns a message like "ERROR NO FILES FOUND UNDER THE GIVEN CRITERIA"

this message is interpreted as an error in the SQL Server JOb which is actually not.

 

do i have a way of handling this kind of errors?

 

please reply

 

noviceDBA

 


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend