Empty Column: tables

Showing posts with label tables. Show all posts

Tuesday, March 27, 2012

Encryption of Database.

Hi,
Can the SQL Database be encrypted such that no one can access tables and
views or see that data?
Do inform.
Also can we do something such that SQL Can be accessed via the front end
ONLY and not directly via SQL Enterprise Manager? Can such a lock be put?
Akshay."iweb" <iweb@.discussions.microsoft.com> wrote in message
news:85867998-1755-4B5C-8676-1C03DA8D93A0@.microsoft.com...
> Hi,
> Can the SQL Database be encrypted such that no one can access tables and
> views or see that data?
> Do inform.
> Also can we do something such that SQL Can be accessed via the front end
> ONLY and not directly via SQL Enterprise Manager? Can such a lock be put?
> Akshay.
>
SQL Server 2005 gives you several options for encrypting data.
Only allowing access thorugh a front-end is neither practical nor prudent.
If you did so, how would you maintain your database, maintain indexes,
backups, restores etc.
BTW: Enterprise Manager is a "front-end" tool.
You can (and should) use the security that is built in to SQL Server to
handle security. Give only those who need access the appropriate
permissions at the server, database and object levels.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Before you do anything like encryption or passwords, please thoroughly read
and understand the section on security in Book-on-Line. Then you will be in
a better position to ask questions about which ever kind of security you
think is best and how to set it up .
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"iweb" <iweb@.discussions.microsoft.com> wrote in message
news:85867998-1755-4B5C-8676-1C03DA8D93A0@.microsoft.com...
> Hi,
> Can the SQL Database be encrypted such that no one can access tables and
> views or see that data?
> Do inform.
> Also can we do something such that SQL Can be accessed via the front end
> ONLY and not directly via SQL Enterprise Manager? Can such a lock be put?
> Akshay.
>|||If you truly want no one to access the database, you should not install it.
If you want only certain people to access it, you can create the database on
an encrypted filesystem or in SQL Server 2005 you can encrypt critical parts
of the data.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"iweb" <iweb@.discussions.microsoft.com> wrote in message
news:85867998-1755-4B5C-8676-1C03DA8D93A0@.microsoft.com...
> Hi,
> Can the SQL Database be encrypted such that no one can access tables and
> views or see that data?
> Do inform.
> Also can we do something such that SQL Can be accessed via the front end
> ONLY and not directly via SQL Enterprise Manager? Can such a lock be put?
> Akshay.
>|||Can I get more details on how to encrypt critical parts of the data using SQL
Server 2005?
Can I encrypt tables, views all? or just the data within them?
Akshay.
"Roger Wolter[MSFT]" wrote:
> If you truly want no one to access the database, you should not install it.
> If you want only certain people to access it, you can create the database on
> an encrypted filesystem or in SQL Server 2005 you can encrypt critical parts
> of the data.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "iweb" <iweb@.discussions.microsoft.com> wrote in message
> news:85867998-1755-4B5C-8676-1C03DA8D93A0@.microsoft.com...
> > Hi,
> >
> > Can the SQL Database be encrypted such that no one can access tables and
> > views or see that data?
> >
> > Do inform.
> >
> > Also can we do something such that SQL Can be accessed via the front end
> > ONLY and not directly via SQL Enterprise Manager? Can such a lock be put?
> >
> > Akshay.
> >
> >
>
>|||Where can i read these books-on-line?
Akshay.
"Arnie Rowland" wrote:
> Before you do anything like encryption or passwords, please thoroughly read
> and understand the section on security in Book-on-Line. Then you will be in
> a better position to ask questions about which ever kind of security you
> think is best and how to set it up .
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "iweb" <iweb@.discussions.microsoft.com> wrote in message
> news:85867998-1755-4B5C-8676-1C03DA8D93A0@.microsoft.com...
> > Hi,
> >
> > Can the SQL Database be encrypted such that no one can access tables and
> > views or see that data?
> >
> > Do inform.
> >
> > Also can we do something such that SQL Can be accessed via the front end
> > ONLY and not directly via SQL Enterprise Manager? Can such a lock be put?
> >
> > Akshay.
> >
> >
>
>|||Books on Line is the SQL Server help documentation. It comes on the SQL
Server installation disks, and you can go to www.Microsoft.com/sql and
download the latest version.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"iweb" <iweb@.discussions.microsoft.com> wrote in message
news:6DC83526-B0FF-4DAC-89F1-A31A1D15D20C@.microsoft.com...
> Where can i read these books-on-line?
> Akshay.
> "Arnie Rowland" wrote:
>> Before you do anything like encryption or passwords, please thoroughly
>> read
>> and understand the section on security in Book-on-Line. Then you will be
>> in
>> a better position to ask questions about which ever kind of security you
>> think is best and how to set it up .
>> --
>> Arnie Rowland, YACE*
>> "To be successful, your heart must accompany your knowledge."
>> *Yet Another Certification Exam
>>
>> "iweb" <iweb@.discussions.microsoft.com> wrote in message
>> news:85867998-1755-4B5C-8676-1C03DA8D93A0@.microsoft.com...
>> > Hi,
>> >
>> > Can the SQL Database be encrypted such that no one can access tables
>> > and
>> > views or see that data?
>> >
>> > Do inform.
>> >
>> > Also can we do something such that SQL Can be accessed via the front
>> > end
>> > ONLY and not directly via SQL Enterprise Manager? Can such a lock be
>> > put?
>> >
>> > Akshay.
>> >
>> >
>>|||http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx
http://msdn2.microsoft.com/en-us/library/ms173744.aspx
You can encrypt everything but you don't want to. You can't select on
encrypted values so your data would become rather useless because you would
have to decrypt each row until you found the one you were looking for which
could take a very long time.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"iweb" <iweb@.discussions.microsoft.com> wrote in message
news:402A11CD-451F-4512-857F-5B09DF68D8E5@.microsoft.com...
> Can I get more details on how to encrypt critical parts of the data using
> SQL
> Server 2005?
> Can I encrypt tables, views all? or just the data within them?
> Akshay.
> "Roger Wolter[MSFT]" wrote:
>> If you truly want no one to access the database, you should not install
>> it.
>> If you want only certain people to access it, you can create the database
>> on
>> an encrypted filesystem or in SQL Server 2005 you can encrypt critical
>> parts
>> of the data.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "iweb" <iweb@.discussions.microsoft.com> wrote in message
>> news:85867998-1755-4B5C-8676-1C03DA8D93A0@.microsoft.com...
>> > Hi,
>> >
>> > Can the SQL Database be encrypted such that no one can access tables
>> > and
>> > views or see that data?
>> >
>> > Do inform.
>> >
>> > Also can we do something such that SQL Can be accessed via the front
>> > end
>> > ONLY and not directly via SQL Enterprise Manager? Can such a lock be
>> > put?
>> >
>> > Akshay.
>> >
>> >
>>sql

Monday, March 26, 2012

encryption of column in sqlserver 2000

Hi
I have some sensitive info like Credit card number in some of my
database tables. Is there a way to encrypt these credit card information in
database. I am running SQL server 2000 standard edition
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> I have some sensitive info like Credit card number in some of
> my database tables. Is there a way to encrypt these credit card
> information in database. I am running SQL server 2000 standard edition
> Thanks
> Mangesh
No native support for encryption in the database. You can
encrypt/decrypt in the application if you need to. There are a number of
freely available encryption algorithms you can use. Or you could try
securing the database to keep prying eyes away. What are you mostly
concerned about?
--
David Gugick
Imceda Software
www.imceda.com|||Thanks
I am concerned about the credit card information in the database.
What is the best and most widely encryption tools used with SQL server 2000.
Mangesh
"David Gugick" wrote:
> Mangesh Deshpande wrote:
> > Hi
> >
> > I have some sensitive info like Credit card number in some of
> > my database tables. Is there a way to encrypt these credit card
> > information in database. I am running SQL server 2000 standard edition
> >
> > Thanks
> > Mangesh
> No native support for encryption in the database. You can
> encrypt/decrypt in the application if you need to. There are a number of
> freely available encryption algorithms you can use. Or you could try
> securing the database to keep prying eyes away. What are you mostly
> concerned about?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks
> I am concerned about the credit card information in the database.
> What is the best and most widely encryption tools used with SQL
> server 2000.
>
Yes, but why are you concerned. Who is going to be able to access this
data? How are they going to access it? Do you have security in place to
prevent unauthorized access to the underlying tables? How does one
currently access the CC information?
You could remove all access to the table and provide a view with a
computed column that just returns the last 4 digits of the CC number for
display: For example:
Create Table order_info3453453 (
cust_name varchar(25) not null,
cc_nbr varchar(16) not null)
DENY ALL ON order_info3453453 TO public
insert into order_info3453453 values ('David', '234234234234234')
insert into order_info3453453 values ('Jeff', '3784662345444441')
Create View order_info_view4324234
as
Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
From order_info3453453
grant select on order_info_view4324234 to public
select * from order_info_view4324234
cust_name cc_nbr
-- --
David 4234
David 4234
Jeff 4441
I saw your other post regarding backups, so maybe that's what you're
after.
--
David Gugick
Imceda Software
www.imceda.com|||Thanks David
I am primarily a oracle DBA and we used oracle supplied encryption which
allows the CC info to be accessed from certain IPs. The users are all
protected but management is worried in case the userinfo is disclosed and
unauthorized user try to access the DB he should be blocked from seeing CC
and password info in database by IP and first and second by encryption.
Management kind of like that idea, but now they want me to do the same thing
in sqlserver and was looking for various possible scenarious.
I found xp_encrypt and tested it but I am not sure if there any good tools
avaiable.
"David Gugick" wrote:
> Mangesh Deshpande wrote:
> > Thanks
> > I am concerned about the credit card information in the database.
> > What is the best and most widely encryption tools used with SQL
> > server 2000.
> >
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks David
> I am primarily a oracle DBA and we used oracle supplied encryption
> which allows the CC info to be accessed from certain IPs. The users
> are all protected but management is worried in case the userinfo is
> disclosed and unauthorized user try to access the DB he should be
> blocked from seeing CC and password info in database by IP and first
> and second by encryption.
> Management kind of like that idea, but now they want me to do the
> same thing in sqlserver and was looking for various possible
> scenarious.
> I found xp_encrypt and tested it but I am not sure if there any good
> tools avaiable.
Maybe this will help:
http://www.windowsitpro.com/Article/ArticleID/44550/44550.html?Ad=1
Go a google search for ["SQL Server" Encryption] and see what comes up.
There are third-party solutions available for data encryption. You could
also manage this from the app or middle-tier if necessary.
Regarding your comments about unauthorized access:
You can revoke access to the underlying tables using built-in security.
You can grant access to the data to the necessary groups/individuals
using specific grants on stored procedures. If no one has SELECT rights
on the table and cannot execute a procedure that selects the data, then
no one can see it. If you use the computed column technique as I
mentioned in my last post, you can show end users the last 4 digits of
the cc number so those in the company that require access to information
for contact with customers can have enough to go on (obviously they
could also have access to the type of card).
So how would an unauthorized user access the database? Well, first they
would need a valid login and would also need to have sufficient rights
to execute the necessary procedures to return the customer information.
You can probably limit access to the server by IP / MAC Address using
your firewall.
For backups, you do need encryption. And as I mentioned I would check
out LiteSpeed (free trial on web site).
David Gugick
Imceda Software
www.imceda.com|||The encryption/decryption of the data is better handled in the presentation
layer tier of the system. There, you can use the native Crypto32 API calls.
If you are using any of the .NET compilers, these are exposed through
System.Security calls.
Sincerely,
Anthony Thomas
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
Mangesh Deshpande wrote:
> Thanks
> I am concerned about the credit card information in the database.
> What is the best and most widely encryption tools used with SQL
> server 2000.
>
Yes, but why are you concerned. Who is going to be able to access this
data? How are they going to access it? Do you have security in place to
prevent unauthorized access to the underlying tables? How does one
currently access the CC information?
You could remove all access to the table and provide a view with a
computed column that just returns the last 4 digits of the CC number for
display: For example:
Create Table order_info3453453 (
cust_name varchar(25) not null,
cc_nbr varchar(16) not null)
DENY ALL ON order_info3453453 TO public
insert into order_info3453453 values ('David', '234234234234234')
insert into order_info3453453 values ('Jeff', '3784662345444441')
Create View order_info_view4324234
as
Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
From order_info3453453
grant select on order_info_view4324234 to public
select * from order_info_view4324234
cust_name cc_nbr
-- --
David 4234
David 4234
Jeff 4441
I saw your other post regarding backups, so maybe that's what you're
after.
--
David Gugick
Imceda Software
www.imceda.com|||I personally like this idea, but cannot imagine how to do that. Any clues ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> The encryption/decryption of the data is better handled in the
presentation
> layer tier of the system. There, you can use the native Crypto32 API
calls.
> If you are using any of the .NET compilers, these are exposed through
> System.Security calls.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> > Thanks
> > I am concerned about the credit card information in the database.
> > What is the best and most widely encryption tools used with SQL
> > server 2000.
> >
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||I just told you: the .NET framework exposes the Crypto API through the
System.Security objects.
Sincerely,
Anthony Thomas
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
I personally like this idea, but cannot imagine how to do that. Any clues ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> The encryption/decryption of the data is better handled in the
presentation
> layer tier of the system. There, you can use the native Crypto32 API
calls.
> If you are using any of the .NET compilers, these are exposed through
> System.Security calls.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> > Thanks
> > I am concerned about the credit card information in the database.
> > What is the best and most widely encryption tools used with SQL
> > server 2000.
> >
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Hi Antony,
O.K. I will try to restate the question.
For example I am using datagrid bound to table in some dataset.
Data table is populated when form is loaded, and data source is updated when
I close the form.
So I can use some logic to decrypt data from table when the form is going to
load and I can encrypt data again before the form is closed. Do you suggest
writing special dataadapter class, or might be some different approach ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uAyhm73LFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I just told you: the .NET framework exposes the Crypto API through the
> System.Security objects.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> I personally like this idea, but cannot imagine how to do that. Any clues
?
> Regards,
> Daniel
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> > The encryption/decryption of the data is better handled in the
> presentation
> > layer tier of the system. There, you can use the native Crypto32 API
> calls.
> > If you are using any of the .NET compilers, these are exposed through
> > System.Security calls.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> > news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> > Mangesh Deshpande wrote:
> > > Thanks
> > > I am concerned about the credit card information in the database.
> > > What is the best and most widely encryption tools used with SQL
> > > server 2000.
> > >
> >
> > Yes, but why are you concerned. Who is going to be able to access this
> > data? How are they going to access it? Do you have security in place to
> > prevent unauthorized access to the underlying tables? How does one
> > currently access the CC information?
> >
> > You could remove all access to the table and provide a view with a
> > computed column that just returns the last 4 digits of the CC number for
> > display: For example:
> >
> > Create Table order_info3453453 (
> > cust_name varchar(25) not null,
> > cc_nbr varchar(16) not null)
> >
> > DENY ALL ON order_info3453453 TO public
> >
> > insert into order_info3453453 values ('David', '234234234234234')
> > insert into order_info3453453 values ('Jeff', '3784662345444441')
> >
> > Create View order_info_view4324234
> > as
> > Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> > From order_info3453453
> >
> > grant select on order_info_view4324234 to public
> >
> > select * from order_info_view4324234
> >
> > cust_name cc_nbr
> > -- --
> > David 4234
> > David 4234
> > Jeff 4441
> >
> >
> > I saw your other post regarding backups, so maybe that's what you're
> > after.
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >
>|||If you do it that way, your system will be SLOOOOW.
I would bind your grid to the desciptive part of the result set (unencrypted
parts). Then, when you users find a particular result they want additional
information on or want to modify, I would launch an independent dialogue
that decrypts the entire contents, but for that one row only.
Allowing users to edit a bound grid is a bad design choice for concurrent
systems anyway, and this way you will only have to run through the
decrypt/encrypt algorythms only when a use specifically request that single
piece of information. You do not want to have to incur this additional
logic while the users are merely searching for the information.
Sincerely,
Anthony Thomas
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:e9hiS$AMFHA.3184@.TK2MSFTNGP09.phx.gbl...
Hi Antony,
O.K. I will try to restate the question.
For example I am using datagrid bound to table in some dataset.
Data table is populated when form is loaded, and data source is updated when
I close the form.
So I can use some logic to decrypt data from table when the form is going to
load and I can encrypt data again before the form is closed. Do you suggest
writing special dataadapter class, or might be some different approach ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uAyhm73LFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I just told you: the .NET framework exposes the Crypto API through the
> System.Security objects.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> I personally like this idea, but cannot imagine how to do that. Any clues
?
> Regards,
> Daniel
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> > The encryption/decryption of the data is better handled in the
> presentation
> > layer tier of the system. There, you can use the native Crypto32 API
> calls.
> > If you are using any of the .NET compilers, these are exposed through
> > System.Security calls.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> > news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> > Mangesh Deshpande wrote:
> > > Thanks
> > > I am concerned about the credit card information in the database.
> > > What is the best and most widely encryption tools used with SQL
> > > server 2000.
> > >
> >
> > Yes, but why are you concerned. Who is going to be able to access this
> > data? How are they going to access it? Do you have security in place to
> > prevent unauthorized access to the underlying tables? How does one
> > currently access the CC information?
> >
> > You could remove all access to the table and provide a view with a
> > computed column that just returns the last 4 digits of the CC number for
> > display: For example:
> >
> > Create Table order_info3453453 (
> > cust_name varchar(25) not null,
> > cc_nbr varchar(16) not null)
> >
> > DENY ALL ON order_info3453453 TO public
> >
> > insert into order_info3453453 values ('David', '234234234234234')
> > insert into order_info3453453 values ('Jeff', '3784662345444441')
> >
> > Create View order_info_view4324234
> > as
> > Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> > From order_info3453453
> >
> > grant select on order_info_view4324234 to public
> >
> > select * from order_info_view4324234
> >
> > cust_name cc_nbr
> > -- --
> > David 4234
> > David 4234
> > Jeff 4441
> >
> >
> > I saw your other post regarding backups, so maybe that's what you're
> > after.
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >
>

encryption of column in sqlserver 2000

Wednesday, March 21, 2012

encrypting tables

Hi Guys,
is there any option to encrypt table structure in sql 2000.
procedures and triggers i can encrypt.
for eg: i have one user who is having only select
permission on one table but he can see all the tables
structure and procedure even though he can not edit.
any workaround to achieve this?
Pls advice me.
Thanks
BijuNo. There is no built in functionality to encrypt the table structure.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Be careful. The built-in encryption mechanisms of MS are completely
vulnerable. Decryption programs are freely available. The closest you will
ever get to securing an MSSQL database is with the NelLib offerings. We
have investigated this for six months now and have been deeply disappointed
with the weakness of the security in MSSQL. If you are writing mission
critical applications using MSSQL as your backend our advice is that you
make absolutely certain that you are satisfied with living with the security
holes. A good place to start is www.sqlsecurity.com and all the links and
literature advertised there. Hopefully MS will address these issues with
the next version of MSSQL.
"biju george" <anonymous@.discussions.microsoft.com> wrote in message
news:17e5201c42228$4dcaf780$a001280a@.phx
.gbl...
> Hi Guys,
> is there any option to encrypt table structure in sql 2000.
> procedures and triggers i can encrypt.
> for eg: i have one user who is having only select
> permission on one table but he can see all the tables
> structure and procedure even though he can not edit.
> any workaround to achieve this?
> Pls advice me.
> Thanks
> Biju

Sunday, March 11, 2012

Encrypted data in tables

How could I store encrypted data in tables in SQL Server 2000 Database.
Say I have made a table to store passwords and I don't want every user to be able to view this table.
Thanks
Jasmitado you really need to store the passwords, or could you just store a one-way hashed value of the password instead, this way the passwords are realivily useless.

if not surely you can set it so that only a certain user can select (and others) from this table?

Friday, March 9, 2012

encrypt database --

Hi,
Sorry for the message in spanish I confused the group.
Is there a way to encrypt a database (tables, columns, etc.) ? I want that
nobody can see the data model of my application
Probably it's impossible to deny access to the database, but at least if
someone is trying to copy my design he/she will see the name of objects,
columns, and others encrypted. (For example instead of see the table
ARTICLE, see symbols ="!!$%&/- )
I'll appreciate your comments.
Edmundo J. DavilaYou can assign certificates and encryption keys under the security
folder of a database in SQL Server Management Studio, however, these
only encrypt the data stream that is sent from a SQL Server instance
to any SQL Server Agents.
If you install SQL Server to run from inside of Microsoft Virtual
Server, then the entire database file will be stacker compressed which
would be unreadable to human eyes. But, with any kind of encryption,
SQL Server will have to spend time decrypting fields as it walks any
kind of search and this will cause a significant performance hit.

encrypt credit card details within SQL 2000/2005

Hi, I am hoping someone could shed some light on encrypting columns within
database tables.
What I need to do is encrypt the credit card field of a sql table. What is
the best way of going about this? This doesnt seem to be well documented.
Any help most appreciated.
Cheers, PeterSQL Server 2000 does not provide encryption functionality out of the
box, you will have to either do this on the client and sending the
already encrypted data to the server or send the data to the server
(you will have to be aware of man-in-the-middle attacks and consider
protocol encryption for securing this) and encrypt it either using
your own encryption algorythm or any other third party procedure
(often xp_s) to do this. SQl Server 2005 intriduced a new encryption
functionalty, based on either certificates or passphrases, not to
extened this explanation further you can read a lot about that in the
BOL or on the internet.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||For SQL 2005 look up the built-in T-SQL Encryption functionality in BOL.
For SQL 2000, either do it client-side as Jens suggested, or get some
utility XP's like this:
http://www.sqlservercentral.com/col...oolkitpart1.asp
"peter walker" <p.walker@.nospam.com> wrote in message
news:eb3yxLeRHHA.4632@.TK2MSFTNGP04.phx.gbl...
> Hi, I am hoping someone could shed some light on encrypting columns within
> database tables.
> What I need to do is encrypt the credit card field of a sql table. What is
> the best way of going about this? This doesnt seem to be well documented.
> Any help most appreciated.
> Cheers, Peter
>

Encrpyt

Hi
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
MexHello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>|||On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select *
> from names
>
Do you really want to return "crap" to some users? I suspect not. I
think your real requirement is to deny user access to data certain
data. You can do that using GRANT / DENY and no encryption is
required.
Encryption is not a substitute for data access control.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good :)
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
>> Hi
>> What is the best method to encrypt data in tables?
>> Lets say if special program or user calls select * from names
>> then all data is visible and normal, but all athers get "crap" with
>> select *
>> from names
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi
"Redivivus" wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good :)
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sql.html#good_practices
HTH
John|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).
> sry my english is not very good :)
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
>> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
>> Hi
>> What is the best method to encrypt data in tables?
>> Lets say if special program or user calls select * from names
>> then all data is visible and normal, but all athers get "crap" with
>> select *
>> from names
>>
>> Do you really want to return "crap" to some users? I suspect not. I
>> think your real requirement is to deny user access to data certain
>> data. You can do that using GRANT / DENY and no encryption is
>> required.
>> Encryption is not a substitute for data access control.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Sunday, February 26, 2012

Enabling "not for replication" contraint on a mass level

HI,
sql server 2000,uni directional transaction replication with initial
snapshot . Can not be simpler than this.
My database has around 100 tables and ever time i setup replication, i have
to make sure that none of the referential integrity constraints are copied
over to the subscriber because they are not set for "not for replication".
And if i want to set it up , i might have to go to each and every table and
specify this contraint. My questions are
1. is there any way i can have all the referential integrity contraints on
the subscriber and still not have "not for replication" checked?
2. if the answer is NO , then is there any way , any script i could use to
mass update all the PKs\ FKs with "Not For Replication" constraint?
Thanks
Muzammil
AFAIK - there is no way of doing this - but why do you want to enforce DRI
on the subscriber? Normally the subscriber is read only.
Hilary Cotter
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
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:34ACBAE1-9F82-46CB-B844-B5B68F2B4798@.microsoft.com...
> HI,
> sql server 2000,uni directional transaction replication with initial
> snapshot . Can not be simpler than this.
> My database has around 100 tables and ever time i setup replication, i
have
> to make sure that none of the referential integrity constraints are copied
> over to the subscriber because they are not set for "not for replication".
> And if i want to set it up , i might have to go to each and every table
and
> specify this contraint. My questions are
> 1. is there any way i can have all the referential integrity contraints on
> the subscriber and still not have "not for replication" checked?
> 2. if the answer is NO , then is there any way , any script i could use to
> mass update all the PKs\ FKs with "Not For Replication" constraint?
> Thanks
> Muzammil
|||Hilary, thanks for you response.
Yes even though the subscriber is read only , but the client just can not
accept that the database does not have any referencial integrity contraints.
I still can not beleive that there is no way i could write down a script to
get every table from Sysobjects , find the referential integrity contraints
and append the "not for replication" contraints on them. Please suggest.
Thanks
Muzammil
"Hilary Cotter" wrote:

> AFAIK - there is no way of doing this - but why do you want to enforce DRI
> on the subscriber? Normally the subscriber is read only.
> --
> Hilary Cotter
> 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
> "sameer" <sameer@.discussions.microsoft.com> wrote in message
> news:34ACBAE1-9F82-46CB-B844-B5B68F2B4798@.microsoft.com...
> have
> and
>
>

Enable/Disable Trigger

I have a stored procedure disable and enable triggers on several tables.
What would be a workaround to using the alter table with disable and enable
triggers listed below.
Thanks,
CREATE PROCEDURE dbo.K_ManualMoves
as
BEGIN
alter table T_Moves
disable trigger iu_t_Moves
alter table T_Fees_Moves
disable trigger iu_t_Fees
Begin
Body Stored Procedure
End
alter table T_Moves
enable trigger iu_t_Moves
alter table T_Fees_Moves
enable trigger iu_t_Fees
End
GOWhy do you need a workaround, if you want to disable the trigger use Alter
table as you have done, Or is there some reason you do not want to disable
the triggers? Without seeing what the rest of the code is it is difficult to
understand your problem
Mike John
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:09A5FD91-8F76-4431-B17B-0F4D4FF85B24@.microsoft.com...
> I have a stored procedure disable and enable triggers on several tables.
> What would be a workaround to using the alter table with disable and
> enable
> triggers listed below.
> Thanks,
> CREATE PROCEDURE dbo.K_ManualMoves
> as
> BEGIN
> alter table T_Moves
> disable trigger iu_t_Moves
> alter table T_Fees_Moves
> disable trigger iu_t_Fees
> Begin
> Body Stored Procedure
> End
> alter table T_Moves
> enable trigger iu_t_Moves
> alter table T_Fees_Moves
> enable trigger iu_t_Fees
> End
> GO|||That's how to do it.
I'm guessing your concern is that the triggers would be disabled while a
process that should fire them runs. In that case, the most solid option
is to have the triggers manage themselves, based on data being modified
(like a source column indicating what process changed the data), or
environment settings (like user or application) or something else
limited to the scope of the modification.
Joe K. wrote:
> I have a stored procedure disable and enable triggers on several tables.
> What would be a workaround to using the alter table with disable and enabl
e
> triggers listed below.
> Thanks,
> CREATE PROCEDURE dbo.K_ManualMoves
> as
> BEGIN
> alter table T_Moves
> disable trigger iu_t_Moves
> alter table T_Fees_Moves
> disable trigger iu_t_Fees
> Begin
> Body Stored Procedure
> End
> alter table T_Moves
> enable trigger iu_t_Moves
> alter table T_Fees_Moves
> enable trigger iu_t_Fees
> End
> GO|||Joe K. (Joe K.@.discussions.microsoft.com) writes:

> I have a stored procedure disable and enable triggers on several tables.
> What would be a workaround to using the alter table with disable and
> enable triggers listed below.
In the stored procedure create a temp table:
CREATE TABLE #trigger$disabled(a int NOT NULL)
In the trigger you would add
IF object_id('tempdb..#trigger$disabled') IS NOT NULL
RETURN
Even better you can disable only the part that you that you will violate.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sunday, February 19, 2012

Enable or disable a relationship between 2 tables at runtime?

Is that possible?
Can somebody show me code in how to accomplish this.

ThanksIf you mean foreign key relationship: yes -- see the ForeignKey.IsEnabled property. You can switch FK relationships on and off.

Friday, February 17, 2012

Empty Tables at subscriber without error messages with Transactional Replication

Hi,
I use a transactional replication and sometimes I realize that all or
some tables at subscriber are empty but the agent history don't log any
error message. All the informations at the publisher is ok. This
morning, I looked to the data at 10AM and all was fine but an hour
after some tables was empty. The snapshot doesn't start and no one use
the subscriber's database.
Any ideas?
Patrick Simard
Patrick - do you have access to Log Explorer or similar tools? It might be
worht investigating the subscriber logs. Typically I've fond a developer
process responsible for this type of thing, or a cascade trigger which
shouldn't exist on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Empty table problem

I am creating three tables in a DataSet using three stored procedures and I need a way to know that either the tables have at least one row or preferably which if any has no rows. How can I do that?

using three SP's, one for each table:

SqlCommand cmd =newSqlCommand("CompanyCheck", con);
SqlCommand cmd2 =newSqlCommand("ContractorVerify", con);
SqlCommand cmd3 =newSqlCommand("StoreLocation", con);

cmd.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
cmd3.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@.CompanyID", CompanyID);
cmd2.Parameters.AddWithValue("@.CompanyID", CompanyID);
cmd3.Parameters.AddWithValue("@.CompanyID", CompanyID);
SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
da.Fill(ds, "CompanyInfo");
da.SelectCommand = cmd2;
da.Fill(ds, "ContractorInfo");
da.SelectCommand = cmd3;
da.Fill(ds, "StoreInfo");
}
catch
{ throw new ApplicationException("Data error"); }
finally
{
con.Close();
}

Thank you

Zoltac,

I would try

