Changing Column Name on a table

Hi All,
I am trying to change column name on an existing table. I am using SQL
Server 7.

As the table is quite big, it is taking quite long time to do it.
By the way I could change the column name only through the Enterprize
Manager.

Is it possible to change the column name using SQL script?
Why the change of column name will depend on the size of the table?

Thanks for your answer.

-MoklesYou can change a column name using sp_rename.

EXEC sp_rename 'MyTable.OldColumnName', 'NewColumnName', 'COLUMN'

See the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mokles" <mokles@.rogers.com> wrote in message
news:wIGdnaGM46YtX_jcRVn-oA@.rogers.com...
> Hi All,
> I am trying to change column name on an existing table. I am using SQL
> Server 7.
> As the table is quite big, it is taking quite long time to do it.
> By the way I could change the column name only through the Enterprize
> Manager.
> Is it possible to change the column name using SQL script?
> Why the change of column name will depend on the size of the table?
> Thanks for your answer.
> -Mokles|||Hi Mokles

Have you tried to rename the field using sp_rename.

Enterprise manager does the job using same kind of scripts but its not
intelligent enough I have observed in various scenarios that sometimes
it accomplishes simple tasks using long approach.

I wonder if there are any indexes on the table ?

Regards
Shehzad

"mokles" <mokles@.rogers.com> wrote in message news:<wIGdnaGM46YtX_jcRVn-oA@.rogers.com>...
> Hi All,
> I am trying to change column name on an existing table. I am using SQL
> Server 7.
> As the table is quite big, it is taking quite long time to do it.
> By the way I could change the column name only through the Enterprize
> Manager.
> Is it possible to change the column name using SQL script?
> Why the change of column name will depend on the size of the table?
> Thanks for your answer.
> -Mokles|||I could do it using Sp_Rename.
Enterprize Manager was using complicated method to do the simple task
Anyway thanks for the help

-Mokles

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:6Qk9d.2605$Al3.1466@.newssvr30.news.prodigy.co m...
> You can change a column name using sp_rename.
> EXEC sp_rename 'MyTable.OldColumnName', 'NewColumnName', 'COLUMN'
> See the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "mokles" <mokles@.rogers.com> wrote in message
> news:wIGdnaGM46YtX_jcRVn-oA@.rogers.com...
> > Hi All,
> > I am trying to change column name on an existing table. I am using SQL
> > Server 7.
> > As the table is quite big, it is taking quite long time to do it.
> > By the way I could change the column name only through the Enterprize
> > Manager.
> > Is it possible to change the column name using SQL script?
> > Why the change of column name will depend on the size of the table?
> > Thanks for your answer.
> > -Mokles|||Today I tried to do it with sp_Rename and it worked well and fast.

Previously I was using profiler to see what the Enterprize Manager was
doing. And really it was using complicated method to change the column name.

Thanks all for the help

-Mokles

"Shehzad Shabbir" <shehzad@.internav.com> wrote in message
news:503e40d5.0410071952.643d25ee@.posting.google.c om...
> Hi Mokles
> Have you tried to rename the field using sp_rename.
> Enterprise manager does the job using same kind of scripts but its not
> intelligent enough I have observed in various scenarios that sometimes
> it accomplishes simple tasks using long approach.
> I wonder if there are any indexes on the table ?
> Regards
> Shehzad
>
> "mokles" <mokles@.rogers.com> wrote in message
news:<wIGdnaGM46YtX_jcRVn-oA@.rogers.com>...
> > Hi All,
> > I am trying to change column name on an existing table. I am using SQL
> > Server 7.
> > As the table is quite big, it is taking quite long time to do it.
> > By the way I could change the column name only through the Enterprize
> > Manager.
> > Is it possible to change the column name using SQL script?
> > Why the change of column name will depend on the size of the table?
> > Thanks for your answer.
> > -Mokles

changing collation_name of SqlServer Database

Hi

I want to change collation_name of my SqlServer Database by running a script. But I don't know how.
Please help me.

ThanksYou should be able to do this:

ALTER DATABASE myDatabase COLLATE SQL_Latin1_General_CP1_CI_AS

