In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".
View 2 RepliesI want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?
Hi,
This thread is a reformulation of a prior thread.
I created a login 'Network service' at server level in Management Studio express.I use windows authentification.Then i defined an user for my database which is associated to login 'Network service', because the application asp.net uses that account (IIS 6.0). This user received db_read and db_write roles.This works.Now i experimented a little bit and i removed from the logins at server level the login 'Network service'.Result: the application still works..Then i removed the Builtinusers login from the login list at server level.Result: i get the error: "login failed for Network service".I recreated then the login 'Network Service' at server level but not the Builtinusers login.Result: it works again.My conclusion is: one of the two logins must be in the list: Network Service or BuiltinusersIs this right?Why do i get that error when both logins are removed and not only when Network Service is removed?Thanks
Does securityadmin Server level role can add, alter logins and corresponding users on all databases ?
If not what is the best role other thn SA to have to manager logins and users.
Please consider enhancing SQL 2005 Reporting Services to provide the ability to disable Clickthrough Reports (Live Links) at the Report and Export Function level. Today you can only enable or disable Clickthrough Reports at the View/Model Level for all Report Builder reports.
View 1 Replies View Related<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">Friends,</g>
<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">I</g>
i am migrating one database from SQL Server 2008 where I have around 20 databases to SQLServer 2012. To migrate the login I was thinking about to use SP_Help <g class="gr_ gr_114 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="114" id="114">revlogin</g> store procedure but now what I believe is this SP scripts out whole server level login and I don't need all the logins in the server except related to the database that I am migrating to.
<g class="gr_ gr_418 gr-alert gr_gramm Grammar multiReplace" data-gr-id="418" id="418">please</g>
best way to migrate logins only related to the specific database to the new server.
Hi All,
I am not able to see any of the jobs in the enterprise manager that are scheduled on one of our production servers. Have any body seen this type of behaviour of SQL server.. The jobs are there in the SQL as I can query sysjobs table to see there entry.. The jobs are being executed properly when they are supposed to..
Thanks
Ajay
Hello all,
we just moved our databases over from a SQL 2000 server onto a new SQL 2005 server. We had no problem migrating them over, but was wondering if there was a way to migrate the jobs over as well. We have about 20 of them and would prefer not to hav to create them all again on the new server.
Thanks in advance,
Phoenix
I have been struggling with this one for awhile now.I have a domain group which only must view the steps and history of all agent jobs.I have added the group to the sqlagentreadergroup.I have created a new role and denied this role,add job,update job,delete job etc execute permissions.But the user still can change ,delete or create a new job.
All the groups and users in th new role,does not have sysadmin rights.
we have sql 2012 enterprise version
What else can i try.I need this for audit purposes.
Hello,
I need to document all SQL Server Agent Jobs with full description of dependencies - database, table, stored procedure, etc.
Is there are a tool that I can download for that particular use???
Thank you so much
I created an agent job, and it runs fine, but I want more control over the scheduling. I need to schedule jobs to run Sunday through
Thursday at 8:00pm, skipping Friday.
How can I use the agent job interface and customize the schedule this way? Will I have to resort to a Windows Service and control the
scheduling that way?
Hi Guys,
I am not sure if this is the right place to put a question on SQL Server Agent.
I have scheduled some SSIS Jobs using the SQL Server Agent, and wanted to know how can I view detailed log information on how the Job ran (particularly Failure Information).
The information in the Log File Viewer to see the job execution log doesnt seem to help much - as it only says weather the Job Failed or Succeeded.
I need to see the reason and/or the step in a job where it failed!
Can someone help me out with some insight into this....
Thanks
Pramod
I have a few jobs in sql server 2005 agent that got corrupted that Iwould like to restore.
Do they get backed up and how can I restore them?
Just wondering if anyone knew if it was possible to create a SQL Server agent job that ran once every 30 seconds. I can't enter a value below 1 minute when editing the job schedule in Enterprise Manager.
Alternatively, I can create duplicate stored procedures and set one to run on the minute and one on the half minute. Thought there might be a better alternative to this though and I'll always have to be remembering to makes changes to both sprocs.
Thanks!
Hi. I was wondering if anyone has ever had a problem where nothing seems to be wrong with the server, and SQL Server Agent is up and running, but jobs fail. We had a job run at midnight, and it was the last successful run. Every job failed after that, with an error stating "Unable to retrieve steps for job..." There doesn't seem to be any reason for the problem. The jobs were kicked off manually and they all ran.
The only other error messages we could find were in the Application Event Viewer on the server. But the first error happened days ago and said this: "The description for Event ID ( 0 ) in Source ( .NET Runtime ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Unable to open shim database version registry key - v2.0.50727.00000."
Since then, every few seconds this error occurred: "Windows cannot load extensible counter DLL MSSQLServerOLAPService, the first DWORD in data section is the Windows error code."
We turned off the performance monitor counters and that error stopped. But we still have no idea if that was the cause of the problem.
This isn't the first time we've had an issue with jobs failing because the steps couldn't be 'found', while SQL Server Agent was up and running.
Has anyone else had this problem?
Thanks!
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 (ADadacct) 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.
Hi,
I have couple of questions about the scheduler:
I'd like my packages to run only on weekdays and disable a package if one of the first two packages fail. is it possible to do it?
Thanks
Gulden
Hello All,
My database application has certain stored procedures which are scheduled to run at certain predefined interval (say once everyday). Now to implement this logic I have two options with me, first being to use the EXECUTE SQL TASK of the SSIS package and schedule it using the windows scheduler and the other is to use the SQL Server Agent Jobs and schedule it there itself. Now the current implementation is using the SSIS packages and I even know that the second approach of using the SQL Server Agents Job is better.
The only thing I want to know is whether it makes sense to invest time in converting my SSIS packages (20 in all) to SQL Server Agent jobs or not. Can somebody briefly explain or give some pointers regarding the performance benefits of Agent Jobs over the SSIS packages.
Thanks in Advance
Mitesh
I want to schedule a database backup using SQL Server Agent Jobs how can i do this?
View 1 Replies View RelatedWhen I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status.
Use msdb
go
select distinct j.Name as "Job Name", --j.job_id,
case j.enabled
when 1 then 'Enable'
when 0 then 'Disable'
[Code] ....
I'm trying to execute my package using schedule in SQL Server Agent, I've already tested my package by run it manually in Integration services and it works. The table created, the data from my flat file also inserted into the table correctly and the result return with success.
The question is why when I execute my package using SQL Server Agent, the SQL Server Agent keep executing my package like infinite loop until I stop the job. after I stop the job there is no error generated by sql server. Could you figured why this happen?? I've already tried to upgrade into SP2 and set the package protection level and still not get good result from it. thank you.
I am actually the MOM administrator and only know enough SQL 2005 to keep mom working....sometimes I noticed on my dev mom box that SQL server agent has a lot of jobs configured to run against the mom onepoint database. The production box does not have these jobs. What would be the simplest way to export these jobs so that i can import them on the production box. Any help would be appreciated.
View 1 Replies View RelatedI am in the process of writing a generic PowerShell script (that executes T-SQL statements) to copy all or some databases from one SQL Server 2012 SP2 instance to a second instance that runs SQL Server 2012 SP2 or higher. The script is designed to be used on demand, and part of its execution will involve reconciling the logins between the source and destination instances.
I am looking for a reliable way to reconcile the logins between the instances in a somewhat repetitive (i.e., "on demand") fashion. There is no SSIS in the environment; so the SSIS transfer logins task to not available to me.
I have a series of jobs in the SQL server Agent that will begin and appear to run fine, but will never finish. Is there a way to limit the time that a job will run in the agent, for instance stop with failure if not completed within 1 hour?
Thanks for the help
Ken
I have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
Hi Guys,
Can you please advice me on if there is a limitation on SQL agent Jobs. If then how many jobs can SQL server accomedate.
If there is a limit, then is there are any ways to increase it?
I appreciate your quick response.
Thanks
I have several users that are a member
of a Windows Nt group. I want to be
able to allow this group to create,
update, and delete SQL Agent Jobs.
But I do not want them to be members
of the SYSADMIN role.
Everything that I have found states
that they must be members of this
server role to perform these actions.
Does anyone have an idea on how to
accomplish this? or point me in some
kind of direction to explore.
Windows 2000 sp2
Sql 2000 sp1
Thanks,
Robert
Hi all,
after creating the replication job (works) other agent jobs hang up. after restart the agent, the jobs work one time. t
can it be a problem with dieffernt agent profiles/accounts
many thanks
oliver
Is there anyway we could retrieve the job history for all the jobs in all the sql server using Central Management Server?
View 0 Replies View RelatedHi Group,
Is it possible to schedule and execute sql jobs from an asp page.
I am developing an application that requires an administration section. In this section the admin would like to change / update the schedule of the sql job and also run the jobs ad hoc. Basicall y they would like the functionality of sql agen in enterprise manager to be available from their web application.
Has anbody done this before ?
All information / solutions shall be psoted back to the group as I reckon this would be pretty useful functionality.
Thanks in advance
Tony
The SQL Server Agent process is running. When I use EM to start a database integrity
check job, it fails with an error message of 22029 and the following:
[LOG] SQLServerAgent security context does not have server-autorestart privileges
I can not find error 22029 in Books on Line. I will go to Microsoft Knowledge base
and research this problem.
In the mean time if someone can help, it is much appreciated.
Thanks to all.
I need to move SQL Agent scheduled jobs from one server to another..
Here are the details:
Source Server
SQL 2000 SP3 (upgraded from SQL 7)
Data stored in MsSQL7Data
target
SQL Server 2000 SP4
Data Stored in DATA
DTS Packages have already been resaved on Target Server, how can I copy the scheduled jobs over?
Thanks
Hi all,
I am trying to write a T-SQL script to change about 200 SQL Agent jobs to send notification to a different email notification list. I could do this manually but it would take me a lot more time. Is there a way to script this out in T-SQL? Thanks!