data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is written
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is know
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive...encryption.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is writt
en
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is kn
ow
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

Data access advise

Hi
vs2005/sql server2005. I have created a simple winform app by dragging a
table on a winform. I have used stored procedures for data access. I have
the following questions;
1. Using the default code generated by vs2005 for data access, how can I
trap record insertion to set some field values before the record is
inserted?
2. The default data access works nicely for insert in the main table. I need
to insert a detailed record for every record inserted in the main table. How
and where do I implement this second insert?
3. If I type a value in 'Company' field on the winform, when record is saved
the underlying table contains that same value in every field that has word
"company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
that?
Thanks
RegardsYou may have better results posting to the *.windowsforms.databinding group.
"John S" <John@.nospam.infovis.co.uk> wrote in message
news:ujjYWH5rFHA.508@.TK2MSFTNGSA03.privatenews.microsoft.com...
> Hi
> vs2005/sql server2005. I have created a simple winform app by dragging a
> table on a winform. I have used stored procedures for data access. I have
> the following questions;
> 1. Using the default code generated by vs2005 for data access, how can I
> trap record insertion to set some field values before the record is
> inserted?
> 2. The default data access works nicely for insert in the main table. I
> need
> to insert a detailed record for every record inserted in the main table.
> How
> and where do I implement this second insert?
> 3. If I type a value in 'Company' field on the winform, when record is
> saved
> the underlying table contains that same value in every field that has word
> "company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
> that?
> Thanks
> Regards
>
>

Data access advise

Hi
vs2005/sql server2005. I have created a simple winform app by dragging a
table on a winform. I have used stored procedures for data access. I have
the following questions;
1. Using the default code generated by vs2005 for data access, how can I
trap record insertion to set some field values before the record is
inserted?
2. The default data access works nicely for insert in the main table. I need
to insert a detailed record for every record inserted in the main table. How
and where do I implement this second insert?
3. If I type a value in 'Company' field on the winform, when record is saved
the underlying table contains that same value in every field that has word
"company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
that?
Thanks
Regards
You may have better results posting to the *.windowsforms.databinding group.
"John S" <John@.nospam.infovis.co.uk> wrote in message
news:ujjYWH5rFHA.508@.TK2MSFTNGSA03.privatenews.mic rosoft.com...
> Hi
> vs2005/sql server2005. I have created a simple winform app by dragging a
> table on a winform. I have used stored procedures for data access. I have
> the following questions;
> 1. Using the default code generated by vs2005 for data access, how can I
> trap record insertion to set some field values before the record is
> inserted?
> 2. The default data access works nicely for insert in the main table. I
> need
> to insert a detailed record for every record inserted in the main table.
> How
> and where do I implement this second insert?
> 3. If I type a value in 'Company' field on the winform, when record is
> saved
> the underlying table contains that same value in every field that has word
> "company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
> that?
> Thanks
> Regards
>
>

Data access advise

Hi
vs2005/sql server2005. I have created a simple winform app by dragging a
table on a winform. I have used stored procedures for data access. I have
the following questions;
1. Using the default code generated by vs2005 for data access, how can I
trap record insertion to set some field values before the record is
inserted?
2. The default data access works nicely for insert in the main table. I need
to insert a detailed record for every record inserted in the main table. How
and where do I implement this second insert?
3. If I type a value in 'Company' field on the winform, when record is saved
the underlying table contains that same value in every field that has word
"company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
that?
Thanks
RegardsYou may have better results posting to the *.windowsforms.databinding group.
"John S" <John@.nospam.infovis.co.uk> wrote in message
news:ujjYWH5rFHA.508@.TK2MSFTNGSA03.privatenews.microsoft.com...
> Hi
> vs2005/sql server2005. I have created a simple winform app by dragging a
> table on a winform. I have used stored procedures for data access. I have
> the following questions;
> 1. Using the default code generated by vs2005 for data access, how can I
> trap record insertion to set some field values before the record is
> inserted?
> 2. The default data access works nicely for insert in the main table. I
> need
> to insert a detailed record for every record inserted in the main table.
> How
> and where do I implement this second insert?
> 3. If I type a value in 'Company' field on the winform, when record is
> saved
> the underlying table contains that same value in every field that has word
> "company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
> that?
> Thanks
> Regards
>
>

Data access advise