This will change the default collation for any new objects added to the database. To change the collation of existing objects you will need to run an ALTER TABLE command against each table.

Terrisql

Change Tracking in SQLserver

What is change Tracking in SQL serverhttp://msdn2.microsoft.com/en-us/ms142575.aspx

Change the SQL Server or SQL Server Agent Service account

Good afternoon,
I was to make the exchange of the account that go up the services of the SQL
Server and the SQL Agent.
Of the SQL Server it was modified without problems, to put when I was to
modify of the SQL Agent I received the following error:
Error:22043: xp_SetSQLSecurity() returned error 5, ' Access Denied '
and I do not obtain to change the account.
Anybody has some ideia of that to make?
Raphael Soares
where did you make the modifications? Enterprise Manager? If so, then the SQL Server service account
need some permissions.
Search Books Online for "level token", and you will find information about service accounts and
needed permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raphael Soares" <Raphael Soares@.discussions.microsoft.com> wrote in message
news:E2FFA01B-7E1C-4A30-BCD0-75BF24AAE6F4@.microsoft.com...
> Good afternoon,
> I was to make the exchange of the account that go up the services of the SQL
> Server and the SQL Agent.
> Of the SQL Server it was modified without problems, to put when I was to
> modify of the SQL Agent I received the following error:
> Error:22043: xp_SetSQLSecurity() returned error 5, ' Access Denied '
> and I do not obtain to change the account.
> Anybody has some ideia of that to make?
> Raphael Soares
|||Tibor
I'm having the same problem that Raphael has.
In my case the account I'm trying to use to start the SQL Server Agent
service, belongs to the local administrators group at the database server,
the account is just a domain account that belongs to Domain users.
I'm trying to change this in the Enterprise Manager
This server has enabled the Terminal services in Application server mode.
Which permission I'm lacking?
It is not supposed that if I do it from Enterprise Manager I'm avoiding to
give manually the permissions that the Books Online article talks about?
Thanks
Guillermo
"Tibor Karaszi" wrote:

> where did you make the modifications? Enterprise Manager? If so, then the SQL Server service account
> need some permissions.
> Search Books Online for "level token", and you will find information about service accounts and
> needed permissions.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Raphael Soares" <Raphael Soares@.discussions.microsoft.com> wrote in message
> news:E2FFA01B-7E1C-4A30-BCD0-75BF24AAE6F4@.microsoft.com...
>
>

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 Password of SQL Server Login

Can any one tell me how to change a password for a logged in user in sql
server? Here the condition is the logged in user is a user who has least
privileges and just will have only select permission on database tables. The
logged in user does not have any server roles and even he is not a member of
db_Owner role. He is not even member Security Administrator which is
required to change the password for a user.
Can any one give an example to change the password using T-Sql
statements for my scenario'
VenkatDo you mean change their own password or change another users password? Any
user can change their own password, if they need to change other users
passwords then they must be a member of the securityadmin or sysadmin fixed
server roles, there's no getting around that.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Venkat" <tammana@.inooga.com> wrote in message
news:%23gIjK%23paFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Can any one tell me how to change a password for a logged in user in sql
> server? Here the condition is the logged in user is a user who has least
> privileges and just will have only select permission on database tables.
> The logged in user does not have any server roles and even he is not a
> member of db_Owner role. He is not even member Security Administrator
> which is required to change the password for a user.
>
> Can any one give an example to change the password using T-Sql
> statements for my scenario'
>
> --
> Venkat
>
>

Change Password

Hi
How do I do when it's time to change the password on the
computer. When I do that I can no longer start up the SQL
server. I quess that I have to make som changes in the Sql
server, but where?
/Anett
Hi
Control Panel > Services
Or under service properties forthe SQL Server in Entrprise Manager
If this is a production SQL Server, you should create a special account on
the domain for SQL Server that does not require password changes.
Regards
Mike
"Anett" wrote:

> Hi
> How do I do when it's time to change the password on the
> computer. When I do that I can no longer start up the SQL
> server. I quess that I have to make som changes in the Sql
> server, but where?
> /Anett
>
|||Instead of using the Services tab, it is better to change passwords in SQL
Enterprise Manager... This gets around some issues if you are running
full-text search, so I always use SEM... Don't forget to change the password
for any other shared services like SQL Agent...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:3bfc01c4c574$9d0d76c0$a401280a@.phx.gbl...
> Hi
> How do I do when it's time to change the password on the
> computer. When I do that I can no longer start up the SQL
> server. I quess that I have to make som changes in the Sql
> server, but where?
> /Anett

Change Password

Hi
How do I do when it's time to change the password on the
computer. When I do that I can no longer start up the SQL
server. I quess that I have to make som changes in the Sql
server, but where'
/AnettHi
Control Panel > Services
Or under service properties forthe SQL Server in Entrprise Manager
If this is a production SQL Server, you should create a special account on
the domain for SQL Server that does not require password changes.
Regards
Mike
"Anett" wrote:

> Hi
> How do I do when it's time to change the password on the
> computer. When I do that I can no longer start up the SQL
> server. I quess that I have to make som changes in the Sql
> server, but where'
> /Anett
>|||Instead of using the Services tab, it is better to change passwords in SQL
Enterprise Manager... This gets around some issues if you are running
full-text search, so I always use SEM... Don't forget to change the password
for any other shared services like SQL Agent...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:3bfc01c4c574$9d0d76c0$a401280a@.phx.gbl...
> Hi
> How do I do when it's time to change the password on the
> computer. When I do that I can no longer start up the SQL
> server. I quess that I have to make som changes in the Sql
> server, but where'
> /Anett

Change Login-Info for Activity-Monitor and sp_who

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas

DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.

Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.

Thomas

You will see only Thomas and AppUser that are used by web server to connect to server and use objects of db.

I think in your db there is a table that contain users with encrypted passwords and if you want to see who access your application when the user app try to connect with or without succes , using sp that making the verification you can put somehere in a table the information that the user is connected. Querying that table you can see who is connected.

|||

Since Login to User is 1 to M , you will not get the result as you mentioned in either SP_Who or Activity monitor. You can make a customized sp from sp_who and you can use that. Just you need to join with sys.database_principals

(a) SP_Helptext sp_Who -- Will give u the source code of sp_who

(b) Join with sys.database_principals .

the following script is taken from sp_who and then joined with sys.database_principals . sys.database_principals is database specific. So you should run this script in the particular database to get the desired result.

select spid , ecid, status

,loginame=rtrim(loginame)

,hostname ,blk=convert(char(5),blocked)

,dbname = case

when dbid = 0 then null

when dbid <> 0 then db_name(dbid)

end

,cmd

,request_id ,sdp.Name as UserName

from master.dbo.sysprocesses inner join

sys.database_principals sdp on sdp.sid=master.dbo.sysprocesses .sid

Madhu

Change Login-Info for Activity-Monitor and sp_who

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas

DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.

Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.

Thomas

You will see only Thomas and AppUser that are used by web server to connect to server and use objects of db.

I think in your db there is a table that contain users with encrypted passwords and if you want to see who access your application when the user app try to connect with or without succes , using sp that making the verification you can put somehere in a table the information that the user is connected. Querying that table you can see who is connected.

|||

Since Login to User is 1 to M , you will not get the result as you mentioned in either SP_Who or Activity monitor. You can make a customized sp from sp_who and you can use that. Just you need to join with sys.database_principals

(a) SP_Helptext sp_Who -- Will give u the source code of sp_who

(b) Join with sys.database_principals .

the following script is taken from sp_who and then joined with sys.database_principals . sys.database_principals is database specific. So you should run this script in the particular database to get the desired result.

select spid , ecid, status

,loginame=rtrim(loginame)

,hostname ,blk=convert(char(5),blocked)

,dbname = case

when dbid = 0 then null

when dbid <> 0 then db_name(dbid)

end

,cmd

,request_id ,sdp.Name as UserName

from master.dbo.sysprocesses inner join

sys.database_principals sdp on sdp.sid=master.dbo.sysprocesses .sid

Madhu