Access databases using ADO.Net

Hi
I am using Visual Studio.Net 2003 and SQL 2000
Web applications run using the ASPNET user account.

I have to set up this account and grant it permissions before my Web application will have access to a SQL database.
How do I grant permissions ?.

There are two permissions in SQL Server the Database permissions in the database and the Server permissions under security in Enterprise manager. If you need more help post again. Hope this helps.

Accesing data from many applications.

Hi, I have a server running sql server 2000 sp3. In this sql server we
use windows authentication to allow users to use the applications that
were developed in house.
At this moment we have some users that are using excel to get data from
databases but we dont want they do that, because we cant control the
data they are getting.
We want to restrict that all users can get data only using the
applications that are developed in house.
Do you know if in sql server I can restrict wich applications can be
connected to sql server and wich not'
I need to continue using windows authentication.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***On Feb 24, 11:28 am, MariaGuzman <mar...@.devdex.com> wrote:
> Hi, I have a server running sql server 2000 sp3. In this sql server we
> use windows authentication to allow users to use the applications that
> were developed in house.
> At this moment we have some users that are using excel to get data from
> databases but we don=B4t want they do that, because we can=B4t control the
> data they are getting.
> We want to restrict that all users can get data only using the
> applications that are developed in house.
> Do you know if in sql server I can restrict wich applications can be
> connected to sql server and wich not'
> I need to continue using windows authentication.
> Thanks a lot for your help.
> *** Sent via Developersdexhttp://www.codecomments.com***
If a user has permission to connect they can.
See SQL Server 2005 Books Online topic:
Application Roles|||MariaGuzman (marisa@.devdex.com) writes:
> Hi, I have a server running sql server 2000 sp3. In this sql server we
> use windows authentication to allow users to use the applications that
> were developed in house.
> At this moment we have some users that are using excel to get data from
> databases but we dont want they do that, because we cant control the
> data they are getting.
> We want to restrict that all users can get data only using the
> applications that are developed in house.
> Do you know if in sql server I can restrict wich applications can be
> connected to sql server and wich not'
I assume here that your applications are not using stored procedures,
but generate SQL statements in the client. Because if the applications
are using stored procedures (with no dynamic SQL in them), the answer
would be trivial. Just revoke all direct table access. It's sufficient
that users have EXEC permissions on the stored procedure.
If your applications are not using stored procedures, it's of course
a major task to rewrite them. Using application roles as Steve suggested
is a possible solution. It still requires the application to be rewritten,
because once the user has logged in, the application must issue
"sp_setapprole". Note also that if you schemes that requires you
to know the name of the current database user, this will break with
application role.
Note also that application role with two-tier solution is not real
security. The password for the application must be hidden somewhere,
but whereever you hide it, a skilled user will be able to find it.
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

About COLUMNS_UPDATED and .Net Applications

Hello All,
I am using COLUMNS_UPDATED in my triggers to audit changes on my tables. I
test everything with Query Analyzer and everything looks perfect.
But when I test the triggers with the applications written by our developers
(using Visual Basic or C#) looks like these applications are sending UPDATEs
including all the fields and COLUMNS_UPDATED shows always all the columns of
the record even if only one field was updated. Am I missing something? Or
maybe some recommendation to our developers?
Thanks,
Benjamin Nevarez
SQL Server Database AdministratorCOLUMNS_UPDATED doesn't tell you what data has changed, only what
columns are referenced in an UPDATE. So even if the developers did
things differently, COLUMNS_UPDATED still may not give you what you
want.
Also, depending on how you handle the values returned by
COLUMNS_UPDATED you may experience other problems. The column ordinal
numbers aren't always updated in a consistent manner whenever the table
structure changes.
For these reasons I recommend you avoid COLUMNS_UPDATED. Identify
changed rows and columns by joining the INSERTED and DELETED tables
instead.
David Portas
SQL Server MVP
--|||Well, what I am doing is
1) Assign the field values from inserted and deleted tables to variables
like in
select @.inserted1 = field1, @.inserted2 = field2, ...
select @.deleted1 = field1, @.deleted2 = field2, ...
2) Manually compare the data like in
if (@.inserted1 = @.deleted1)
--do something
The problem here is that I need to do that for every field in every table.
Is there a faster way?
Thanks,
Ben
"David Portas" wrote:

> COLUMNS_UPDATED doesn't tell you what data has changed, only what
> columns are referenced in an UPDATE. So even if the developers did
> things differently, COLUMNS_UPDATED still may not give you what you
> want.
> Also, depending on how you handle the values returned by
> COLUMNS_UPDATED you may experience other problems. The column ordinal
> numbers aren't always updated in a consistent manner whenever the table
> structure changes.
> For these reasons I recommend you avoid COLUMNS_UPDATED. Identify
> changed rows and columns by joining the INSERTED and DELETED tables
> instead.
> --
> David Portas
> SQL Server MVP
> --
>|||On Thu, 3 Mar 2005 10:27:02 -0800, Benjamin Nevarez wrote:

