encryption

Hi
Is there any SQL Server encryption to hide the contents of fields
That is I do have a password column and I want to hide/encrpt it from users
to see it.
Is there any way to do it --like turn encrption ON for a column ?
Is there any way SQL Server help us for this ?
Thanks in advanceIs there any SQL Server encryption to hide the contents of
fields
That is I do have a password column and I want to
hide/encrpt it from users
to see it.
Yes. See permissions-sql server in BOL.
>--Original Message--
>Hi
>Is there any SQL Server encryption to hide the contents
of fields
>That is I do have a password column and I want to
hide/encrpt it from users
>to see it.
>Is there any way to do it --like turn encrption ON for a
column ?
>Is there any way SQL Server help us for this ?
>Thanks in advance
>
>.
>|||right, normally encrypt it NOT let the user see it. BUT
they still need to access the column to get verify their
log on.
i am using DES to encrypt it in my user profile password
column.
>--Original Message--
>There is no column level encryption directly in SQL
Server 2000. You can
>use security features in SQL Server to prevent users from
accessing the
>fields at all, however if you give them permission to
access the field then
>they can see the data. If you need to encrypt the data
then you can do it
>in your application or there several 3rd party encryption
packages that will
>handle this for you. For example:
>http://www.netlib.com/sql-server-encryption.htm
>http://www.protegrity.com/pdf/SD_222_SQL_Datasheet_FINAL_v
4.pdf
>
>--
>Hal Berenson, SQL Server MVP
>True Mountain Group LLC
>
>"Abraham" <binu_ca@.yahoo.com> wrote in message
>news:exFZI3ZQDHA.1024@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> Is there any SQL Server encryption to hide the contents
of fields
>> That is I do have a password column and I want to
hide/encrpt it from
>users
>> to see it.
>> Is there any way to do it --like turn encrption ON for
a column ?
>> Is there any way SQL Server help us for this ?
>> Thanks in advance
>>
>
>.
>|||Unless there is a complelling need to be able to read the original password,
you're probably better of storing a password hash. You can still use the
hash to _verify_ the password, you just can't _read_ the original password.
This is why very few systems these days store the password using reversible
encryption...there's just no need.
Check out my previous post:
http://groups.google.com/groups?&hl=en&lr=&ie=UTF-8&selm=0uacnTNyHJ5plZuiRTvUqQ%40speakeasy.net&rnum=2
--
Dan Farino
Sr. Systems Engineer
Stamps.com, Inc.
news.danATstamps.com
"leecs" <leecs@.silverlgobe.com> wrote in message
news:04a101c34290$dd7349f0$a301280a@.phx.gbl...
> right, normally encrypt it NOT let the user see it. BUT
> they still need to access the column to get verify their
> log on.
> i am using DES to encrypt it in my user profile password
> column.
> >--Original Message--
> >There is no column level encryption directly in SQL
> Server 2000. You can
> >use security features in SQL Server to prevent users from
> accessing the
> >fields at all, however if you give them permission to
> access the field then
> >they can see the data. If you need to encrypt the data
> then you can do it
> >in your application or there several 3rd party encryption
> packages that will
> >handle this for you. For example:
> >
> >http://www.netlib.com/sql-server-encryption.htm
> >http://www.protegrity.com/pdf/SD_222_SQL_Datasheet_FINAL_v
> 4.pdf
> >
> >
> >--
> >Hal Berenson, SQL Server MVP
> >True Mountain Group LLC
> >
> >
> >"Abraham" <binu_ca@.yahoo.com> wrote in message
> >news:exFZI3ZQDHA.1024@.TK2MSFTNGP12.phx.gbl...
> >> Hi
> >> Is there any SQL Server encryption to hide the contents
> of fields
> >> That is I do have a password column and I want to
> hide/encrpt it from
> >users
> >> to see it.
> >> Is there any way to do it --like turn encrption ON for
> a column ?
> >>
> >> Is there any way SQL Server help us for this ?
> >>
> >> Thanks in advance
> >>
> >>
> >
> >
> >.
> >sql

Empty contents of database

Hi All,
We have an SQL database that is filled by a Cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
ThanksHi
This sounds like you may be using MSDE which has a 2GB limit. You could
upgrade to a version of SQL Server that does not have this restriction!
If you do need to delete the data, make sure that you don't delete
static/configuration data that is required by the application to function
correctly, which could be why the empty database did not work (assuming you
did have tables in there!).
If you are deleting data from a table you will need to delete data from any
table that references it with a foreign key unless the FK has cascading
deletes, or alternatively you can disable foreign keys before deleting the
data and then re-instating them.
This may help you find which tables may be used most and therefore not
static data!
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
How to find foreign keys:
http://www.aspfaq.com/show.asp?id=2520
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
If you are deleting a large amount of data you may want to set the recovery
mode to simple during the excercise. You may want to TRUNCATE the table
rather than deleting rows. Truncation will reset identity values, you may
have to reset the identity values separately if you use delete.
Once you have a working template database, you can back it up and then you
will only need to restore the template database instead of deleting data the
next time it gets full.
John
"Luke Cassar" wrote:
> Hi All,
> We have an SQL database that is filled by a Cisco call accounting program.
> This program can only put data in a database which is less then 1.5GB in
> size. Currently the database is 1.5GB and so the program can no longer keep
> data.
> What we wish to do is get rid of all records from this database, but leave
> the table structure, etc.. in tact. We did try using just a blank database,
> but the program does not work in that situation..
> To that end, how can we empty the SQL database of records without upsetting
> any of the other database properties.
> Thanks

