Changing the MSSQLServer service account causes SQL Agent could not start

Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
running either a domain account or local system account, i.e.
MSSQLServer service is under a domain account, SQL Agent is under
local system.
For a reason, we need to switch the domain account to the other domain
account, the MSSQLServer service is started up with no problem but the
SQL Agent is not able to start up neither in local system or new
domain account, the error message is: "The SQL Server Agent
(MSSQLSERVER) service on Local Computer started and then stop
automatically if they have no work to do, for example, the Performance
Logs and Alerts service. "
Switch back to original domain account is fine, let the MSSQLServer
running on local system account is fine too for both MSSQLServer and
SQL Agent.
P.S. Both old/new domain accounts are in the server's local admin and
domain administrator groups and as well as SQL sa.
What is needed to use a domain account as the SQL service account?
Thanks,
Yvette.
Hi Yvette
"yvette.ye@.gmail.com" wrote:

> Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
> running either a domain account or local system account, i.e.
> MSSQLServer service is under a domain account, SQL Agent is under
> local system.
> For a reason, we need to switch the domain account to the other domain
> account, the MSSQLServer service is started up with no problem but the
> SQL Agent is not able to start up neither in local system or new
> domain account, the error message is: "The SQL Server Agent
> (MSSQLSERVER) service on Local Computer started and then stop
> automatically if they have no work to do, for example, the Performance
> Logs and Alerts service. "
> Switch back to original domain account is fine, let the MSSQLServer
> running on local system account is fine too for both MSSQLServer and
> SQL Agent.
> P.S. Both old/new domain accounts are in the server's local admin and
> domain administrator groups and as well as SQL sa.
> What is needed to use a domain account as the SQL service account?
> Thanks,
> Yvette.
>
For the account types that can be used see
http://support.microsoft.com/kb/907557
and http://support.microsoft.com/kb/283811 describes the requirements for
the accounts if you don't use EM or SCM to change the account.
John
|||"John Bell" wrote:

> Hi Yvette
> For the account types that can be used see
> http://support.microsoft.com/kb/907557
> and http://support.microsoft.com/kb/283811 describes the requirements for
> the accounts if you don't use EM or SCM to change the account.
> John
Also try http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John

Changing the MSSQLServer service account causes SQL Agent could not start

Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
running either a domain account or local system account, i.e.
MSSQLServer service is under a domain account, SQL Agent is under
local system.
For a reason, we need to switch the domain account to the other domain
account, the MSSQLServer service is started up with no problem but the
SQL Agent is not able to start up neither in local system or new
domain account, the error message is: "The SQL Server Agent
(MSSQLSERVER) service on Local Computer started and then stop
automatically if they have no work to do, for example, the Performance
Logs and Alerts service. "
Switch back to original domain account is fine, let the MSSQLServer
running on local system account is fine too for both MSSQLServer and
SQL Agent.
P.S. Both old/new domain accounts are in the server's local admin and
domain administrator groups and as well as SQL sa.
What is needed to use a domain account as the SQL service account?
Thanks,
Yvette.Hi Yvette
"yvette.ye@.gmail.com" wrote:
> Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
> running either a domain account or local system account, i.e.
> MSSQLServer service is under a domain account, SQL Agent is under
> local system.
> For a reason, we need to switch the domain account to the other domain
> account, the MSSQLServer service is started up with no problem but the
> SQL Agent is not able to start up neither in local system or new
> domain account, the error message is: "The SQL Server Agent
> (MSSQLSERVER) service on Local Computer started and then stop
> automatically if they have no work to do, for example, the Performance
> Logs and Alerts service. "
> Switch back to original domain account is fine, let the MSSQLServer
> running on local system account is fine too for both MSSQLServer and
> SQL Agent.
> P.S. Both old/new domain accounts are in the server's local admin and
> domain administrator groups and as well as SQL sa.
> What is needed to use a domain account as the SQL service account?
> Thanks,
> Yvette.
>
For the account types that can be used see
http://support.microsoft.com/kb/907557
and http://support.microsoft.com/kb/283811 describes the requirements for
the accounts if you don't use EM or SCM to change the account.
John|||"John Bell" wrote:
> Hi Yvette
> For the account types that can be used see
> http://support.microsoft.com/kb/907557
> and http://support.microsoft.com/kb/283811 describes the requirements for
> the accounts if you don't use EM or SCM to change the account.
> John
Also try http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John

changing table ownership

Did something change with the sp_changeobject owner with SQL2005. The
command does not seem to work. I am looking to change the owner of all the
tbale of a SQL2005 database. Is there a quick way to do it? Thanks.
Tom
ALTER SCHEMA targetschema TRANSFER sourceschema.Tblname;
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:umlac5lnIHA.5472@.TK2MSFTNGP03.phx.gbl...
> Did something change with the sp_changeobject owner with SQL2005. The
> command does not seem to work. I am looking to change the owner of all the
> tbale of a SQL2005 database. Is there a quick way to do it? Thanks.
>
|||Did you read the 2005 Books Online regarding this procedure? It should work, but since we now have
user-schema separation it is better to use the more modern what, where you can decide whether it is
the schema (ALTER SCHEMA) or the owner (ALTER AUTHORIZATION) to change.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:umlac5lnIHA.5472@.TK2MSFTNGP03.phx.gbl...
> Did something change with the sp_changeobject owner with SQL2005. The command does not seem to
> work. I am looking to change the owner of all the tbale of a SQL2005 database. Is there a quick
> way to do it? Thanks.
>
sql

