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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

sql server agent roles and permissions

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

SQL Agent - service account permissions - SQL Server 2008

Hi @ all   I installed two SQL Server 2008 on Server 2008 R2 Std (principal and mirror) and an AD Server 2008, with sperate service accounts, connect as SA, localy all works fine. I created some agent tasks (PowerShell, T-SQL), but I get some Error massages in the history, that service account of SQL Agent didn't have the permission to query a remote machine(access denied for wmi (HRESULT: 0x80070005 (E_ACCESSDENIED)) and linked database(SQLSTATE 42000 Error(7314)). The simple query with SA permissions on the remote machine works and the powershell scripts with the local domain user works too. But not with the SQL Agent. WHY?? Where ist the different between the user account permisions and service account permissions? Which settings are needed? Example: get-wmiobject -class win32_service -computername 192.168.xxx.xxx| where {$_.name -like '*SQL*'} Powershell Console: works                                     SQL Agent Job: access denied I tried some solutions with user rights, group policies and security permissions but nothing works. like: Configuration -Service Accounts, SQL Server or SQL Server Agent service account http://support.microsoft.com/kb/283811/en-us http://msdn2.microsoft.com/

sql server agent - job schedule 22022 error


Hi ! I have scheduled a job in sql server 2008 to send birthday e-mails. I run the script and it looks wroking but in agent schedule it doesn't. I am getting the below error; what is the problem?

TITLE: Microsoft.SqlServer.Smo
Start failed for Job 'Sending_transferdb_birthdate_e-mails'. 
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Start+Job&Link

Error while executing a package through SQL Server Agent

Hi All,   I have a ssis package. It has 3 tasks, first task updates a record in Oracle database to set an Indicator to “Y”, second task process SSAS Cube and the third again updates the same record and sets the indicator to “N”. This package has 2 data sources, one is Oracle and the other is Analysis Services. I gave the credentials for the Oracle and use NT Authority for Analysis services.   When I am executing from BIDS package is executing successfully. But when I am calling the job to execute this package its throwing me the error. Below is the error.     Message Executed as user: User\Username. ...ion 9.00.4035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:57:46 AM  Error: 2010-08-27 10:57:46.79     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2010-08-27 10:57:47.16     Code: 0xC0202009     Source: Ord_test Connection manager &q

How to check effective permissions for a user or group for a SQL server object or whole server?

Guys, How do I see effective permissions for a user or group for a SQL server object or whole server? For example, a user is not listed in security/logins, but he is a member of few groups and some of them have assigned permissions on SQL server (again either directly or through membership in other groups) Even when I select an object (a table), check properties/permission – it doesn’t let me select any principal, except those listed on security/logins… Anyway, what is the right way to see effective permissions for a user or group? Sorry for asking such a simple question, I’ve searched but found nothing relevant.

Error starting SQL Server Agent - Could not load the DLL xpstar90.dll. Reason: 126

AD Service account password was changed.  Now the SQL Server Agent (MSSQLSERVER) will not start even after updating the password.  Have no access into the databases.  Log files say "Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)."  Service Account has Full rights on system.  Any help in solving the problem would be great.

Install on New Windows7 - Getting Invalid SQL Server Agent credentials

Hi: I have a Developer License for MSSQL Server 2008.  Have just upgraded to Windows 7 Professional Edition.  When installing MS SQL Server, the setup shows that I have an instance of SQL Server Express already installed.  I am given a choice between Default Instance and Named Instance.  First time I chose named instance and then used the name of my machine - RBS1.  I received the following message:  The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service. Went back and tried Default instance and got same answer.  Can't proceed to install.  Any help in this greatly appreciated. roger  

SQL Server 2008 Agent Fails to start in a Win 2008 Cluster

When I try to bring SQLAGENT Online, I get the following errors: EventID:53 [sqagtres] StartResourceService: Failed to start SQLSERVERAGENT service.  CurrentState: 1 [sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 435) [sqagtres] OnlineThread: Error 435 bringing resource online.   I just did a fresh SQL Cluster install as well.  When I installed the cluster, the service account was good to go.  I cant figure out what is going on.  SQL Server Engine starts fine.  Any ideas?

Sql Server Job Agent vs Web Services

I've been tasked with coming up with a way to query our RS6000 and using the results to update records in the SQL Server 2005 db. Our RS6000 vendor has created an xml query which we call from an url. We query a table, loop through the results and call the xml query for each record. Then we check for a certain status in the xml results and, if we find it, update the db record. My question is which route would be better; building a web service in .Net or running a vb script from the SQL Server Agent? I normally do such things from a Web Service or an .exe setup under scheduled tasks on the server, but someone suggested SQL job agent route and I was wondering which would be more effiecient?Thanks in advance.

Checking SQL Server User Roles and Creating SQL Server Users using VB.NET

