DSO in SQL2005: attempt

Showing posts with label attempt. Show all posts

Thursday, March 22, 2012

Drop User

When I attempt to drop a user I receive an error that the user objects
objects in the SQL 2000 DB, and the login could not be dropped. Does anyone
have a workaround to this? How can you get around this. Perhaps changing
ownership globally, and then deleteing the login? Any Ideas?Hi,
You can not drop the user if the user owns any objects.
How to change the object owner:
sp_changeobjectowner 'obj_name','new_owner'
You can also change the owner by updating the sysobjects tables
update sysobjects
set uid=<new uid>
where uid='uid for the user you need to drop'
Thanks
Hari
MCDBA
"Casey" <casey.canales@.bestsoftware.com> wrote in message
news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> When I attempt to drop a user I receive an error that the user objects
> objects in the SQL 2000 DB, and the login could not be dropped. Does
anyone
> have a workaround to this? How can you get around this. Perhaps changing
> ownership globally, and then deleteing the login? Any Ideas?
>|||> You can also change the owner by updating the sysobjects tables
> update sysobjects
> set uid=<new uid>
> where uid='uid for the user you need to drop'
Hari, although this may work, Casey should probably use the supported method
(sp_changedbowner).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You can not drop the user if the user owns any objects.
> How to change the object owner:
> sp_changeobjectowner 'obj_name','new_owner'
> You can also change the owner by updating the sysobjects tables
> update sysobjects
> set uid=<new uid>
> where uid='uid for the user you need to drop'
> Thanks
> Hari
> MCDBA
>
> "Casey" <casey.canales@.bestsoftware.com> wrote in message
> news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > When I attempt to drop a user I receive an error that the user objects
> > objects in the SQL 2000 DB, and the login could not be dropped. Does
> anyone
> > have a workaround to this? How can you get around this. Perhaps
changing
> > ownership globally, and then deleteing the login? Any Ideas?
> >
> >
>|||Hi,
I agree with Dan. I just mentioned various possibilities to change the
object owner.
Casey,
Please use sp_changeobjectowner system stored procedure to change the object
owners. This is always safe.
Updating system tables is always risky.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OiJAsV1IEHA.3840@.TK2MSFTNGP11.phx.gbl...
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> Hari, although this may work, Casey should probably use the supported
method
> (sp_changedbowner).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > You can not drop the user if the user owns any objects.
> >
> > How to change the object owner:
> >
> > sp_changeobjectowner 'obj_name','new_owner'
> >
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Casey" <casey.canales@.bestsoftware.com> wrote in message
> > news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > > When I attempt to drop a user I receive an error that the user objects
> > > objects in the SQL 2000 DB, and the login could not be dropped. Does
> > anyone
> > > have a workaround to this? How can you get around this. Perhaps
> changing
> > > ownership globally, and then deleteing the login? Any Ideas?
> > >
> > >
> >
> >
>|||Oops, I meant sp_changeobjectowner.
--
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OiJAsV1IEHA.3840@.TK2MSFTNGP11.phx.gbl...
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> Hari, although this may work, Casey should probably use the supported
method
> (sp_changedbowner).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > You can not drop the user if the user owns any objects.
> >
> > How to change the object owner:
> >
> > sp_changeobjectowner 'obj_name','new_owner'
> >
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Casey" <casey.canales@.bestsoftware.com> wrote in message
> > news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > > When I attempt to drop a user I receive an error that the user objects
> > > objects in the SQL 2000 DB, and the login could not be dropped. Does
> > anyone
> > > have a workaround to this? How can you get around this. Perhaps
> changing
> > > ownership globally, and then deleteing the login? Any Ideas?
> > >
> > >
> >
> >
>

Drop Trigger from Replicated Table?

I created a trigger on a replicated table in a publishing database on SQL Server 2000. When I attempt to ALTER TABLE ... DISABLE TRIGGER ..., I get a message that I cannot alter the table since it is part of a publication. Does anyone know if I would be able to issue a DROP TRIGGER or ALTER TRIGGER on a replicated table?

Thanks,

Gerald

you can not Disable trigger by alter statment in a replicated table. Drop the trigger is the only way...

Madhu

sql

Friday, March 9, 2012

drop col from published table

I wish to add a col to a table I have published for merge replication.
When I attempt to do this I receive errors in regard to the rowguid col that
was added when creating my publication.
Do I need to drop the publication before adding col ?
Or is there another way out of this ?
You shouldn't have to if you use sp_repldropcolumn. What is the exact error
message you are getting?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eTmObDGBHHA.1012@.TK2MSFTNGP04.phx.gbl...
>I wish to add a col to a table I have published for merge replication.
> When I attempt to do this I receive errors in regard to the rowguid col
> that was added when creating my publication.
> Do I need to drop the publication before adding col ?
> Or is there another way out of this ?
>
|||Hi Hilary, and thanks for the response.
When I attempt to add (not drop) a col to a table I have published for
mobile merge replication I receive the following errors :
"Error validation the default for column 'rowguid'."
"It is invalid to drop the default constraint on the rowguid column that is
used by merge replication. The DDL operation failed inside merge DDL
replication manipulation. The transaction ended in the trigger. The batch
has been aborted."
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ObR97mGBHHA.3380@.TK2MSFTNGP04.phx.gbl...
> You shouldn't have to if you use sp_repldropcolumn. What is the exact
> error message you are getting?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:eTmObDGBHHA.1012@.TK2MSFTNGP04.phx.gbl...
>

Thursday, March 22, 2012

Drop User

Drop User

Drop User

Drop Trigger from Replicated Table?

Friday, March 9, 2012

drop col from published table

DSO in SQL2005

Blog Archive

About Me