>Well, what I am doing is
>1) Assign the field values from inserted and deleted tables to variables
>like in
> select @.inserted1 = field1, @.inserted2 = field2, ...
> select @.deleted1 = field1, @.deleted2 = field2, ...
>2) Manually compare the data like in
> if (@.inserted1 = @.deleted1)
> --do something
>The problem here is that I need to do that for every field in every table.
Hi Ben,
No, that's not the problem. The main problem is that your trigger will
break on any multi-row update. A trigger gets fired once per execution
of an UPDATE statement, not once per row affected, so the trigger code
should be able to handle one row affected as well as no rows affected or
1000 rows affected.
To find out what data actually changed, you must:
a) Take steps to ensure that the primary key columns of your table are
never changed in an UPDATE statement - these columns are the only way to
match rows from the inserted and deleted pseudo-tables;
b) Use the following FROM and WHERE clause as part of a query that
handles the rows in which the value for Column01 actually changed
(assuming the primary key consists of columns KeyCol01 and KeyCol02)
FROM inserted AS i
INNER JOIN deleted AS d
ON d.KeyCol01 = i.KeyCol01
AND d.KeyCol02 = i.KeyCol02
WHERE i.Column01 <> d.Column01
c) If Column01 allows NULLS, change the WHERE clause to
WHERE COALESCE (NULLIF (i.Column01, d.Column01),
NULLIF (d.Column01, i.Column01)) <> NULL
Of course, you still have to do this for all relevant columns. Yes,
programming sometimes involves lots of tedium.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually I wrote one solution with a simple join ... and NULLIF. This does
everything in one step. The insert is something like this
insert into audittable
select i.pk, nullif(i.col1, d.col1), nullif(i.col2, d.col2), nullif(i.col3,
d.col3), system_user, getdate()
from inserted i inner join deleted d on i.pk = d.pk
Thanks All,
Ben
"Hugo Kornelis" wrote:

> On Thu, 3 Mar 2005 10:27:02 -0800, Benjamin Nevarez wrote:
>
> Hi Ben,
> No, that's not the problem. The main problem is that your trigger will
> break on any multi-row update. A trigger gets fired once per execution
> of an UPDATE statement, not once per row affected, so the trigger code
> should be able to handle one row affected as well as no rows affected or
> 1000 rows affected.
> To find out what data actually changed, you must:
> a) Take steps to ensure that the primary key columns of your table are
> never changed in an UPDATE statement - these columns are the only way to
> match rows from the inserted and deleted pseudo-tables;
> b) Use the following FROM and WHERE clause as part of a query that
> handles the rows in which the value for Column01 actually changed
> (assuming the primary key consists of columns KeyCol01 and KeyCol02)
> FROM inserted AS i
> INNER JOIN deleted AS d
> ON d.KeyCol01 = i.KeyCol01
> AND d.KeyCol02 = i.KeyCol02
> WHERE i.Column01 <> d.Column01
> c) If Column01 allows NULLS, change the WHERE clause to
> WHERE COALESCE (NULLIF (i.Column01, d.Column01),
> NULLIF (d.Column01, i.Column01)) <> NULL
>
> Of course, you still have to do this for all relevant columns. Yes,
> programming sometimes involves lots of tedium.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

About building international applications in dotnet

Hi,

I am developing an application using C#.NET that accesses a remote database that that contains japanese characters.

My sample querry is strCommand = "SELECT * FROM table WHERE prodname = 'ＣＲ新海物語スペシャルＭ８'";

The result of querry displayed in the binded datagrid also contains other rows that has this prodname:

ＣＲＦ湯けむり紀行ＳＦ－Ｔ
ＣＲぱちんこハイサイＶＲ１
ＣＲホワイトエンジェルＦＳ

etc.

Thanks in advance for your help.

You forgot to ask a question.
|||

Hi,

I am developing an application using C#.NET that accesses a remote database that that contains japanese characters.

My sample querry is strCommand = "SELECT * FROM table WHERE prodname = 'ＣＲ新海物語スペシャルＭ８'";

The result of the querry above that is displayed in the binded datagrid also contains other rows that has this prodname:

ＣＲＦ湯けむり紀行ＳＦ－Ｔ
ＣＲぱちんこハイサイＶＲ１
ＣＲホワイトエンジェルＦＳ

etc.

Why does the command yields other records where the prodname field contains the 'CR' thing?

Sample querry result in a datagrid:

field1 field2 field3 prodname

a as er ＣＲ新海物語スペシャルＭ８

w aa ww ＣＲＦ湯けむり紀行ＳＦ－Ｔ

ss dd ee ＣＲぱちんこハイサイＶＲ1

sdds ddd ee ＣＲ新海物語スペシャルＭ８

rf d5r wwe ＣＲぱちんこハイサイＶＲ１

Does this strange and redundant output has something to do with the Japanese characters contained in some fields

of the table?

Thanks in advance for your help.

|||Try with the correct nvarchar syntax:

SELECT * FROM table WHERE prodname = N'ＣＲ新海物語スペシャルＭ８'