changing table ownership

Did something change with the sp_changeobject owner with SQL2005. The
command does not seem to work. I am looking to change the owner of all the
tbale of a SQL2005 database. Is there a quick way to do it? Thanks.Tom
ALTER SCHEMA targetschema TRANSFER sourceschema.Tblname;
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:umlac5lnIHA.5472@.TK2MSFTNGP03.phx.gbl...
> Did something change with the sp_changeobject owner with SQL2005. The
> command does not seem to work. I am looking to change the owner of all the
> tbale of a SQL2005 database. Is there a quick way to do it? Thanks.
>|||Did you read the 2005 Books Online regarding this procedure? It should work, but since we now have
user-schema separation it is better to use the more modern what, where you can decide whether it is
the schema (ALTER SCHEMA) or the owner (ALTER AUTHORIZATION) to change.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:umlac5lnIHA.5472@.TK2MSFTNGP03.phx.gbl...
> Did something change with the sp_changeobject owner with SQL2005. The command does not seem to
> work. I am looking to change the owner of all the tbale of a SQL2005 database. Is there a quick
> way to do it? Thanks.
>

Changing Permissions for all Stored Procs in a SQL2005 DB

I have a massive number of Stored Procedures in a SQL Database. I need to
give a Database Role in that Database Execute permissions for all of the
Stored Procedures I had created (but not the System Stored Procedures). Doin
g
this through the SQL Management Tool UI will take FOREVER. Is there another
way? If it's a script of some kind, can someone give me a hint or a sample?
This may be a bit beyond me.
AlexHi,
In SQL Server 2005 it is very easy to set the execute rights to all
procedures. See the script
USE DBNAME
GO
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
Assign this role to the database user and he will be able to execute all
procedures inside the databse
THanks
Hari
SQL Server MVP
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:1BCFF9ED-12DC-4FD3-921F-3A1E185DB18E@.microsoft.com...
>I have a massive number of Stored Procedures in a SQL Database. I need to
> give a Database Role in that Database Execute permissions for all of the
> Stored Procedures I had created (but not the System Stored Procedures).
> Doing
> this through the SQL Management Tool UI will take FOREVER. Is there
> another
> way? If it's a script of some kind, can someone give me a hint or a
> sample?
> This may be a bit beyond me.
> Alex|||Hi Alex,
Thanks for posting.
To execute a stored procedure, you have to be granted execute permission on
this stored procedure. As we know, stored procedure referenced as object in
SQL 2005. To grant permission on a object, we have a T-SQL command: Grant.
You can Grants permissions on a table, view, table-valued function, stored
procedure, extended stored procedure, scalar function, aggregate function,
service queue, or synonym.
For more information about Grant command, please refer to following link:
http://msdn2.microsoft.com/en-us/library/ms188371.aspx
Hope this information helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
--[vbcol=seagreen]
Doing[vbcol=seagreen]
another[vbcol=seagreen]
sample?[vbcol=seagreen]|||Hari -
Hi. This is very helpful. Can I ask one more detail? Assuming that I've
already created the database role, if I do "GRANT EXECUTE TO MyNewRole", wil
l
this grant execute permissions to ALL of the stored procedures in the
database (including system stored procedures)? I don't really want to do
that. Is there a way that I can have it grant execute only to stored procs
that I created (dbo.<procname> )? Or can I do it by matching a pattern in the
SP name? All of my stored procedures start with a special 3 character prefix
.
Can I use a "where" clause or something? And if I can, how?
Let me know?
Thanks!
Alex
"Hari Prasad" wrote:

> Hi,
> In SQL Server 2005 it is very easy to set the execute rights to all
> procedures. See the script
> USE DBNAME
> GO
> /* CREATE A NEW ROLE */
> CREATE ROLE db_executor
> /* GRANT EXECUTE TO THE ROLE */
> GRANT EXECUTE TO db_executor
> Assign this role to the database user and he will be able to execute all
> procedures inside the databse
> THanks
> Hari
> SQL Server MVP
>
> "Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
> news:1BCFF9ED-12DC-4FD3-921F-3A1E185DB18E@.microsoft.com...
>
>|||Hi,
As far as I know you need to give EXEC privilages to indvidual stored
procedures.
sample script:-
Run this in the database, then copy the results to the query window
and execute.
select 'Grant EXEC on ' + name + ' to WhomEver'
from sysobjects
where type = 'P'
Thanks
Hari
SQL Server MVP
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:E57C2633-AE66-4837-8769-A33CB3C36CE1@.microsoft.com...[vbcol=seagreen]
> Hari -
> Hi. This is very helpful. Can I ask one more detail? Assuming that I've
> already created the database role, if I do "GRANT EXECUTE TO MyNewRole",
> will
> this grant execute permissions to ALL of the stored procedures in the
> database (including system stored procedures)? I don't really want to do
> that. Is there a way that I can have it grant execute only to stored procs
> that I created (dbo.<procname> )? Or can I do it by matching a pattern in
> the
> SP name? All of my stored procedures start with a special 3 character
> prefix.
> Can I use a "where" clause or something? And if I can, how?
> Let me know?
> Thanks!
> Alex
>
> "Hari Prasad" wrote:
>|||> Is there a way that I can have it grant execute only to stored procs
> that I created (dbo.<procname> )? Or can I do it by matching a pattern in
> the
> SP name? All of my stored procedures start with a special 3 character
> prefix.
> Can I use a "where" clause or something? And if I can, how?
One method is to generate a script to do this. The example below was
developed for SQL 2000 but works with SQL 2005 too.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO MyNewRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1 AND
ROUTINE_SCHEMA = N'dbo' AND
ROUTINE_NAME LIKE N'usp%'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:E57C2633-AE66-4837-8769-A33CB3C36CE1@.microsoft.com...[vbcol=seagreen]
> Hari -
> Hi. This is very helpful. Can I ask one more detail? Assuming that I've
> already created the database role, if I do "GRANT EXECUTE TO MyNewRole",
> will
> this grant execute permissions to ALL of the stored procedures in the
> database (including system stored procedures)? I don't really want to do
> that. Is there a way that I can have it grant execute only to stored procs
> that I created (dbo.<procname> )? Or can I do it by matching a pattern in
> the
> SP name? All of my stored procedures start with a special 3 character
> prefix.
> Can I use a "where" clause or something? And if I can, how?
> Let me know?
> Thanks!
> Alex
>
> "Hari Prasad" wrote:
>

Changing name on Sql server

I change the name on my SQL2005 server, after that i run sp_dropserver and
sp_addserver, but when i tru do delete som sql agent job or create new one
iam getting error:"An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server) (.Net SqlClient Data Provider)"
any help please
Did you restarted SQL when you changed SQL names?
And also one thing ... did you executed
"sp_addserver 'servername', local"?
"Viktor" <serguienkov@.hotmail.com> wrote in message
news:ef9Sza%23yHHA.1776@.TK2MSFTNGP03.phx.gbl...
>I change the name on my SQL2005 server, after that i run sp_dropserver and
>sp_addserver, but when i tru do delete som sql agent job or create new one
>iam getting error:"An error has occurred while establishing a connection to
>the server. When connecting to SQL Server 2005, this failure may be caused
>by the fact that under the default settings SQL Server does not allow
>remote connections. (provider: Named Pipes Provider, error: 40 - Could not
>open a connection to SQL Server) (.Net SqlClient Data Provider)"
> any help please
>

Changing name on Sql server

I change the name on my SQL2005 server, after that i run sp_dropserver and
sp_addserver, but when i tru do delete som sql agent job or create new one
iam getting error:"An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server) (.Net SqlClient Data Provider)"
any help pleaseDid you restarted SQL when you changed SQL names?
And also one thing ... did you executed
"sp_addserver 'servername', local"?
"Viktor" <serguienkov@.hotmail.com> wrote in message
news:ef9Sza%23yHHA.1776@.TK2MSFTNGP03.phx.gbl...
>I change the name on my SQL2005 server, after that i run sp_dropserver and
>sp_addserver, but when i tru do delete som sql agent job or create new one
>iam getting error:"An error has occurred while establishing a connection to
>the server. When connecting to SQL Server 2005, this failure may be caused
>by the fact that under the default settings SQL Server does not allow
>remote connections. (provider: Named Pipes Provider, error: 40 - Could not
>open a connection to SQL Server) (.Net SqlClient Data Provider)"
> any help please
>

Changing name on Sql server

I change the name on my SQL2005 server, after that i run sp_dropserver and
sp_addserver, but when i tru do delete som sql agent job or create new one
iam getting error:"An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server) (.Net SqlClient Data Provider)"
any help pleaseDid you restarted SQL when you changed SQL names?
And also one thing ... did you executed
"sp_addserver 'servername', local"?
"Viktor" <serguienkov@.hotmail.com> wrote in message
news:ef9Sza%23yHHA.1776@.TK2MSFTNGP03.phx.gbl...
>I change the name on my SQL2005 server, after that i run sp_dropserver and
>sp_addserver, but when i tru do delete som sql agent job or create new one
>iam getting error:"An error has occurred while establishing a connection to
>the server. When connecting to SQL Server 2005, this failure may be caused
>by the fact that under the default settings SQL Server does not allow
>remote connections. (provider: Named Pipes Provider, error: 40 - Could not
>open a connection to SQL Server) (.Net SqlClient Data Provider)"
> any help please
>