How to encrypt and decrypt stored procedures?

I encrypt my procedures using with encryption clause, but I do not how to decrypt again.

Is there a command or utility for encrypt and decrypt in Sql 2000? How about Sql 2005?

Thanks

Haydee

Decryption is weak and can be cracked by searching on google for the specific algorithms, there was a thread sometime ago, which might be useful to you:

http://groups.google.de/group/comp.databases.ms-sqlserver/browse_frm/thread/34b309b76ba574b4

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens is right, the procedure encryption is actually referred to as obfuscation in Books Online. Also, there is no SQL Server command for decrypting it back.

Thanks
Laurentiu

|||

Thanks for your comments

and is there a tool in Sql Server 2005 in order to protect the code? What can I do? I need to install a project in the customer, and I would like to protect it.

Thanks again for your help.

Haydee

|||You could use third party components to accomplish this, there sure can be found some by searching in google for them.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

HI,

I am using MSDE 2000 and I will be deploying it with my software application. I have invested a good bit into my database schema and I don't want it to be viewed by others.

I can not see why some user can not take the .mdf (multiple mdf's actually) and sp_AttachDB or attach them to their instanced SQL server using EM. I of course do not want this.

Maybe someone can clear up the limitations and types of SQL security that can assure no one can simply attach the MDF to see the structure, let alone the data.

As far as I can see there is Network security as to authentication for a live/instanced SQL server and this has no ability to prevent an MDF from being re-attached and viewed/queried.

I also see EncryptByPassPhrase which I can use prior to executing a query (if I understand this process which is data remains in encrypted state until its about to be used, then decrypted in memory (I presume ? otherwise someone could grab a snapshot of the mdf while it's in decrypted state ? {or SQL server has a temp region when using encryption where it places the decrypted data I take it}) and then I have to encrypt it again after processing.

Neither of these look like they can obfuscate or lock the db schema information, such as table names, structures, fields, field types/attributes etc.

Sooooooo.....

How can I prevent a user from seeing the underlying table structures and does anyone know if column encryption will cost me 10 years off my life time wise on large data sets ?

Thanks

|||

See this recent thread for a discussion of this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1

Thanks
Laurentiu

|||

The only feature for protecting code in SQL Server is the WITH ENCRYPTION clause that we discussed so far. It is weak not necessarily because the encryption is weak (it uses RC4), but because the encryption key can be easily found. An attacker will focus on finding the encryption key rather than breaking the encryption algorithm in such a solution. This is a general problem and for any solution you consider, you should look at how easy it is for someone to find the encryption key.

This is basically a DRM solution, and I have talked about the difficulty of creating an unbreakable DRM solution on other threads, more recently in:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1

Thanks
Laurentiu

How to drop a column that has a DEFAULT clause

Hi,
I have a table with a column:
rv smallint default 1 not null
I want to drop it:
alter table myTable
drop column rv
But I get an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rv failed because one or more objects access this
column.
The 'object' mentioned above is the default constraint.
How do I just drop the column (without having to know waht constraints it
has)?
Thanks
Michael
You first have to drop the default constraint
alter table myTable
drop constraint DF__ccAstPublica__rv__27C3E46E
Then drop the cilumn
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> Hi,
> I have a table with a column:
> rv smallint default 1 not null
> I want to drop it:
> alter table myTable
> drop column rv
> But I get an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> column.
> The 'object' mentioned above is the default constraint.
> How do I just drop the column (without having to know waht constraints it
> has)?
> Thanks
> Michael
>
|||Armand,
Thanks. Yes, I know I have to drop the constraint but...
This is in a script and I don't know the auto generated name of the
constraint, so I can't readily drop it.
Is there a way to find out the constraint name for the DEFAULT for that
column?
That way, I could fid the name then frop the constraint programmatically.
BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
Thanks
Michael
"Armando Prato" wrote:

> You first have to drop the default constraint
> alter table myTable
> drop constraint DF__ccAstPublica__rv__27C3E46E
> Then drop the cilumn
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>
>
|||Sure
What you can do is query the sysconstraints table in your db
and join it to syscolumns
select s2.name, object_name(s1.constid)
from sysconstraints s1
inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
and s1.colid = s2.colid and s1.status = 133141)
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...[vbcol=seagreen]
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
'rv'.[vbcol=seagreen]
this[vbcol=seagreen]
it[vbcol=seagreen]
|||you can run sp_help tablename or sp_helpconstraint tablename to view the
default name.
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...[vbcol=seagreen]
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
|||Armando,
Thanks. What does status 133141 mean?
"Armando Prato" wrote:

> Sure
> What you can do is query the sysconstraints table in your db
> and join it to syscolumns
>
> select s2.name, object_name(s1.constid)
> from sysconstraints s1
> inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
> and s1.colid = s2.colid and s1.status = 133141)
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> 'rv'.
> this
> it
>
>
|||Richard,
I assume this would be helpful interactively, but not if I want to do this
in a script?
"Richard Ding" wrote:

> you can run sp_help tablename or sp_helpconstraint tablename to view the
> default name.
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
>
>
|||It means that the particular row represents a DEFAULT constraint. It's a
bitmap, actually.
I have notes that say the status = 5 for defaults but my SQL Server
represents it
as 133141 bitmap
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:2118DFBB-8625-452C-BAB3-D6CA804E905F@.microsoft.com...[vbcol=seagreen]
> Armando,
> Thanks. What does status 133141 mean?
> "Armando Prato" wrote:
s2.id[vbcol=seagreen]
that[vbcol=seagreen]
programmatically.[vbcol=seagreen]
access[vbcol=seagreen]
constraints[vbcol=seagreen]
|||> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
This is why you should name your constraint in the first place... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...[vbcol=seagreen]
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
|||Tibor
Thanks for the helpful advice .
Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
not DEFAULTs. I think this particular aspect of SQL Server's design is not
particularly helpful. To my eye, it's much clearer to define:
myColumn smallint default 1
than
myColumn smallint,
constraint MyTab_DF_MyColumn default 1 for MyColumn
Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
allow me to drop a column that has an impicit not null, but not one with an
implicit DEFAULT.
Ho hum...
Bet this isn't 'fixed' in 2005...
Regards
Michael
"Tibor Karaszi" wrote:

> This is why you should name your constraint in the first place... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
>
>

How to drop a column that has a DEFAULT clause

Hi,
I have a table with a column:
rv smallint default 1 not null
I want to drop it:
alter table myTable
drop column rv
But I get an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rv failed because one or more objects access this
column.
The 'object' mentioned above is the default constraint.
How do I just drop the column (without having to know waht constraints it
has)?
Thanks
MichaelYou first have to drop the default constraint
alter table myTable
drop constraint DF__ccAstPublica__rv__27C3E46E
Then drop the cilumn
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> Hi,
> I have a table with a column:
> rv smallint default 1 not null
> I want to drop it:
> alter table myTable
> drop column rv
> But I get an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> column.
> The 'object' mentioned above is the default constraint.
> How do I just drop the column (without having to know waht constraints it
> has)?
> Thanks
> Michael
>|||Armand,
Thanks. Yes, I know I have to drop the constraint but...
This is in a script and I don't know the auto generated name of the
constraint, so I can't readily drop it.
Is there a way to find out the constraint name for the DEFAULT for that
column?
That way, I could fid the name then frop the constraint programmatically.
BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
Thanks
Michael
"Armando Prato" wrote:
> You first have to drop the default constraint
> alter table myTable
> drop constraint DF__ccAstPublica__rv__27C3E46E
> Then drop the cilumn
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > Hi,
> > I have a table with a column:
> > rv smallint default 1 not null
> >
> > I want to drop it:
> > alter table myTable
> > drop column rv
> >
> > But I get an error:
> > Server: Msg 5074, Level 16, State 1, Line 1
> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> > Server: Msg 4922, Level 16, State 1, Line 1
> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> > column.
> >
> > The 'object' mentioned above is the default constraint.
> > How do I just drop the column (without having to know waht constraints it
> > has)?
> > Thanks
> > Michael
> >
>
>|||Sure
What you can do is query the sysconstraints table in your db
and join it to syscolumns
select s2.name, object_name(s1.constid)
from sysconstraints s1
inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
and s1.colid = s2.colid and s1.status = 133141)
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
> > You first have to drop the default constraint
> >
> > alter table myTable
> > drop constraint DF__ccAstPublica__rv__27C3E46E
> >
> > Then drop the cilumn
> >
> > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > Hi,
> > > I have a table with a column:
> > > rv smallint default 1 not null
> > >
> > > I want to drop it:
> > > alter table myTable
> > > drop column rv
> > >
> > > But I get an error:
> > > Server: Msg 5074, Level 16, State 1, Line 1
> > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
'rv'.
> > > Server: Msg 4922, Level 16, State 1, Line 1
> > > ALTER TABLE DROP COLUMN rv failed because one or more objects access
this
> > > column.
> > >
> > > The 'object' mentioned above is the default constraint.
> > > How do I just drop the column (without having to know waht constraints
it
> > > has)?
> > > Thanks
> > > Michael
> > >
> >
> >
> >|||you can run sp_help tablename or sp_helpconstraint tablename to view the
default name.
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
>> You first have to drop the default constraint
>> alter table myTable
>> drop constraint DF__ccAstPublica__rv__27C3E46E
>> Then drop the cilumn
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> > Hi,
>> > I have a table with a column:
>> > rv smallint default 1 not null
>> >
>> > I want to drop it:
>> > alter table myTable
>> > drop column rv
>> >
>> > But I get an error:
>> > Server: Msg 5074, Level 16, State 1, Line 1
>> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
>> > 'rv'.
>> > Server: Msg 4922, Level 16, State 1, Line 1
>> > ALTER TABLE DROP COLUMN rv failed because one or more objects access
>> > this
>> > column.
>> >
>> > The 'object' mentioned above is the default constraint.
>> > How do I just drop the column (without having to know waht constraints
>> > it
>> > has)?
>> > Thanks
>> > Michael
>> >
>>|||Armando,
Thanks. What does status 133141 mean?
"Armando Prato" wrote:
> Sure
> What you can do is query the sysconstraints table in your db
> and join it to syscolumns
>
> select s2.name, object_name(s1.constid)
> from sysconstraints s1
> inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
> and s1.colid = s2.colid and s1.status = 133141)
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> > > You first have to drop the default constraint
> > >
> > > alter table myTable
> > > drop constraint DF__ccAstPublica__rv__27C3E46E
> > >
> > > Then drop the cilumn
> > >
> > > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > > Hi,
> > > > I have a table with a column:
> > > > rv smallint default 1 not null
> > > >
> > > > I want to drop it:
> > > > alter table myTable
> > > > drop column rv
> > > >
> > > > But I get an error:
> > > > Server: Msg 5074, Level 16, State 1, Line 1
> > > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> 'rv'.
> > > > Server: Msg 4922, Level 16, State 1, Line 1
> > > > ALTER TABLE DROP COLUMN rv failed because one or more objects access
> this
> > > > column.
> > > >
> > > > The 'object' mentioned above is the default constraint.
> > > > How do I just drop the column (without having to know waht constraints
> it
> > > > has)?
> > > > Thanks
> > > > Michael
> > > >
> > >
> > >
> > >
>
>|||Richard,
I assume this would be helpful interactively, but not if I want to do this
in a script?
"Richard Ding" wrote:
> you can run sp_help tablename or sp_helpconstraint tablename to view the
> default name.
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> >> You first have to drop the default constraint
> >>
> >> alter table myTable
> >> drop constraint DF__ccAstPublica__rv__27C3E46E
> >>
> >> Then drop the cilumn
> >>
> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> >> > Hi,
> >> > I have a table with a column:
> >> > rv smallint default 1 not null
> >> >
> >> > I want to drop it:
> >> > alter table myTable
> >> > drop column rv
> >> >
> >> > But I get an error:
> >> > Server: Msg 5074, Level 16, State 1, Line 1
> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> >> > 'rv'.
> >> > Server: Msg 4922, Level 16, State 1, Line 1
> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access
> >> > this
> >> > column.
> >> >
> >> > The 'object' mentioned above is the default constraint.
> >> > How do I just drop the column (without having to know waht constraints
> >> > it
> >> > has)?
> >> > Thanks
> >> > Michael
> >> >
> >>
> >>
> >>
>
>|||It means that the particular row represents a DEFAULT constraint. It's a
bitmap, actually.
I have notes that say the status = 5 for defaults but my SQL Server
represents it
as 133141 bitmap
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:2118DFBB-8625-452C-BAB3-D6CA804E905F@.microsoft.com...
> Armando,
> Thanks. What does status 133141 mean?
> "Armando Prato" wrote:
> > Sure
> >
> > What you can do is query the sysconstraints table in your db
> > and join it to syscolumns
> >
> >
> > select s2.name, object_name(s1.constid)
> > from sysconstraints s1
> > inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id =s2.id
> > and s1.colid = s2.colid and s1.status = 133141)
> >
> >
> > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > > Armand,
> > > Thanks. Yes, I know I have to drop the constraint but...
> > > This is in a script and I don't know the auto generated name of the
> > > constraint, so I can't readily drop it.
> > > Is there a way to find out the constraint name for the DEFAULT for
that
> > > column?
> > > That way, I could fid the name then frop the constraint
programmatically.
> > > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > > Thanks
> > > Michael
> > >
> > > "Armando Prato" wrote:
> > >
> > > > You first have to drop the default constraint
> > > >
> > > > alter table myTable
> > > > drop constraint DF__ccAstPublica__rv__27C3E46E
> > > >
> > > > Then drop the cilumn
> > > >
> > > > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > > > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > > > Hi,
> > > > > I have a table with a column:
> > > > > rv smallint default 1 not null
> > > > >
> > > > > I want to drop it:
> > > > > alter table myTable
> > > > > drop column rv
> > > > >
> > > > > But I get an error:
> > > > > Server: Msg 5074, Level 16, State 1, Line 1
> > > > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> > 'rv'.
> > > > > Server: Msg 4922, Level 16, State 1, Line 1
> > > > > ALTER TABLE DROP COLUMN rv failed because one or more objects
access
> > this
> > > > > column.
> > > > >
> > > > > The 'object' mentioned above is the default constraint.
> > > > > How do I just drop the column (without having to know waht
constraints
> > it
> > > > > has)?
> > > > > Thanks
> > > > > Michael
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
This is why you should name your constraint in the first place... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
>> You first have to drop the default constraint
>> alter table myTable
>> drop constraint DF__ccAstPublica__rv__27C3E46E
>> Then drop the cilumn
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> > Hi,
>> > I have a table with a column:
>> > rv smallint default 1 not null
>> >
>> > I want to drop it:
>> > alter table myTable
>> > drop column rv
>> >
>> > But I get an error:
>> > Server: Msg 5074, Level 16, State 1, Line 1
>> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
>> > Server: Msg 4922, Level 16, State 1, Line 1
>> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
>> > column.
>> >
>> > The 'object' mentioned above is the default constraint.
>> > How do I just drop the column (without having to know waht constraints it
>> > has)?
>> > Thanks
>> > Michael
>> >
>>|||Tibor
Thanks for the helpful advice :).
Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
not DEFAULTs. I think this particular aspect of SQL Server's design is not
particularly helpful. To my eye, it's much clearer to define:
myColumn smallint default 1
than
myColumn smallint,
constraint MyTab_DF_MyColumn default 1 for MyColumn
Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
allow me to drop a column that has an impicit not null, but not one with an
implicit DEFAULT.
Ho hum...
Bet this isn't 'fixed' in 2005...
Regards
Michael
"Tibor Karaszi" wrote:
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> This is why you should name your constraint in the first place... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> >> You first have to drop the default constraint
> >>
> >> alter table myTable
> >> drop constraint DF__ccAstPublica__rv__27C3E46E
> >>
> >> Then drop the cilumn
> >>
> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> >> > Hi,
> >> > I have a table with a column:
> >> > rv smallint default 1 not null
> >> >
> >> > I want to drop it:
> >> > alter table myTable
> >> > drop column rv
> >> >
> >> > But I get an error:
> >> > Server: Msg 5074, Level 16, State 1, Line 1
> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> >> > Server: Msg 4922, Level 16, State 1, Line 1
> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> >> > column.
> >> >
> >> > The 'object' mentioned above is the default constraint.
> >> > How do I just drop the column (without having to know waht constraints it
> >> > has)?
> >> > Thanks
> >> > Michael
> >> >
> >>
> >>
> >>
>
>|||Yes, DEFAULTs are handles like constraints in SQL Server. Where in ANSI SQL, they are just column
attributes. Just one of those things to get used to... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:0B2F4A00-65E5-4DF1-B2C6-86CA9C21C4D8@.microsoft.com...
> Tibor
> Thanks for the helpful advice :).
> Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
> not DEFAULTs. I think this particular aspect of SQL Server's design is not
> particularly helpful. To my eye, it's much clearer to define:
> myColumn smallint default 1
> than
> myColumn smallint,
> constraint MyTab_DF_MyColumn default 1 for MyColumn
> Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
> allow me to drop a column that has an impicit not null, but not one with an
> implicit DEFAULT.
> Ho hum...
> Bet this isn't 'fixed' in 2005...
> Regards
> Michael
> "Tibor Karaszi" wrote:
>> > This is in a script and I don't know the auto generated name of the
>> > constraint, so I can't readily drop it.
>> This is why you should name your constraint in the first place... :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
>> > Armand,
>> > Thanks. Yes, I know I have to drop the constraint but...
>> > This is in a script and I don't know the auto generated name of the
>> > constraint, so I can't readily drop it.
>> > Is there a way to find out the constraint name for the DEFAULT for that
>> > column?
>> > That way, I could fid the name then frop the constraint programmatically.
>> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
>> > Thanks
>> > Michael
>> >
>> > "Armando Prato" wrote:
>> >
>> >> You first have to drop the default constraint
>> >>
>> >> alter table myTable
>> >> drop constraint DF__ccAstPublica__rv__27C3E46E
>> >>
>> >> Then drop the cilumn
>> >>
>> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> >> > Hi,
>> >> > I have a table with a column:
>> >> > rv smallint default 1 not null
>> >> >
>> >> > I want to drop it:
>> >> > alter table myTable
>> >> > drop column rv
>> >> >
>> >> > But I get an error:
>> >> > Server: Msg 5074, Level 16, State 1, Line 1
>> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
>> >> > Server: Msg 4922, Level 16, State 1, Line 1
>> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
>> >> > column.
>> >> >
>> >> > The 'object' mentioned above is the default constraint.
>> >> > How do I just drop the column (without having to know waht constraints it
>> >> > has)?
>> >> > Thanks
>> >> > Michael
>> >> >
>> >>
>> >>
>> >>
>>