if (ds.Tables.Count == 3)
{
if(ds.Tables[0].Rows.Count > 0)
{ }
if(ds.Tables[1].Rows.Count > 0)
{ }
if(ds.Tables[2].Rows.Count > 0)
{ }

Is this what you are looking for? If not, I might be misunderstanding your question.

|||

Thank you - Exactly what I was looking for - maybe one of these days I will finally get it. I need a big wall chart with all the properties and methods, etc,, etc. and maybe then I will have a clue as to what Net 2.0. is made up from. appreciate the quick response.

Empty Subreport in Table

I have a problem with an subreport. I have master-detail relatinship
between tables. My table has three rows, 1st row is table header, 2nd
row is Master data and 3rd row is place where I put mine child
table(subreport).
If my subreport doesnt have any data this row is still visible(empty
space), I search all over internet and didnt find resolve for this
problem. Im working with Visual Studio 20005.
Is there any way for finding out number of rows in subreport, and use
that in Visibility expression of the 3rd row'
Thanks in advance,
Alen Husanovicanyone?|||I am having the exact same problem. Anybody have a proposed solution?
"alenhusanovic@.gmail.com" wrote:
> anyone?
>|||I am, also, having the same problem. Does anyone have a solution or isn't
there one? I would appreciate some kind of a reply.
"bjkaledas" wrote:
> I am having the exact same problem. Anybody have a proposed solution?
> "alenhusanovic@.gmail.com" wrote:
> > anyone?
> >
> >|||And the problem is?
When asking for help you need to include the question. If you make it
difficult to find out the question then you decrease the chance of someone
answering it. In this case I don't have the starting post on this thread so
I don't have a clue what your problem is.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MikeS" <MikeS@.discussions.microsoft.com> wrote in message
news:5015E622-82AA-4446-846A-A5A039891708@.microsoft.com...
>I am, also, having the same problem. Does anyone have a solution or isn't
> there one? I would appreciate some kind of a reply.
> "bjkaledas" wrote:
>> I am having the exact same problem. Anybody have a proposed solution?
>> "alenhusanovic@.gmail.com" wrote:
>> > anyone?
>> >
>> >|||Sorry, here is the starting post:
I have a problem with an subreport. I have master-detail relatinship
between tables. My table has three rows, 1st row is table header, 2nd
row is Master data and 3rd row is place where I put mine child
table(subreport).
If my subreport doesnt have any data this row is still visible(empty
space), I search all over internet and didnt find resolve for this
problem. Im working with Visual Studio 20005.
Is there any way for finding out number of rows in subreport, and use
that in Visibility expression of the 3rd row'
Thanks in advance,
Alen Husanovic
"Bruce L-C [MVP]" wrote:
> And the problem is?
> When asking for help you need to include the question. If you make it
> difficult to find out the question then you decrease the chance of someone
> answering it. In this case I don't have the starting post on this thread so
> I don't have a clue what your problem is.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MikeS" <MikeS@.discussions.microsoft.com> wrote in message
> news:5015E622-82AA-4446-846A-A5A039891708@.microsoft.com...
> >I am, also, having the same problem. Does anyone have a solution or isn't
> > there one? I would appreciate some kind of a reply.
> >
> > "bjkaledas" wrote:
> >
> >> I am having the exact same problem. Anybody have a proposed solution?
> >>
> >> "alenhusanovic@.gmail.com" wrote:
> >>
> >> > anyone?
> >> >
> >> >
>
>|||There is a norows property where you can have text be displayed when there
is no data but I am not aware of a way to do what you desire here.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ek%23Wv%23MCHHA.3924@.TK2MSFTNGP02.phx.gbl...
> And the problem is?
> When asking for help you need to include the question. If you make it
> difficult to find out the question then you decrease the chance of someone
> answering it. In this case I don't have the starting post on this thread
> so I don't have a clue what your problem is.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MikeS" <MikeS@.discussions.microsoft.com> wrote in message
> news:5015E622-82AA-4446-846A-A5A039891708@.microsoft.com...
>>I am, also, having the same problem. Does anyone have a solution or isn't
>> there one? I would appreciate some kind of a reply.
>> "bjkaledas" wrote:
>> I am having the exact same problem. Anybody have a proposed solution?
>> "alenhusanovic@.gmail.com" wrote:
>> > anyone?
>> >
>> >
>

Empty Space between two tables

I have placed two tables from the toolbox onto the report. Filled the two tables Table A, Table B with data from the database. When i run the report I want the Table B's data to be appended to Table A's data. But, when I tried to do this, there is lot of empty space between two tables's data. How can i get rid of this empty space.
Thank You so much.

I don't understand why you separating data in 2 tables. It's a master - detail report? Anyway, the text box support expressions. You can do an expresion like this =!Fields(TableAField1.Value)+" "+!Fields(TableBField1.Value).
Is that what you want to do?
Can you be more specific? I think you have a midunderstanding of the kind of report you want to do or a query mistake.
VectorR3

Empty Space between two tables

if u put the two tables close together there will be no space
u can remove the second table headers and footer from the first table and see
check padding
i made something like that and it works fine
hope it help

Wednesday, February 15, 2012

Empty Database Not Shrinking

SQL Server 2K database with size 117GB. I have dropped the all the tables.
So No user tables are there in the database.
Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase. But
its not reducing the size of the database.
When I take a backup, the backup size is coming close to 200MB only. Log
file is not big, its close to 100 MB in size.
I only have dbo permissions on this database, not sysadmin and I am not sure
about the initial size of the database.
How can I shrink this database?What do the following yield?
USE [your_database];
GO
SELECT name,size FROM sysfiles;
SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
ORDER BY rows DESC;
--
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
> SQL Server 2K database with size 117GB. I have dropped the all the tables.
> So No user tables are there in the database.
> Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase.
> But its not reducing the size of the database.
> When I take a backup, the backup size is coming close to 200MB only. Log
> file is not big, its close to 100 MB in size.
> I only have dbo permissions on this database, not sysadmin and I am not
> sure about the initial size of the database.
> How can I shrink this database?|||Here is the query results
name size
-- --
SNRDEV_Data 14712960
SNRDEV_Log 128000
(2 row(s) affected)
table_or_view rows
-- --
dtproperties 0
(1 row(s) affected)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
> What do the following yield?
> USE [your_database];
> GO
> SELECT name,size FROM sysfiles;
> SELECT TOP 10
> table_or_view = OBJECT_NAME(id), rows
> FROM sysindexes
> WHERE indid IN (0,1)
> AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
> ORDER BY rows DESC;
> --
> Aaron Bertrand
> SQL Server MVP
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
>> SQL Server 2K database with size 117GB. I have dropped the all the
>> tables. So No user tables are there in the database.
>> Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase.
>> But its not reducing the size of the database.
>> When I take a backup, the backup size is coming close to 200MB only. Log
>> file is not big, its close to 100 MB in size.
>> I only have dbo permissions on this database, not sysadmin and I am not
>> sure about the initial size of the database.
>> How can I shrink this database?
>|||Ok, so maybe some system table is taking up space?
SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
ORDER BY rows DESC;
You said basically that shrinking doesn't work. But what *exactly* is the
result of
DBCC SHRINKFILER(SNRDEV_Data, 1)
?
Of course, final silly question, you are running these queries from SNRDEV,
right? And that is the database you are trying to shrink?
--
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
> Here is the query results
> name size
> -- --
> SNRDEV_Data 14712960
> SNRDEV_Log 128000
> (2 row(s) affected)
> table_or_view rows
> -- --
> dtproperties 0
> (1 row(s) affected)|||I don't think you will be able to shrink the data file beyond its minimum
configured size (see ALTER DATABASE).
Why don't you just drop the database and re-create it at a more appropriate
size?
--
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
> Here is the query results
> name size
> -- --
> SNRDEV_Data 14712960
> SNRDEV_Log 128000
> (2 row(s) affected)
> table_or_view rows
> -- --
> dtproperties 0
> (1 row(s) affected)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
>> What do the following yield?
>> USE [your_database];
>> GO
>> SELECT name,size FROM sysfiles;
>> SELECT TOP 10
>> table_or_view = OBJECT_NAME(id), rows
>> FROM sysindexes
>> WHERE indid IN (0,1)
>> AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
>> ORDER BY rows DESC;
>> --
>> Aaron Bertrand
>> SQL Server MVP
>> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
>> news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
>> SQL Server 2K database with size 117GB. I have dropped the all the
>> tables. So No user tables are there in the database.
>> Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase.
>> But its not reducing the size of the database.
>> When I take a backup, the backup size is coming close to 200MB only. Log
>> file is not big, its close to 100 MB in size.
>> I only have dbo permissions on this database, not sysadmin and I am not
>> sure about the initial size of the database.
>> How can I shrink this database?
>>|||DBCC commands are not showing any specific messages, It says the regular,
"completed, please contact admin in any error"
I am running the quries from the SNRDEV database.
sysdepends is the table with max rows, that's 283 rows. rest of the tables
are having less than 100 rows
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uvepewS2HHA.4476@.TK2MSFTNGP06.phx.gbl...
> Ok, so maybe some system table is taking up space?
> SELECT TOP 10
> table_or_view = OBJECT_NAME(id), rows
> FROM sysindexes
> WHERE indid IN (0,1)
> AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
> ORDER BY rows DESC;
> You said basically that shrinking doesn't work. But what *exactly* is the
> result of
> DBCC SHRINKFILER(SNRDEV_Data, 1)
> ?
> Of course, final silly question, you are running these queries from
> SNRDEV, right? And that is the database you are trying to shrink?
> --
> Aaron Bertrand
> SQL Server MVP
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
>> Here is the query results
>> name size
>> -- --
>> SNRDEV_Data 14712960
>> SNRDEV_Log 128000
>> (2 row(s) affected)
>> table_or_view rows
>> -- --
>> dtproperties 0
>> (1 row(s) affected)
>

Empty Database Not Shrinking

SQL Server 2K database with size 117GB. I have dropped the all the tables.
So No user tables are there in the database.
Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase. But
its not reducing the size of the database.
When I take a backup, the backup size is coming close to 200MB only. Log
file is not big, its close to 100 MB in size.
I only have dbo permissions on this database, not sysadmin and I am not sure
about the initial size of the database.
How can I shrink this database?
What do the following yield?
USE [your_database];
GO
SELECT name,size FROM sysfiles;
SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
ORDER BY rows DESC;
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
> SQL Server 2K database with size 117GB. I have dropped the all the tables.
> So No user tables are there in the database.
> Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase.
> But its not reducing the size of the database.
> When I take a backup, the backup size is coming close to 200MB only. Log
> file is not big, its close to 100 MB in size.
> I only have dbo permissions on this database, not sysadmin and I am not
> sure about the initial size of the database.
> How can I shrink this database?
|||Here is the query results
name size
-- --
SNRDEV_Data 14712960
SNRDEV_Log 128000
(2 row(s) affected)
table_or_view rows
-- --
dtproperties 0
(1 row(s) affected)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
> What do the following yield?
> USE [your_database];
> GO
> SELECT name,size FROM sysfiles;
> SELECT TOP 10
> table_or_view = OBJECT_NAME(id), rows
> FROM sysindexes
> WHERE indid IN (0,1)
> AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
> ORDER BY rows DESC;
> --
> Aaron Bertrand
> SQL Server MVP
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
>
|||Ok, so maybe some system table is taking up space?
SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
ORDER BY rows DESC;
You said basically that shrinking doesn't work. But what *exactly* is the
result of
DBCC SHRINKFILER(SNRDEV_Data, 1)
?
Of course, final silly question, you are running these queries from SNRDEV,
right? And that is the database you are trying to shrink?
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
> Here is the query results
> name size
> -- --
> SNRDEV_Data 14712960
> SNRDEV_Log 128000
> (2 row(s) affected)
> table_or_view rows
> -- --
> dtproperties 0
> (1 row(s) affected)
|||I don't think you will be able to shrink the data file beyond its minimum
configured size (see ALTER DATABASE).
Why don't you just drop the database and re-create it at a more appropriate
size?
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...[vbcol=seagreen]
> Here is the query results
> name size
> -- --
> SNRDEV_Data 14712960
> SNRDEV_Log 128000
> (2 row(s) affected)
> table_or_view rows
> -- --
> dtproperties 0
> (1 row(s) affected)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
|||DBCC commands are not showing any specific messages, It says the regular,
"completed, please contact admin in any error"
I am running the quries from the SNRDEV database.
sysdepends is the table with max rows, that's 283 rows. rest of the tables
are having less than 100 rows
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uvepewS2HHA.4476@.TK2MSFTNGP06.phx.gbl...
> Ok, so maybe some system table is taking up space?
> SELECT TOP 10
> table_or_view = OBJECT_NAME(id), rows
> FROM sysindexes
> WHERE indid IN (0,1)
> AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
> ORDER BY rows DESC;
> You said basically that shrinking doesn't work. But what *exactly* is the
> result of
> DBCC SHRINKFILER(SNRDEV_Data, 1)
> ?
> Of course, final silly question, you are running these queries from
> SNRDEV, right? And that is the database you are trying to shrink?
> --
> Aaron Bertrand
> SQL Server MVP
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
>

Tuesday, March 27, 2012

Encryption of Database.

Monday, March 26, 2012

encryption of column in sqlserver 2000

encryption of column in sqlserver 2000

encryption of column in sqlserver 2000

Wednesday, March 21, 2012

encrypting tables

Sunday, March 11, 2012

Encrypted data in tables

Friday, March 9, 2012

encrypt database --

encrypt credit card details within SQL 2000/2005

Encrpyt

Sunday, February 26, 2012

Enabling "not for replication" contraint on a mass level

Enable/Disable Trigger

Sunday, February 19, 2012

Enable or disable a relationship between 2 tables at runtime?

Friday, February 17, 2012

Empty Tables at subscriber without error messages with Transactional Replication

Empty table problem

Empty Subreport in Table

Empty Space between two tables

Empty Space between two tables

Wednesday, February 15, 2012

Empty Database Not Shrinking

Empty Database Not Shrinking

Empty Column

Blog Archive

About Me