Empty contents of database

Hi All,
We have an SQL database that is filled by a Cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
Thanks
Hi
This sounds like you may be using MSDE which has a 2GB limit. You could
upgrade to a version of SQL Server that does not have this restriction!
If you do need to delete the data, make sure that you don't delete
static/configuration data that is required by the application to function
correctly, which could be why the empty database did not work (assuming you
did have tables in there!).
If you are deleting data from a table you will need to delete data from any
table that references it with a foreign key unless the FK has cascading
deletes, or alternatively you can disable foreign keys before deleting the
data and then re-instating them.
This may help you find which tables may be used most and therefore not
static data!
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
How to find foreign keys:
http://www.aspfaq.com/show.asp?id=2520
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
If you are deleting a large amount of data you may want to set the recovery
mode to simple during the excercise. You may want to TRUNCATE the table
rather than deleting rows. Truncation will reset identity values, you may
have to reset the identity values separately if you use delete.
Once you have a working template database, you can back it up and then you
will only need to restore the template database instead of deleting data the
next time it gets full.
John
"Luke Cassar" wrote:

> Hi All,
> We have an SQL database that is filled by a Cisco call accounting program.
> This program can only put data in a database which is less then 1.5GB in
> size. Currently the database is 1.5GB and so the program can no longer keep
> data.
> What we wish to do is get rid of all records from this database, but leave
> the table structure, etc.. in tact. We did try using just a blank database,
> but the program does not work in that situation..
> To that end, how can we empty the SQL database of records without upsetting
> any of the other database properties.
> Thanks

Empty contents of database

Hi All,
We have an SQL database that is filled by a cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
ThanksHi
This sounds like you may be using MSDE which has a 2GB limit. You could
upgrade to a version of SQL Server that does not have this restriction!
If you do need to delete the data, make sure that you don't delete
static/configuration data that is required by the application to function
correctly, which could be why the empty database did not work (assuming you
did have tables in there!).
If you are deleting data from a table you will need to delete data from any
table that references it with a foreign key unless the FK has cascading
deletes, or alternatively you can disable foreign keys before deleting the
data and then re-instating them.
This may help you find which tables may be used most and therefore not
static data!
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
How to find foreign keys:
http://www.aspfaq.com/show.asp?id=2520
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
If you are deleting a large amount of data you may want to set the recovery
mode to simple during the excercise. You may want to TRUNCATE the table
rather than deleting rows. Truncation will reset identity values, you may
have to reset the identity values separately if you use delete.
Once you have a working template database, you can back it up and then you
will only need to restore the template database instead of deleting data the
next time it gets full.
John
"Luke Cassar" wrote:

> Hi All,
> We have an SQL database that is filled by a cisco call accounting program.
> This program can only put data in a database which is less then 1.5GB in
> size. Currently the database is 1.5GB and so the program can no longer ke
ep
> data.
> What we wish to do is get rid of all records from this database, but leave
> the table structure, etc.. in tact. We did try using just a blank database
,
> but the program does not work in that situation..
> To that end, how can we empty the SQL database of records without upsettin
g
> any of the other database properties.
> Thanks

Empty a Database SQL 2000

Hello,
Is there a way to export a database and delete the contents of it as
it creates the backup - leaving the database intact but devoid of
data?
Thanks,
TmuldHi,
You could create SP which uses BACKUP and TRUNCATE TABLE statement, I
guess. Is it something you want, or is there another constraint?
Tmuldoon wrote:
> Hello,
> Is there a way to export a database and delete the contents of it as
> it creates the backup - leaving the database intact but devoid of
> data?
> Thanks,
> Tmuld
>|||See Dan's post some times ago
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1185920307.362745.28610@.d30g2000prg.googlegroups.com...
> Hello,
> Is there a way to export a database and delete the contents of it as
> it creates the backup - leaving the database intact but devoid of
> data?
> Thanks,
> Tmuld
>

Empty a Database SQL 2000

Hello,
Is there a way to export a database and delete the contents of it as
it creates the backup - leaving the database intact but devoid of
data?
Thanks,
Tmuld
Hi,
You could create SP which uses BACKUP and TRUNCATE TABLE statement, I
guess. Is it something you want, or is there another constraint?
Tmuldoon wrote:
> Hello,
> Is there a way to export a database and delete the contents of it as
> it creates the backup - leaving the database intact but devoid of
> data?
> Thanks,
> Tmuld
>
|||See Dan's post some times ago
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1185920307.362745.28610@.d30g2000prg.googlegro ups.com...
> Hello,
> Is there a way to export a database and delete the contents of it as
> it creates the backup - leaving the database intact but devoid of
> data?
> Thanks,
> Tmuld
>

Empty a Database SQL 2000

Hello,
Is there a way to export a database and delete the contents of it as
it creates the backup - leaving the database intact but devoid of
data?
Thanks,
TmuldHi,
You could create SP which uses BACKUP and TRUNCATE TABLE statement, I
guess. Is it something you want, or is there another constraint?
Tmuldoon wrote:
> Hello,
> Is there a way to export a database and delete the contents of it as
> it creates the backup - leaving the database intact but devoid of
> data?
> Thanks,
> Tmuld
>|||See Dan's post some times ago
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1185920307.362745.28610@.d30g2000prg.googlegroups.com...
> Hello,
> Is there a way to export a database and delete the contents of it as
> it creates the backup - leaving the database intact but devoid of
> data?
> Thanks,
> Tmuld
>