Hi
vs2005/sql server2005. I have created a simple winform app by dragging a
table on a winform. I have used stored procedures for data access. I have
the following questions;
1. Using the default code generated by vs2005 for data access, how can I
trap record insertion to set some field values before the record is
inserted?
2. The default data access works nicely for insert in the main table. I need
to insert a detailed record for every record inserted in the main table. How
and where do I implement this second insert?
3. If I type a value in 'Company' field on the winform, when record is saved
the underlying table contains that same value in every field that has word
"company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
that?
Thanks
RegardsYou may have better results posting to the *.windowsforms.databinding group.
"John S" <John@.nospam.infovis.co.uk> wrote in message
news:ujjYWH5rFHA.508@.TK2MSFTNGSA03.privatenews.microsoft.com...
> Hi
> vs2005/sql server2005. I have created a simple winform app by dragging a
> table on a winform. I have used stored procedures for data access. I have
> the following questions;
> 1. Using the default code generated by vs2005 for data access, how can I
> trap record insertion to set some field values before the record is
> inserted?
> 2. The default data access works nicely for insert in the main table. I
> need
> to insert a detailed record for every record inserted in the main table.
> How
> and where do I implement this second insert?
> 3. If I type a value in 'Company' field on the winform, when record is
> saved
> the underlying table contains that same value in every field that has word
> "company" in the fieldname such as CompanyType, CompanyAddress etc. Why is
> that?
> Thanks
> Regards
>
>

Data about Stored Procudre

How i get data about stored procedures

Data like stored procudres name

Name of parameters in store procudre .

And type of parameters in stored procedure.

Check this

select a.Name,b.Name as ParameterName,c.name as Datatype,b.Length From
(select *from sysobjects where xtype='p') a
inner join
Syscolumns b on a.id=b.id

inner join

Systypes c ON c.xtype=b.xtype

Madhu

|||

I found way to take detail

select * from INFORMATION_SCHEMA.PARAMATERS

cycling through results of a select statement

I am new to stored procedures and T-SQL so please stick with me. I have a table that holds information about companies. I am trying to write a stored procedure that when run will query that table and find out if there are more than one entry of that company. All company names in that table must be unique (they can only occur once), if they occur more than once I need to flag it for reporting.

So what is the best way to go about this? Essentially what i was thinking was doing a select * on the table and then going from the first entry to the last and at each entry running a select * from table where companyname = @.nameofcompany. @.nameofcompany would be the name for that entry. If the select statement revealed more than one entry then i would know there was a problem.

Like I said I am new and this is probably very simple but i need a little help getting started

thanksSELECT *
FROM myTable99 o
WHERE EXISTS ( SELECT Company_Name
FROM myTable99 i
WHERE o.Company_Name = i.Company_Name
GROUP BY Company_Name
HAVING COUNT(*) > 1)

But you wouldn't have to do that if you defined the table like

CREATE TABLE myTable99(Company_Name varchar(50) UNIQUE)

EDIT: Where in Jersey? And what school?|||I am originally from Vernon (Mountain Creek). Went to school at Stevens Institute of Technology in Hoboken, NJ.

I didn't create the dll for this database so i just loaded the schema by the .sql file. Right now I am handed an excel template and i wrote somce vb code to go through that excel file pull out the information i want and then write it to a text file delimited with "#" and then i load it into SQL server with a bulk load command. If the user sends me a template that already has duplicate entries in it and i try to load the data into SQL column that has a unique indentifier what will happen? Will it throw an error? If this is the case then it would probably be better to get the data in the database and then decided whether or not it is a duplicate.
Redefining the table seems like the simplest way to go but i don't want to break functionality in the process

thanks|||I just tried to add this code and it is complaining on the second line in reference to the o. Here is the error: Error 170: Line 2: incorrect syntax near 'o'. As I said i am new to stored procedures and T-SQL do i need to declare the o and i as variables somewhere?|||I didn't test the code, so you gave me a start...but the code does work...

Where are you running this from? Do you have query analyzer and the other sql server client toools?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Company_Name varchar(50))
GO

INSERT INTO myTable99(Company_Name)
SELECT 'Vernon Valley' UNION ALL
SELECT 'Mountain Creek' UNION ALL
SELECT 'Hidden Valley' UNION ALL
SELECT 'Campgaw' UNION ALL
SELECT 'Break Neck Road' UNION ALL
SELECT 'High Point' UNION ALL
SELECT 'Octogon Lounge' UNION ALL
SELECT 'Great Gorge' UNION ALL
SELECT 'Mountain Creek'
GO

SELECT *
FROM myTable99 o
WHERE EXISTS ( SELECT Company_Name
FROM myTable99 i
WHERE o.Company_Name = i.Company_Name
GROUP BY Company_Name
HAVING COUNT(*) > 1)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||sorry i am an a** i have an extra space floating in there. Man i am an idiot|||Hey...you're from Jersey...never apologize