How to do this?

I know how to use IN with WHERE clause but it does the OR on all values in the subquery. The question is how to make it match against all values returned?

ex:

SELECT UserID FROM UserCars WHERE CarID IN (10,20,30)

would return user ids of all users that have a either car 10, 20 OR 30

How to write simple query to get users that have cars 10, 20 AND 30

thanks,where CarID between 10 and 30

otherwise

where CarID = 10 and CarID = 20 and CarID = 30 and CarID = 10000001|||hmm, so there is really no other statement like IN that would do AND instead of OR on subquery?

i tried using ALL but that didnt work :/|||select u.UserID
from Users u
where exists(select 'x' from UserCars uc1 where u.UserID=uc1.UserID and uc1.CarID=10)
and exists(select 'x' from UserCars uc2 where u.UserID=uc1.UserID and uc1.CarID=20)
and exists(select 'x' from UserCars uc2 where u.UserID=uc1.UserID and uc1.CarID=30)

OR

select UserID
from UserCars
where CarID in (10,20,30)
group by UserID
having count('x')=3

How to do a case-sensitive WHERE clause comparision?

Using SQL Server 2000
Database is setup to be case-insensitive
I want to do a query comparing password in a case-sensitive way. Is there an
easy way to do that?
SELECT COUNT(*)
FROM mytable
WHERE Username = 'someuser'
AND Password = 'somepassword'
Is there maybe a special character or a function that will match Password in
a case-sensitive way?
Thanks in advance.You can use either CONVERT or COLLATE:
AND CONVERT(VARBINARY, Password) = CONVERT(VARBINARY, 'somepassword')
or
AND Password COLLATE SQL_Latin1_General_CP1_CS_AS = 'somepassword'
"M" wrote:

> Using SQL Server 2000
> Database is setup to be case-insensitive
> I want to do a query comparing password in a case-sensitive way. Is there
an
> easy way to do that?
> SELECT COUNT(*)
> FROM mytable
> WHERE Username = 'someuser'
> AND Password = 'somepassword'
> Is there maybe a special character or a function that will match Password
in
> a case-sensitive way?
> Thanks in advance.
>
>|||try this
SELECT COUNT(*)
FROM mytable
WHERE binary_checksum(Username) = binary_checksum('someuser')
AND binary_checksum(Password) = binary_checksum('somepassword')
Message posted via http://www.webservertalk.com|||I don't recommend either of the suggestions already posted. Better is this:
select count(*) from mytable
where Username collate Latin1_General_CS_AS = 'someuser'
and Password collate Latin1_General_CS_AS = 'somepassword'
Use in the COLLATE clause the collation you have in place, but
with CI changed to CS.
Steve Kass
Drew University
M wrote:

>Using SQL Server 2000
>Database is setup to be case-insensitive
>I want to do a query comparing password in a case-sensitive way. Is there a
n
>easy way to do that?
>SELECT COUNT(*)
>FROM mytable
>WHERE Username = 'someuser'
>AND Password = 'somepassword'
>Is there maybe a special character or a function that will match Password i
n
>a case-sensitive way?
>Thanks in advance.
>
>|||Will this be better performance wise, or just more correct? I agree with
you that I would have suggested this, but not sure either of them were not
perfectly valid. Can you expand?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23ySDjivOFHA.4000@.TK2MSFTNGP15.phx.gbl...
>I don't recommend either of the suggestions already posted. Better is
>this:
> select count(*) from mytable
> where Username collate Latin1_General_CS_AS = 'someuser'
> and Password collate Latin1_General_CS_AS = 'somepassword'
> Use in the COLLATE clause the collation you have in place, but
> with CI changed to CS.
> Steve Kass
> Drew University
> M wrote:
>|||On Wed, 6 Apr 2005 22:43:59 -0400, Louis Davidson wrote:

>Will this be better performance wise, or just more correct? I agree with
>you that I would have suggested this, but not sure either of them were not
>perfectly valid. Can you expand?
Hi Louis,
I *think* that the CONVERT(VARBINARY ...) suggested by Jack is just as
correct (but slower). And I *know* that the binary_checksum(...)
suggested by baie dronk is less correct.
SELECT BINARY_CHECKSUM('a'), BINARY_CHECKSUM('aaaaaaaaaaaaaaaaa')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||That is what I thought (though at the time I had not seen the
binary_checksum issue.) Thanks.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3d8b515s66gptarutaqhkfvmfq3hgb55hm@.
4ax.com...
> On Wed, 6 Apr 2005 22:43:59 -0400, Louis Davidson wrote:
>
> Hi Louis,
> I *think* that the CONVERT(VARBINARY ...) suggested by Jack is just as
> correct (but slower). And I *know* that the binary_checksum(...)
> suggested by baie dronk is less correct.
> SELECT BINARY_CHECKSUM('a'), BINARY_CHECKSUM('aaaaaaaaaaaaaaaaa')
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Louis,
In some cases, casting to varbinary will yield different results than
applying
a case-sensitive collation.
if cast('password' as varbinary)
= cast('paword' as varbinary)
print 1
else
print 2
-- prints 2
if 'password' collate Latin1_General_CS_AS
= 'paword' collate Latin1_General_CS_AS
print 1
else
print 2
-- prints 1
Another difference is that trailing spaces do not affect case-
sensitive string comparisons, but they do affect binary comparisons.
And if an accent-insensitive collation is desired, you can't get it
if you convert to varbinary.
Also, while the OP was comparing with =, it's worth noting that the
ordering
of data won't be the same when considered as varbinary as when considered
as case-sensitive character data.
if cast('A' as varbinary)
< cast('a' as varbinary)
print 1
else
print 2
if 'A' collate Latin1_General_CS_AS
< 'a' collate Latin1_General_CS_AS
print 1
else
print 2
Louis Davidson wrote:

