Change the server name does not default usenames

Hi :

I am in the process of testing server name on which sqlserver is running.In this process i could sucessfully rename the server and bring the database online.I have renamed the server name in the system tables by using sp_dropserver and addserver .But still in the security logins i can see the agent logins not reflecting the change .

Oldservername \SQLServer2005SQLAgentUser$oldservername$MSSQLSERVER.How can i modify these names.

thanks

Ganesh

You need to restart the sql server after performing sp_dropserver and sp_addserver to reflect the new name !
|||

The server and the service were restarted serveral times.Any help appreciated .

thanks,

Ganesh

Change the server name does not change default usenames

Hi :

I am in the process of testing server name on which sqlserver is running.In this process i could sucessfully rename the server and bring the database online.I have renamed the server name in the system tables by using sp_dropserver and addserver .But still in the security logins i can see the agent logins not reflecting the change .

Oldservername \SQLServer2005SQLAgentUser$oldservername$MSSQLSERVER.How can i modify these names.

thanks

Ganesh

You need to restart the sql server after performing sp_dropserver and sp_addserver to reflect the new name !
|||

The server and the service were restarted serveral times.Any help appreciated .

thanks,

Ganesh

Change Table Ownership.

I have a DB that I moved to another server recently and in the process we
created a new user/pass combination.
All the tables for the main app are listed as "system" and the main app runs
great.
There is a secondary app using 12 tables in the DB and although we changed
the user/pass in the app and it connects fine the app won't run saying
"Invalid object name 'tablename'."
The table is there. The only thing I notice that seems odd is the tables are
listed as "user" and not "system" and the user assigned is the old user.
How can I either,
A. change what user owns the table? or,
B. Change it from a "user" table to a "system" table?
Thanks for any replies.
Check out sp_changeobjectowner in BOL.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>
|||System tables are there for SQL to run properly. User tables are what you
put information into. You don't need to or want to change a user table to a
system table.
You probably do need to change the owner of the tables. Look up
sp_changeobjectowner on BOL (Books On-Line) for exact instructions on how to
do this.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>
|||> "Invalid object name 'tablename'."
Have you tried explicitly naming the object? e.g. dbo.tablename?
Also see http://www.aspfaq.com/2284
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Thanks for the replies guys.
What I did was ran into the lazy GUI method.

>EM>Design Table>relationships icon from the top>Table tab> Change
ownership.
All set, app is behaving again.
Thanks.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>

Change Table Ownership.

I have a DB that I moved to another server recently and in the process we
created a new user/pass combination.
All the tables for the main app are listed as "system" and the main app runs
great.
There is a secondary app using 12 tables in the DB and although we changed
the user/pass in the app and it connects fine the app won't run saying
"Invalid object name 'tablename'."
The table is there. The only thing I notice that seems odd is the tables are
listed as "user" and not "system" and the user assigned is the old user.
How can I either,
A. change what user owns the table? or,
B. Change it from a "user" table to a "system" table?
Thanks for any replies.Check out sp_changeobjectowner in BOL.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>|||System tables are there for SQL to run properly. User tables are what you
put information into. You don't need to or want to change a user table to a
system table.
You probably do need to change the owner of the tables. Look up
sp_changeobjectowner on BOL (Books On-Line) for exact instructions on how to
do this.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>|||> "Invalid object name 'tablename'."
Have you tried explicitly naming the object? e.g. dbo.tablename?
Also see http://www.aspfaq.com/2284
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks for the replies guys.
What I did was ran into the lazy GUI method.
>EM>Design Table>relationships icon from the top>Table tab> Change
ownership.
All set, app is behaving again.
Thanks.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>

Change Table Ownership.

I have a DB that I moved to another server recently and in the process we
created a new user/pass combination.
All the tables for the main app are listed as "system" and the main app runs
great.
There is a secondary app using 12 tables in the DB and although we changed
the user/pass in the app and it connects fine the app won't run saying
"Invalid object name 'tablename'."
The table is there. The only thing I notice that seems odd is the tables are
listed as "user" and not "system" and the user assigned is the old user.
How can I either,
A. change what user owns the table? or,
B. Change it from a "user" table to a "system" table?
Thanks for any replies.Check out sp_changeobjectowner in BOL.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>|||System tables are there for SQL to run properly. User tables are what you
put information into. You don't need to or want to change a user table to a
system table.
You probably do need to change the owner of the tables. Look up
sp_changeobjectowner on BOL (Books On-Line) for exact instructions on how to
do this.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>|||> "Invalid object name 'tablename'."
Have you tried explicitly naming the object? e.g. dbo.tablename?
Also see http://www.aspfaq.com/2284
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks for the replies guys.
What I did was ran into the lazy GUI method.

>EM>Design Table>relationships icon from the top>Table tab> Change
ownership.
All set, app is behaving again.
Thanks.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>

Change structure in Db

Hi.
How I can change a structure(add/drop columns or add st.
procedures) in databases which are involved in Merge
Replication process? I'm using SQL Server 2000.
Thank you.
use sp_repladdcolumn and sp_repldropcolumn

Change SA password. Any effect to replication?

Dear all,

I have some questions regard subject above. I want to know the impact of replication process after SA pasword been changed.

Example: server1 (hq), server2 (district) & server3 (district).

If server1, SA password been changed, is it server2 & server3 need to change too?

may i know where is it the replication SA password stored? is it replication script refer ID (SA) from Security -> Logins?

I hope someone can help me.. thanks.

changing the Sa password would not harm the replication process

ideally...

replication is using the account used by the sqlserver agent services.

to know what account is your agent services running.

click start>run>type "services.msc">look for sqlserver agent

Click on Log On> this is the account used by replication.

usually this is a domain account..

if your system is configure in this manner, then changing the "Sa" password

will not matter at all.

If it configured some other way around then you have to watch out for the account

being used in the replication

Change maintenance plan owner

Jim,
Sorry, I don't know a neat way to change the maintenance plan owner. (In
SQL Server 2000, you can go through the process to modify system tables and
change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
recommend it) but in SQL Server 2005 that avenue has been closed.)
You can, of course, change the job owner that runs the maintenance plan as
you already described. Is there a reason that the maintenance plan owner
needs to be changed? What side effects are you experiencing?
RLF
"Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>I would like to change the owner of several maintenance plans. I can't
>seem
> to find a way to that without recreating each plan while connected as the
> new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks
Changing the owner of the job only works until the maintenance plan is
modified, then the jobs reverts back to the owner of the maintenance plan.
You might review this article:
http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
"Russell Fields" wrote:

> Jim,
> Sorry, I don't know a neat way to change the maintenance plan owner. (In
> SQL Server 2000, you can go through the process to modify system tables and
> change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
> recommend it) but in SQL Server 2005 that avenue has been closed.)
> You can, of course, change the job owner that runs the maintenance plan as
> you already described. Is there a reason that the maintenance plan owner
> needs to be changed? What side effects are you experiencing?
> RLF
> "Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
> news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>
>
|||Interesting. Thanks, RLF
"jed" <jed@.discussions.microsoft.com> wrote in message
news:96DC37A2-DD29-4EE2-A89D-F24A8DC41981@.microsoft.com...[vbcol=seagreen]
> Changing the owner of the job only works until the maintenance plan is
> modified, then the jobs reverts back to the owner of the maintenance plan.
> You might review this article:
> http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
> "Russell Fields" wrote: