rename login (Possible OT)

I need to rename a login that owns some tables. I cannot do this via the
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
Thanks
Don't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>
|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables[vbcol=seagreen]
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
the
>

rename login (Possible OT)

I need to rename a login that owns some tables. I cannot do this via the
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
ThanksDon't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> > I need to rename a login that owns some tables. I cannot do this via
the
> > enterprise manager? Can I do it via SQL scripting?
> >
> > Please advise (also if there is a better NG to post this to)
> >
> > Thanks
> >
> >
>

rename login (Possible OT)

I need to rename a login that owns some tables. I cannot do this via the
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
ThanksDon't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
>

Rename Columns via Query

I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to use
a view to display the ID column,and only one other, which will vary depending
on my requirements. I have another table that contains only one row (and is
updated regularly) with the column name (ColID) I'm trying to retieve.
Can anyone tell me how I can pass the value of ColID into the column name in
my view?
I hope this is clear. Thanks in anticipation...
Take a look at this example:
USE Northwind
CREATE TABLE QueryColumn
(qc sysname)
GO
INSERT INTO QueryColumn VALUES(N'CompanyName')
DECLARE @.q varchar(8000)
SET @.q='SELECT ShipperId, '+(SELECT qc FROM QueryColumn)+' FROM Shippers'
EXEC (@.q)
UPDATE QueryColumn SET qc=N'Phone'
SET @.q='SELECT ShipperId, '+(SELECT qc FROM QueryColumn)+' FROM Shippers'
EXEC (@.q)
GO
DROP TABLE QueryColumn
GO
Do please also read the article at
http://www.sommarskog.se/dynamic_sql.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"MillionChads" <MillionChads@.discussions.microsoft.com> wrote in message
news:ABCE5205-361F-4BB8-8DAC-DCBEF6DACB4C@.microsoft.com...
> I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to
use
> a view to display the ID column,and only one other, which will vary
depending
> on my requirements. I have another table that contains only one row (and
is
> updated regularly) with the column name (ColID) I'm trying to retieve.
> Can anyone tell me how I can pass the value of ColID into the column name
in
> my view?
> I hope this is clear. Thanks in anticipation...

Removing Merge Replication

Hi,
I have two databases which have previously been replicating via merge replication.
I have removed replacation from one of the databases (the smaller) one, and noticed during this process the system slowed to an absolute crawl.
My problem is, i need to do the second (and much larger database) but cant afford for the system to a) take so long b) be virtually unusable during this period.
Is there any way around this? Can it be done manually? Can you change the priority or limit the maximum i/o usage during the removal period?
Also, is it normal for replicated databases to be double the size of non-replicated databases?
Thanks,
Andrew
Andrew,
can you try disabling the merge agent then dropping the subscriber out of
hours?
As for the size of the database, have a look at the main merge replication
tables:
sp_spaceused msmerge_contents
sp_spaceused msmerge_tombstone
sp_spaceused MSmerge_genhistory
This is probably where you'll find the extra space is used up. Cleaning up
metadata can aid in this issue.
HTH,
Paul Ibison
|||Unfortunately we dont have an after hours period, we are a 24/7 business. Any suggestions?
Thanks,
Andrew
"Paul Ibison" wrote:

> Andrew,
> can you try disabling the merge agent then dropping the subscriber out of
> hours?
> As for the size of the database, have a look at the main merge replication
> tables:
> sp_spaceused msmerge_contents
> sp_spaceused msmerge_tombstone
> sp_spaceused MSmerge_genhistory
> This is probably where you'll find the extra space is used up. Cleaning up
> metadata can aid in this issue.
> HTH,
> Paul Ibison
>
>
|||dropping replication should not be causing this problem. I would run
profiler to determine exactly where it is failing.
You can drop a merge subscription by using
sp_dropmergepullsubscription
sp_dropmergesubscription and set the ignore_distribution parameter to true.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:7ED41D2D-F59F-4F37-BDD2-DF5DDDACEC5A@.microsoft.com...
> Unfortunately we dont have an after hours period, we are a 24/7 business.
Any suggestions?[vbcol=seagreen]
> Thanks,
> Andrew
> "Paul Ibison" wrote:
of[vbcol=seagreen]
replication[vbcol=seagreen]
up[vbcol=seagreen]
|||dropping replication should not be causing this problem. I would run
profiler to determine exactly where it is failing.
You can drop a merge subscription by using
sp_dropmergepullsubscription
sp_dropmergesubscription and set the ignore_distribution parameter to true.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:7ED41D2D-F59F-4F37-BDD2-DF5DDDACEC5A@.microsoft.com...
> Unfortunately we dont have an after hours period, we are a 24/7 business.
Any suggestions?[vbcol=seagreen]
> Thanks,
> Andrew
> "Paul Ibison" wrote:
of[vbcol=seagreen]
replication[vbcol=seagreen]
up[vbcol=seagreen]
|||Hi Hilary,
Thanks for your reply.
Does the procedure you describe do the same as deleting the subscription via enterprise manager?
If I start the procedure described via Query Analyser, can it be stopped? (If the load on the server becomes too high?)
Andrew
"Hilary Cotter" wrote:

> dropping replication should not be causing this problem. I would run
> profiler to determine exactly where it is failing.
> You can drop a merge subscription by using
> sp_dropmergepullsubscription
> sp_dropmergesubscription and set the ignore_distribution parameter to true.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:7ED41D2D-F59F-4F37-BDD2-DF5DDDACEC5A@.microsoft.com...
> Any suggestions?
> of
> replication
> up
>
>
|||Hi Hilary,
Thanks for your reply.
Does the procedure you describe do the same as deleting the subscription via enterprise manager?
If I start the procedure described via Query Analyser, can it be stopped? (If the load on the server becomes too high?)
Andrew
"Hilary Cotter" wrote:

> dropping replication should not be causing this problem. I would run
> profiler to determine exactly where it is failing.
> You can drop a merge subscription by using
> sp_dropmergepullsubscription
> sp_dropmergesubscription and set the ignore_distribution parameter to true.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:7ED41D2D-F59F-4F37-BDD2-DF5DDDACEC5A@.microsoft.com...
> Any suggestions?
> of
> replication
> up
>
>