Hello gurus!Firstly I want to apologies if this question is out of place here.. if someone can direct me to the correct forumn great and Thanks!I have a VB.NET application which uses its own Backend Database (MSSQL Server). I need to distribute this application to sites where there will be an existing SQL Server.So I will need to Create the Database on this server. The Application includes methods for building the database on startup if not already connected to one.However the users windows logon may not have the correct permission to connect and create a Database on the Server. I have a DB Setup form in my application which asks for the Servname, Username, Password and Database name. I have catered for Windows Authentication and SQL Server Authentication within the form - the user makes the choice.Assuming they enter a Username and Password for SQL Server Athentication then I will be trying to connect using this user and create the database on the given server. The following is my outline logic:-                                                                  Create db Process                                                                             |                                                                             |                                                               Check Credentials                                                                   / 

Another merge agent for the subscription or subscriptions is running, or the server is working on a

Hi All, Using Merge Replication over the web (https). Server is running SQL Server 2008, client using SQL Server Express 2008. I am getting these error messages while trying to synchronize, and it won't let me sync: {call sp_MSensure_single_instance (N'{459D0BBA-53EC-4F65-AF52-E7DA478841DA}', 4)} Another merge agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent. Can you please advice what can be done to fix. Do I need to kill a process in SQL Server?

SQL Server Agent not listed as cluster resource

Hello everyone, thanks for any help on this issue.  I have recently installed SQL 2008 R2 on a Server 2008 R2 machine cluster.  We ran the advanced cluster prep and completion.  I have SQL Server up and running properly and failing over between nodes.  In the failover cluster manager, when I select SQL Server, I do not have the SQL Server Agent listed under other resources.  When I try to start the Agent from the SQL Configuration Manager it fails with an event 103 and I've found several notes on this error but I am figuring the root issue is that there isn't a cluster resource for the Agent. Is it possible to create the resource for the Agent? I have run a repair and I have tried to use add a resource, generic service, sql agent but it fails when I attempt to bring it online. Thanks again for any help on this. Mike

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).

SQL Server Agent Job

I want to run the following code as a regularly scheduled job using SQLServerAgent: exec sp_attach_db @dbname = 'district', @filename1=  'c:\data\engineering\billing\billingrSource\work_data.mdf', @filename2=  'c:\data\engineering\billingr\billingrSource\district_log.ldf' The job fails to execute. What I have determined is that certain jobs. They include dropping databases, creating new tables inside the database "district" and filling the database by querying other tables. However, creating a database does not work. However, if I start a new query using MS Management Studio, and place the code inside the query window, it executes cleanly. I assume it's a permissions issue, I just don't know where to start.

SQL Server 2005 Express Edition - GUI to set permissions on stored procedures

Hi there, I have SQL Serve 2005 Express Edition (Build 2600: Service Pack 3) installed; I also have the Management Console installed. My problem is that I cannot set execute permission, or any other type of permissions on my stored procedures through the GUI, as the Property menu item is missing from the right click menu. I read somewhere that this happens when you have SP1, but as I stated above I have got SP3 installed... Any help?   Regards, D.

Sql Server 2k8 Agent Service

I was going thru the tutorial and stopped sql server agent service with 'other' as the start mode, and now I cannot get it to come back online. I received the 0x800706be error. I also tried using a built in account and that didn't work either.

Login goes to Sql server for users and to sqlExpress for roles

Hi all,I have a site witl forms authentication using te login control. I altered my sql server, I added a connectionstring and used the connectionstring in both, <rolemanager> and <Membership>. That part of the web.config is listed below.The pronlem is that the login control goed to SQLserver to check the users and their passwords, but it goed to the SQLExpress database for the roles..... who can help me because it's driving me crazy!!!!!!<connectionStrings>    <clear/>    <remove name="Conn"/>    <add name="Conn" connectionString="Server=SERVER;Database=DBNAME;User Id =USERNAME;Password=PASSWORD;Integrated Security=False"                       providerName="System.Data.SqlClient"/>    <remove name="LocalSqlServer"/>    <add name="LocalSqlServer" connectionString="Server=SERVER;Database=DBNAME;User Id =USERNAME;Password=PASSWORD;Integrated Security=False" providerName="System.Data.SqlClient"/>  </connectionStrings>  <system.web>    <membership>      <providers>     &nbs

Validating cluster resource: SQL Server and SQL Server Agent services

SQL Server 2008 R2 installed on a Windows Server 2008 R2 2-node cluster. Cluster validation wizard warns for SQL Server and SQL Server Agent services: This resource is configured to run in a separate monitor. By default, resources are configured to run in a shared monitor. This setting can be changed manually to keep it from affecting or being affected by other resources. It can also be set automatically by the failover cluster. If a resource fails it will be restarted in a separate monitor to try to reduce the impact on other resources if it fails again. This value can be changed by opening the resource properties and selecting the 'Advanced Policies' tab. There is a check-box 'run this resource in a separate Resource Monitor'. "Should" SQL Server and SQL Server Agent services run in shared (default) or separate monitors in this environment?
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