>Will this be better performance wise, or just more correct? I agree with
>you that I would have suggested this, but not sure either of them were not
>perfectly valid. Can you expand?
>
>|||Well, frankly this makes sens, other than why you would suggest using a
collation comparison in this case versus a binary one. I would actually
prefer 'password' and 'paword' to NOT compare as the same for a password,
while for almost any other reason I would like these to match.
The only change I didn't think to suggest would be to store the password at
the very least as a binary value, but probably in some form of at least mild
encryption.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23ZLVbzIPFHA.2468@.tk2msftngp13.phx.gbl...
> Louis,
> In some cases, casting to varbinary will yield different results than
> applying
> a case-sensitive collation.
> if cast('password' as varbinary)
> = cast('paword' as varbinary)
> print 1
> else
> print 2
> -- prints 2
> if 'password' collate Latin1_General_CS_AS
> = 'paword' collate Latin1_General_CS_AS
> print 1
> else
> print 2
> -- prints 1
> Another difference is that trailing spaces do not affect case-
> sensitive string comparisons, but they do affect binary comparisons.
> And if an accent-insensitive collation is desired, you can't get it
> if you convert to varbinary.
> Also, while the OP was comparing with =, it's worth noting that the
> ordering
> of data won't be the same when considered as varbinary as when considered
> as case-sensitive character data.
> if cast('A' as varbinary)
> < cast('a' as varbinary)
> print 1
> else
> print 2
> if 'A' collate Latin1_General_CS_AS
> < 'a' collate Latin1_General_CS_AS
> print 1
> else
> print 2
>
> Louis Davidson wrote:
>