Print and Raiserror

I wanted to give user message from SQL Stored Procedure.
I tried to use PRINT, but it does not go to my MS Access front end
application
I tried to use 'RAISERROR' to send out message, but the message has SQL
server message to my MS Access.
Are there any way to sent message to my front end application user form
stored procedure?
Any information is great appreciated.
Souris,souris wrote:
> I wanted to give user message from SQL Stored Procedure.
> I tried to use PRINT, but it does not go to my MS Access front end
> application
> I tried to use 'RAISERROR' to send out message, but the message has
> SQL server message to my MS Access.
> Are there any way to sent message to my front end application user
> form stored procedure?
> Any information is great appreciated.
> Souris,
You can use RAISERROR. Please post you code to generate the error and
exactly the problem you are seeing with using it. I assume you want to
notify the client app about an error condition and not just a normal
message. Is that correct?
For example, you could use:
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
or
RAISERROR ('The level for job_id:%d should be between %d and %d.', 16,
1, @.@.JOB_ID, @.@.MIN_LVL, @.@.MAX_LVL)
David Gugick
Imceda Software
www.imceda.com|||Thanks for the message.
Here is my SQL
BEGIN
RAISERROR('YOU HAVE UPDATE DATA TODAY, IF YOU THINK YOU NEED UPLOAD ONE
MORE TIME PLEASE SEE YOUR ADMINISTRATOR, THANKS', 16, -1)
RETURN
END
The message comes following form my Access front end
Microsoft Access
ODBC - call failer
[Microsoft][ODBC SQL server Driver][SQL Driver]YOU AHVE UPDATE DATA TODAY,
OF YOU THINK YOU NEED UPLOAD ONE MORE TIME PLEASE SEE YOUR ADMINISTARTOR,
THANKS(#50000) [Microsoft][ODBC driver SQL Server][SQL server] Violation of
PRIMARY KEY constaint 'PK_TSC_CMPLD_CALLOUTS', Cannot insert duplicate key
in object 'TSC_CMPLD_CALLOUTS'. (#2627)Microsoft][ODBC SQL server
Driver][SQL Driver]The statement has been terminated.(#3621)[ODBC driver SQL
Server][SQL server] Violation of PRIMARY KEY constaint
'PK_TSC_CMPLD_CALLOUTS', Cannot insert duplicate key in object
'TSC_CMPLD_CALLOUTS'. (#2627)Microsoft][ODBC SQL server Driver][SQL
Driver]The statement has been terminated.(#3621)
How can I give the user message from my stored procedure?
Why my Access front give so much message to user?
Any information is great appreciated.
Thanks again,
Souris,
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23OSTKNfJFHA.1308@.TK2MSFTNGP15.phx.gbl...
> souris wrote:
> You can use RAISERROR. Please post you code to generate the error and
> exactly the problem you are seeing with using it. I assume you want to
> notify the client app about an error condition and not just a normal
> message. Is that correct?
>
> For example, you could use:
> RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
> or
> RAISERROR ('The level for job_id:%d should be between %d and %d.', 16, 1,
> @.@.JOB_ID, @.@.MIN_LVL, @.@.MAX_LVL)
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||souris wrote:
> Thanks for the message.
> Here is my SQL
> BEGIN
> RAISERROR('YOU HAVE UPDATE DATA TODAY, IF YOU THINK YOU NEED UPLOAD
> ONE MORE TIME PLEASE SEE YOUR ADMINISTRATOR, THANKS', 16, -1)
> RETURN
> END
Sounds like you are generating a lot of errors in whatever SQL you are
running. Try running the SQL with the same parameter values from Query
Analyzer or some other query tool (probably can use Access as well) and
make sure it doesn't generate any errors.
It looks like you are using RAISERROR to return a message. RAISERROR is
for raising errors only, not for messages. I would have the application
display the message on it's own, and not have it generated from the
database.
David Gugick
Imceda Software
www.imceda.com|||Thanks for the message.
My App needs download once and only once per day
The date filed is a part of my primary key.
I wanted to use RAISERROR to raise error and also let user know what
happens.
1. If I use RAISERROR the SQL statement should not continue executing the
rest of SQL, but it seems continues running to violate primary key rule.
2. If I need to write message on front end application then there must be a
way to get rid the [Microsoft ODBC][SQL Server] message from RAISERROR.
Your information is great appreciated
Souris,
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OTuFBvyJFHA.3552@.TK2MSFTNGP12.phx.gbl...
> souris wrote:
> Sounds like you are generating a lot of errors in whatever SQL you are
> running. Try running the SQL with the same parameter values from Query
> Analyzer or some other query tool (probably can use Access as well) and
> make sure it doesn't generate any errors.
> It looks like you are using RAISERROR to return a message. RAISERROR is
> for raising errors only, not for messages. I would have the application
> display the message on it's own, and not have it generated from the
> database.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||souris wrote:
> Thanks for the message.
> My App needs download once and only once per day
> The date filed is a part of my primary key.
> I wanted to use RAISERROR to raise error and also let user know what
> happens.
> 1. If I use RAISERROR the SQL statement should not continue executing
> the rest of SQL, but it seems continues running to violate primary
> key rule.
> 2. If I need to write message on front end application then there
> must be a way to get rid the [Microsoft ODBC][SQL Server] message
> from RAISERROR.
It looks like the snippet of code you posted should work. You are using
raiserror in a block with a return statement (you should add a return
code). I don't see how the rest of the SQL is being executed unless it
occurred before the raiserror. Can you post the entire procedure?
David Gugick
Imceda Software
www.imceda.com

Primary Key problem in db transfer Access to SQL server

I have an application initially made with Cold Fusion and Access.
I have transferred the db from Access to SQL Server and all the code works fine except for the INSERT statement. SQL Server does not generate an automatic primary key... 'o( any ideas?what was an autonumber in access is an IDENTITY column in sql/server

if you are doing INSERT statements, you do not mention the identity column (same as in access)

if you need to preserve the numbers that existed in the access tables, you can do that (look up the bcp utility and BULK INSERT KEEPIDENTITY in Books Online)

rudy
http://rudy.ca/|||Thanks!

primary key in the network

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks.

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks a lot.

|||

Are you inserting explicit values for the identity column, or letting SQL Server autonumber them for you?

Thanks,

John

|||

SQL Server autonumber them for me.

one more easy question:

What's the kind of the network SQL server works with? Is it peer to peer ( workgroup ) or server (domain ) network?

thanks

|||

I am still unsure what is going on in your first problem, if you could provide a code sample or more information it would help a lot. As for your second question, what do you mean by what kind of network does it work with? Are you asking about windows authentication for SQL Server? The architecture is client server, not p2p but I am unsure why you have broken these two communication archetypes into workgroup/domain groupings so I am not sure how to answer that question.

Thanks,

John

|||The identity column is maintained by SQL Server only, until you specify the Identity Insert to ON, to insert values for yourself. My next step if I were you would be to sniff the commands that are executed against the database using SQL Server profiler. I guess that the error message either comes from anothert column having A UNIQUE constraint build upon it or someone uses the IDENTITY INSERT command to insert values manually.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--sql

primary key in datarow after update works in access not in sql server

Heys

a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key
of a datarow you had just inserted into the database.

After a long set of trial and error i came up with the following:

- add the tablemappings of a table
- call the dataadapte.fillschema method

then after inserting a new row into the database the primary key gets filled in automatically!

now thing is

i was hoping to duplicate this in sql server

but it doesn't seem to work at all

so after i insert a row into my datatable
and update it
the row is in the database
but in vb the datarow primary key is not filled in!
anyone have an idea?

prefereabely one that does not resort to stored procedures with return parameters etc

thx a million in advance!Try the solution in this post to get started. Hope this helps.
http://forums.asp.net/967888/ShowPost.aspx

Primary Key Help

Hi am fairly new to VS and very new to Access. I am managing somewhat. I have access to the database, I get inside my tables and I can change what needs to be changed. However I want to know how to work with primary keys. As of right now I am using 4 tables in my app. If i run my program all of the tables get filled in just fine. However if I run it again, none of the tables get updated because I have a primary key on a few of the tables columns.

Dim ADOConn As ADODB.Connection

Dim recSet As ADODB.Recordset

ADOConn = New ADODB.Connection

recSet = New ADODB.Recordset

ADOConn.Provider = "Microsoft.Jet.OLEDB.4.0"

ADOConn.ConnectionString = "C:\Documents and Settings\christopher.cornell\Desktop\MyDB.mdb"

ADOConn.Open()

recSet.Open("select * from Table1", ADOConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

recSet.AddNew("Column1", textbox1.text) 'Primary key on this. When i try to add the same thing over

'no other tables will get updated.

recSet.Update()

recSet.Close()

The first time through will be just fine, however if I run it again it wont update the rest of my tables. It seems to run everything (stepping through the debugger) but it is not reflected in the tables. I hope what I am trying to do makes sense.

Hi Chris,

The issue here is that a primary key is what is referred to as a unique constraint. There can not be any duplicate values for the primary key column. The primary key is defined in this manner so that the database engine knows how to locate the record you are looking for. Think of this as the row name for a table. Very similar to an excel spreadsheet. If I were to give you a spreadsheet with no row numbers on it and asked you to find a specific row you would have to scan all the rows in the sheet to find what I am asking you for. If you have a primary key (usually a number) and I ask you for row #456 and it is garunteed unique you can find it and once you found it you can stop looking because you know there is no other row #456.

So, you need to make sure your values you insert for your primary key are unique. Often times you will need to define multiple columns as your primary key if a combination of values makes a row or record unique.

Hope this helps you.

-David Sandor

|||

OK, that makes sense. But for instance lets say that I only want to have this one specific entry in my table. I am parsing a file to get the info for my table. So if I come to this value again and try to insert it, it obviously will not be allowed. However after it trys that, nothing else will be inserted into any of the other tables. So for table1 I have 2 columns. The first column has a primary key. Then I have table 2 with 4 columns, I do not have a primary key because I do not mind that records are the same. But when I parse my file for insertion into the tables, it will try the first table and since the entry is already in there it will not put anything else in the second table. It seems to finish executing the code however. I was thinking I may have needed an if that checks if its alredy in there, but I could not get that to work either.

I am guess this problem is caused by the primary key thing, because when I take it off, everything works fine. Thank you very much for the reply.

Chris

I still need help with the above issue. However I think that If I get help with this problem I will be able to figure it out myself. I am trying to find something in the database. This is what I am doing.

recSet.Open("select * from Table", ADOConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

recSet.Find("[Name] = '" & temp(i) & "'", 0, ADODB.SearchDirectionEnum.adSearchForward, 1)

If Not recSet.Fields("Name").Value = temp(i) Then

recSet.AddNew("Name", Apps(i))

recSet.Update()

recSet.Close()

End If

This is how I have seen to do things on other sites however I can not get this to work. When I try to do the find, all I am getting is the very first entry so I assume I am doing something wrong. Any help would be greatly appreciated. Thanks.

Primary key allows null?

XP Pro / Access 2003 Project / SQL Server 2000 Backend
I have a table that I have assigned multiple fields as the primary key(s).
There are about 6 fields that make up the primary key for this table. When
entering a new record into the table the primary key forces me to enter all
the data fields for the primary key. BUT.. if I were to copy a record and
remove the data from one of the PK fields, SQL Server accepts this. How is
this possible? I have this field designated as PK and it cannot accept a
null, but it does. Any help on this would be much appreciated.Mark wrote:
> XP Pro / Access 2003 Project / SQL Server 2000 Backend
> I have a table that I have assigned multiple fields as the primary key(s).
> There are about 6 fields that make up the primary key for this table. Whe
n
> entering a new record into the table the primary key forces me to enter al
l
> the data fields for the primary key. BUT.. if I were to copy a record an
d
> remove the data from one of the PK fields, SQL Server accepts this. How i
s
> this possible? I have this field designated as PK and it cannot accept a
> null, but it does. Any help on this would be much appreciated.
Depends what you mean by "remove all the data". If it's a string then
it can be empty. An empty string isn't null. If it's a numeric then it
can be zero. Did you run a query to check what the value *really* is in
the table rather than just look at what you are shown in a grid
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
--|||> this possible? I have this field designated as PK and it cannot accept a
> null, but it does. Any help on this would be much appreciated.
Are you sure? Are you certain that a unique constraint is not being used as
a PK (which will allow nulls)? Below is a simple proof.
set nocount on
-- this will fail
create table #test (id1 int not null, id2 int null, other_junk varchar(20)
not null,
constraint pkx primary key (id1, id2) )
go
-- this will succeed
create table #test (id1 int not null, id2 int null, other_junk varchar(20)
not null,
constraint pkx unique (id1, id2) )
go
-- 1 of 2 will succeed
insert #test (id1, id2, other_junk) values (1, 1, 'test')
insert #test (id1, id2, other_junk) values (1, 1, 'test')
go
-- all will succeed
insert #test (id1, id2, other_junk) values (1, 2, 'test')
insert #test (id1, id2, other_junk) values (2, 1, 'test')
insert #test (id1, id2, other_junk) values (1, null, 'test')
go
-- this will fail
insert #test (id1, id2, other_junk) values (1, null, 'test')
go
-- this will succeed
update #test set id2 = null where id1 = 2 and id2 = 1
go
-- final result
select * from #test
go
drop table #test
go

Primary key allows null?

XP Pro / Access 2003 Project / SQL Server 2000 Backend
I have a table that I have assigned multiple fields as the primary key(s).
There are about 6 fields that make up the primary key for this table. When
entering a new record into the table the primary key forces me to enter all
the data fields for the primary key. BUT.. if I were to copy a record and
remove the data from one of the PK fields, SQL Server accepts this. How is
this possible? I have this field designated as PK and it cannot accept a
null, but it does. Any help on this would be much appreciated.Mark wrote:
> XP Pro / Access 2003 Project / SQL Server 2000 Backend
> I have a table that I have assigned multiple fields as the primary key(s).
> There are about 6 fields that make up the primary key for this table. When
> entering a new record into the table the primary key forces me to enter all
> the data fields for the primary key. BUT.. if I were to copy a record and
> remove the data from one of the PK fields, SQL Server accepts this. How is
> this possible? I have this field designated as PK and it cannot accept a
> null, but it does. Any help on this would be much appreciated.
Depends what you mean by "remove all the data". If it's a string then
it can be empty. An empty string isn't null. If it's a numeric then it
can be zero. Did you run a query to check what the value *really* is in
the table rather than just look at what you are shown in a grid
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
--|||> this possible? I have this field designated as PK and it cannot accept a
> null, but it does. Any help on this would be much appreciated.
Are you sure? Are you certain that a unique constraint is not being used as
a PK (which will allow nulls)? Below is a simple proof.
set nocount on
-- this will fail
create table #test (id1 int not null, id2 int null, other_junk varchar(20)
not null,
constraint pkx primary key (id1, id2) )
go
-- this will succeed
create table #test (id1 int not null, id2 int null, other_junk varchar(20)
not null,
constraint pkx unique (id1, id2) )
go
-- 1 of 2 will succeed
insert #test (id1, id2, other_junk) values (1, 1, 'test')
insert #test (id1, id2, other_junk) values (1, 1, 'test')
go
-- all will succeed
insert #test (id1, id2, other_junk) values (1, 2, 'test')
insert #test (id1, id2, other_junk) values (2, 1, 'test')
insert #test (id1, id2, other_junk) values (1, null, 'test')
go
-- this will fail
insert #test (id1, id2, other_junk) values (1, null, 'test')
go
-- this will succeed
update #test set id2 = null where id1 = 2 and id2 = 1
go
-- final result
select * from #test
go
drop table #test
go

Primary Key / Referntial Integrity Question

Hi,

I am supporting an application that was converted from ACCESS to SQL
Server 2000. My question focuses on two particuliar tables. The
parent table has 14000 rows while child table has over 9 million rows.
Referential integrity is set up between the two tables. The parent
table has a composite primary key of:
CustomerId (int)
LocationId (int)
ProductId (int)

The child table has a composite primary key of:
CustomerId (int)
LocationId (int)
ProductId (int)
InvTime (datetime)

A constraint between the parent and child table consists of:
CustomerId
LocationId
TankId

I have been asked to write an application that will allow users to
update the Customerid and LocationId columns of the parent table. My
program works fine when you update just one parent record but if you
try and update 30 parent records I get a timeout error. I believe the
bottleneck is the constraint between the parent and child tables.
Outside of re-designing the tables (NOTE: I was not the original
DBA/Developer), does anyone have any suggestions I can do to improve
performance for my app?

ThanksRodney King () writes:
> I am supporting an application that was converted from ACCESS to SQL
> Server 2000. My question focuses on two particuliar tables. The
> parent table has 14000 rows while child table has over 9 million rows.
> Referential integrity is set up between the two tables. The parent
> table has a composite primary key of:
> CustomerId (int)
> LocationId (int)
> ProductId (int)
> The child table has a composite primary key of:
> CustomerId (int)
> LocationId (int)
> ProductId (int)
> InvTime (datetime)
> A constraint between the parent and child table consists of:
> CustomerId
> LocationId
> TankId
> I have been asked to write an application that will allow users to
> update the Customerid and LocationId columns of the parent table. My
> program works fine when you update just one parent record but if you
> try and update 30 parent records I get a timeout error. I believe the
> bottleneck is the constraint between the parent and child tables.
> Outside of re-designing the tables (NOTE: I was not the original
> DBA/Developer), does anyone have any suggestions I can do to improve
> performance for my app?

I assume that the referential integrity is set up with ON UPDATE CASCADE?

First all, are there any triggers on the tables? In such case, what
do they do?

Next, what are the indexes on the tables? Obviosly the PKs are indexes,
but are the clustered or non-clustered?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for responding.
1) To answer your questions I have full Delete/Update cascade on my
constraint.

2) There are no DB triggers and here are the indexes on my tables:

3) Indexes are as follows:
Master table:
Primary Key, Unique, Nonclustered index on:
CustomerId,LocationId,ProductId

Child table:
Nonclustered index on:
CustomerId,LocationId,ProductId

Nonclustered index on:
MinNumber

Primary Key, Unique, Nonclustered index on:
ProductId,InvTime

When I run the following query in SQL Query Analyzer:

Update MasterTable set Customerid=23,LocationId=34 where CustomerId=44
and LocationId=788 and ProductId=91022

My Explain plan tells me that a full table scan would be performed
against my child table. However, if I perform the same statement
against my Child table, the Explain plan uses my Nonclustered index of
CustomerId,LocationId and ProductId

Can you tell me what's going on?

Thanks
On Wed, 8 Dec 2004 20:52:38 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Rodney King () writes:
>> I am supporting an application that was converted from ACCESS to SQL
>> Server 2000. My question focuses on two particuliar tables. The
>> parent table has 14000 rows while child table has over 9 million rows.
>> Referential integrity is set up between the two tables. The parent
>> table has a composite primary key of:
>> CustomerId (int)
>> LocationId (int)
>> ProductId (int)
>>
>> The child table has a composite primary key of:
>> CustomerId (int)
>> LocationId (int)
>> ProductId (int)
>> InvTime (datetime)
>>
>> A constraint between the parent and child table consists of:
>> CustomerId
>> LocationId
>> TankId
>>
>> I have been asked to write an application that will allow users to
>> update the Customerid and LocationId columns of the parent table. My
>> program works fine when you update just one parent record but if you
>> try and update 30 parent records I get a timeout error. I believe the
>> bottleneck is the constraint between the parent and child tables.
>> Outside of re-designing the tables (NOTE: I was not the original
>> DBA/Developer), does anyone have any suggestions I can do to improve
>> performance for my app?
>I assume that the referential integrity is set up with ON UPDATE CASCADE?
>First all, are there any triggers on the tables? In such case, what
>do they do?
>Next, what are the indexes on the tables? Obviosly the PKs are indexes,
>but are the clustered or non-clustered?|||Rodney King () writes:
> Thanks for responding.
> 1) To answer your questions I have full Delete/Update cascade on my
> constraint.
> 2) There are no DB triggers and here are the indexes on my tables:
> 3) Indexes are as follows:
> Master table:
> Primary Key, Unique, Nonclustered index on:
> CustomerId,LocationId,ProductId
> Child table:
> Nonclustered index on:
> CustomerId,LocationId,ProductId
> Nonclustered index on:
> MinNumber
> Primary Key, Unique, Nonclustered index on:
> ProductId,InvTime

Do I read this right that there are no clustered indexes? While an update
to the clustered index would be more costly, locating the rows will be
faster.

The problem now is that the optimizer estimates that using the non-clustered
index on the table will be more expensive than a table scan. This is not
that strange as it sounds. If you need to access 50% of the rows in a
table, this is lot more expensive to do through a non-clustered index
than a table scan. (Because you will need to access many pages more
than once.) The optimizer therefor tends to be somewhat conservative
in its use of nonclustered indexes.

If you don't want to change the index to a clustered index, an UPDATE
STATISTICS WITH FULLSCAN on the tables may help, but I would not really
count on it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Primary Key - Multiple Columns

Friends,
In Access I can set Primary Keys on two columns so that, together, those two
columns comprise a Primary Key.
Is there a way, through T-SQL, to define two or more columns as the Primary
Key on a table? For example, it may be that the [sku] column contains
duplicate values and the [salesman] column contains duplicate values, but if
I GROUP BY [sku] and [salesman] then those two columns taken together contain
no duplicate values and can act as a Primary Key (i.e., there are no records
that contain the same [sku] AND [salesman]. Can I make two columns the
Primary Key?
Thanks for your help ...
Bill MorganIf you look in the documentation (Books Online) under
"PRIMARY KEY", you'll find the article "Creating and Modifying
PRIMARY KEY Constraints", which sort of shows how to do this
when you create the table and also how to do this after the table
is created.
But it's not easy to dig through the entire CREATE/ALTER TABLE
syntax to see how to do it, so...
-- When you create the table:
CREATE TABLE T (
sku int not null,
salesman int not null,
other varchar(20) not null,
primary key (sku,salesman)
)
-- later (the columns must be NOT NULL)
ALTER TABLE T
ADD CONSTRAINT pk_you_pick_a_name_for_the_primary_key_c
onstraint
PRIMARY KEY (sku, salesman)
If you do not have Books Online installed, download the current
version here:
http://www.microsoft.com/sql/techin.../2000/books.asp
Steve Kass
Drew University
bill_morgan_3333 wrote:

>Friends,
>In Access I can set Primary Keys on two columns so that, together, those tw
o
>columns comprise a Primary Key.
>Is there a way, through T-SQL, to define two or more columns as the Primary
>Key on a table? For example, it may be that the [sku] column contains
>duplicate values and the [salesman] column contains duplicate values, but if
>I GROUP BY [sku] and [salesman] then those two columns taken together contain
>no duplicate values and can act as a Primary Key (i.e., there are no record
s
>that contain the same [sku] AND [salesman]. Can I make two columns the
>Primary Key?
>Thanks for your help ...
>Bill Morgan
>
>|||Steve,
Perfectly clear. Thanks for your assistance ...
"Steve Kass" wrote:

> If you look in the documentation (Books Online) under
> "PRIMARY KEY", you'll find the article "Creating and Modifying
> PRIMARY KEY Constraints", which sort of shows how to do this
> when you create the table and also how to do this after the table
> is created.
> But it's not easy to dig through the entire CREATE/ALTER TABLE
> syntax to see how to do it, so...
> -- When you create the table:
> CREATE TABLE T (
> sku int not null,
> salesman int not null,
> other varchar(20) not null,
> primary key (sku,salesman)
> )
> -- later (the columns must be NOT NULL)
> ALTER TABLE T
> ADD CONSTRAINT pk_you_pick_a_name_for_the_primary_key_c
onstraint
> PRIMARY KEY (sku, salesman)
> If you do not have Books Online installed, download the current
> version here:
> http://www.microsoft.com/sql/techin.../2000/books.asp
> Steve Kass
> Drew University
> bill_morgan_3333 wrote:
>
>

primary key

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks.

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks a lot.

|||

Are you inserting explicit values for the identity column, or letting SQL Server autonumber them for you?

Thanks,

John

|||

SQL Server autonumber them for me.

one more easy question:

What's the kind of the network SQL server works with? Is it peer to peer ( workgroup ) or server (domain ) network?

thanks

|||

I am still unsure what is going on in your first problem, if you could provide a code sample or more information it would help a lot. As for your second question, what do you mean by what kind of network does it work with? Are you asking about windows authentication for SQL Server? The architecture is client server, not p2p but I am unsure why you have broken these two communication archetypes into workgroup/domain groupings so I am not sure how to answer that question.

Thanks,

John

|||The identity column is maintained by SQL Server only, until you specify the Identity Insert to ON, to insert values for yourself. My next step if I were you would be to sniff the commands that are executed against the database using SQL Server profiler. I guess that the error message either comes from anothert column having A UNIQUE constraint build upon it or someone uses the IDENTITY INSERT command to insert values manually.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

primary key

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks.

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks a lot.

|||

Are you inserting explicit values for the identity column, or letting SQL Server autonumber them for you?

Thanks,

John

|||

SQL Server autonumber them for me.

one more easy question:

What's the kind of the network SQL server works with? Is it peer to peer ( workgroup ) or server (domain ) network?

thanks

|||

I am still unsure what is going on in your first problem, if you could provide a code sample or more information it would help a lot. As for your second question, what do you mean by what kind of network does it work with? Are you asking about windows authentication for SQL Server? The architecture is client server, not p2p but I am unsure why you have broken these two communication archetypes into workgroup/domain groupings so I am not sure how to answer that question.

Thanks,

John

|||The identity column is maintained by SQL Server only, until you specify the Identity Insert to ON, to insert values for yourself. My next step if I were you would be to sniff the commands that are executed against the database using SQL Server profiler. I guess that the error message either comes from anothert column having A UNIQUE constraint build upon it or someone uses the IDENTITY INSERT command to insert values manually.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

primary key

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks.

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks a lot.

|||

Are you inserting explicit values for the identity column, or letting SQL Server autonumber them for you?

Thanks,

John

|||

SQL Server autonumber them for me.

one more easy question:

What's the kind of the network SQL server works with? Is it peer to peer ( workgroup ) or server (domain ) network?

thanks

|||

I am still unsure what is going on in your first problem, if you could provide a code sample or more information it would help a lot. As for your second question, what do you mean by what kind of network does it work with? Are you asking about windows authentication for SQL Server? The architecture is client server, not p2p but I am unsure why you have broken these two communication archetypes into workgroup/domain groupings so I am not sure how to answer that question.

Thanks,

John

|||The identity column is maintained by SQL Server only, until you specify the Identity Insert to ON, to insert values for yourself. My next step if I were you would be to sniff the commands that are executed against the database using SQL Server profiler. I guess that the error message either comes from anothert column having A UNIQUE constraint build upon it or someone uses the IDENTITY INSERT command to insert values manually.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

primary key

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks.

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks a lot.

|||

Are you inserting explicit values for the identity column, or letting SQL Server autonumber them for you?

Thanks,

John

|||

SQL Server autonumber them for me.

one more easy question:

What's the kind of the network SQL server works with? Is it peer to peer ( workgroup ) or server (domain ) network?

thanks

|||

I am still unsure what is going on in your first problem, if you could provide a code sample or more information it would help a lot. As for your second question, what do you mean by what kind of network does it work with? Are you asking about windows authentication for SQL Server? The architecture is client server, not p2p but I am unsure why you have broken these two communication archetypes into workgroup/domain groupings so I am not sure how to answer that question.

Thanks,

John

|||The identity column is maintained by SQL Server only, until you specify the Identity Insert to ON, to insert values for yourself. My next step if I were you would be to sniff the commands that are executed against the database using SQL Server profiler. I guess that the error message either comes from anothert column having A UNIQUE constraint build upon it or someone uses the IDENTITY INSERT command to insert values manually.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

Primary Key

In a recent course on database programming using Microsoft Access 2002. I noticed that the text entitiled New Perspectives Microsoft Access 2002 stated that a primary key could only be used once per table. But If I am not mistaken could one use the select key to select more than one primary key within a table.If I am not taking ur sentance wrongly.
There is no limitation on select statement. But defining PK is for uniquess enforcement.|||In SQL Server or MS Access, only one Primary Key can be defined on a table, although this Primary Key may be a compound key consisting of more than one column.

A SELECT list can contain as many columns as you wish, regardless of whether they are primary keys.|||you can have only one primary key per table

that part is absolutely true

your comment "could one use the select key to select more than one primary key within a table" is slightly inaccurate, but you are on the right track

yes, you can, in the Design View for the table, select more than one column and make them the primary key

there can be only one primary key, but it may be a composite key, consisting of multiple colulmns|||Not the way the definition works... There should be one or more Candidate Keys (a column or columns that uniquely identify each row within the table). One of those Candidate Keys becomes the Primary Key just because the data modeler said so, by definition all of the rest of the Candidate Keys become Alternate Keys.

-PatP|||Huh? What are you reading? And what's most important, - what are you smoking? And can I have one?|||Originally posted by rdjabarov
what are you smoking? And can I have one? I don't smoke, so you'd better have two, they're small!

-PatP|||Pat and r937,

First of all thank you for your replies and I did do this selection in the design view while having the shift button selected.|||Ah-ha! I was thinking from a data modeler perspective. You were asking from an Access GUI user perspective. Fundamental communication error there!

Yes, Access allows you to select more than one column to become part of the primary key. The columns don't even have to be next to each other, you can use the first, third, fifth, and tenth columns if you like.

-PatP

Preview/Print/Save reports

Hi

I have done many Reports using SQL 2005 Reporting Services and I can access them by:

http://localhost/Reports

In my ASP.NET application, I have 3 buttons:

Preview, Print and Save

If the user clicks on Preview the report is displayed

if the user clicks on Print,the report is sent automatically to the printer

if the user clicks on Save,the report is Exported in Pdf format to C:/

Can anyone give me some tutorials on how to manage Reports(.rdc) from ASP.NET

Hi,

From your description, it seems that you are wondering how to embed your report in ASP.Net application, right?

Generally, you can add a report viewer to your aspx page. You can follow the steps below to add your report viewer.

Open the Toolbox window. From the Data group, drag a ReportViewer control onto the page.

If it is not already open, open the ReportViewer Tasks smart tag panel by clicking the triangle in the upper right corner of the ReportViewer control on the Web page. In the Choose Report box, select the fully qualified file name for Report.rdlc.

When you select a report, instances of data sources used in the report are created automatically. Code is generated to instantiate each DataTable (and its DataSet container) and an ObjectDataSource control corresponding to each data source used in the report. This data source control is configured automatically.

And then, you can preview and print reports from reportviewer in the following way:
http://msdn2.microsoft.com/en-us/library/ms251693(VS.80).aspx

For more information on embed a report in your website, see:
http://msdn2.microsoft.com/en-us/library/ms252123(VS.80).aspx

Thanks.

Preventing unauthorized access

I am building a windows forms VB .NET project that connects to an SQL server
database. The problem we have is that since we are using windows-based aut
hentication, anyone with access or even excel can connect to the sql server
and fiddle with the data.
I looked into application roles but using this we lose flexibility for givin
g (or removing) rights from specific users.
This problem wouldn't exist if we were building web apps, as we could set th
ings up in a way the web server would be the only one to connect to the data
base.
What do people use in enterprises to prevent users from connecting to databa
ses with unauthorized apps ?
Thanks in advance.Use only stored procedures to access the database in your application. Do
not grant any permissions on tables or views to users; only grant
permissions on stored procedures. This will effectively block access to the
database from any source except your application (or a keen user who knows
how to properly call the stored procedures).
"/dev/null" <anonymous@.discussions.microsoft.com> wrote in message
news:4AD3A344-840C-47B4-A9DE-9968BF748D0C@.microsoft.com...
> I am building a windows forms VB .NET project that connects to an SQL
server database. The problem we have is that since we are using
windows-based authentication, anyone with access or even excel can connect
to the sql server and fiddle with the data. I looked into application roles
but using this we lose flexibility for giving (or removing) rights from
specific users.
> This problem wouldn't exist if we were building web apps, as we could set
things up in a way the web server would be the only one to connect to the
database.
> What do people use in enterprises to prevent users from connecting to
databases with unauthorized apps ?
> Thanks in advance.

Preventing Remote Access to SQL Server

Our company's customer wants to prevent anyone from logging into SQL Server unless they are actually logged on the server machine. (In other words they do not want a user to be able to connect unless he or she is sitting at the server's keyboard). They do not wish to utilize NT authenication. In addition, there are a number of ASP and MTS apps that are required to connect using ADO connections. These apps are located on another server which is acting as the Web Server and the MTS Server. This server is on the other side of a firewall. I have been searching the Web for weeks now and have been unable to find any reference to anything similar. Any advice would be most appreciated.

JLLR
MCDBA MCSD
Database DeveloperThere is "a way" to prevent logging into the server unless sitting in front of it, but how do you picture those apps to continue functioning? Can you clarify your question?|||Here's the deal. SQL Server is running a machine we will call DBserver. There are several Web apps, and MTS apps running on another machine which is acting as an MTS Server and a Web Server. We will call this machine WBServer. There are also some VB exe's running on some client machines in the Network. WBServer is separated from DBServer by a firewall, (with WB server being outside the firewall). The servers are located in the server room. Inside the firewall, the are several machines which could connect to SQL Server using client tools (enterprise manager, Query analyzer, etc.) In addition, there were some machines linking to the data using ODBC connections in MS Access. Due to the fact there were alterations to the data in the tables which caused major complications to the National Defense readiness, the customer now requires all ad hoc connections to SQL Server be done on DBServer after the person making the change lsigns a logbook recording their entry into the server room and which machine on to which they will be logging. Due to the fact that individuals are violating the rules they wish to set up SQL Server to make it impossible to make an ad hoc connection other than from DBServer. However they do not want affect the way the various applications work. The obvious answer was to provide a limited number of logins and restrict their distribution. However, the passwords did not remain secret, and persons were able to get them and login. I have some thoughts bof a work around but I would like something better.

What do you think.|||If you wish to prevent users from connecting to SQL Server using Microsoft SQL Client Tools, if the are not sitting at the keyboard of the server. You could look at creating a script (job) that runs every 10 seconds let's say and looks for records in SYSPROCESSES for connections where program_name is in a list example :
SQL Query Analyzer
SQL Query Analyzer - Object Browser
SQL Profiler
ISQL-32
etc. and hostname is not equal to DBserver (using your name). Any matches would have there process terminated.

PS

What about users that create a link to SQL Server via MS Access and then perform ad-hoc queries via MS Access?|||Also Forbidden.|||Very interesting. There is the problem of server resources, and how often to run this procedure.. A user can do a lot of damage in 10 seconds. I will think about it.|||Actually a KILL is not forbidden, if you thought I meant to "DELETE" the record from sysprocesses your mistaking. As far as the 10 seconds goes that is an arbitrary number.

To correctly do what you want, you would not allow direct access to the database tables in the first place.

At our site all data queries and modifications are done via stored procedures which is in a controlled environment. Any ad-hoc queries are done on a separate server which is refreshed via database backup from production on a nightly bases.|||You misunderstand. MS Access ODBC links to the server are also forbidden.

It sounds like DBA's have some authority in your shop. Here they are just glorified data entry people until something goes wrong, then they do not listen to the advice anyway, or they ask you to build the impossible. If it were up to me, all apps would connect through Application roles., and no one would be able to connect to the DB if they were not in the an administrators role Then only certain NT Logins would be in that role. (Can't make that Admin group of Windows NT because everyone is in Admin group!!) But what do I know I am only an MCDBA.|||Tell them they can't screen their doors with chicken wire and then complain about flies in the house.

blindman|||There is a way to allow only certain applications to access a database on a SQLServer. It's called APPLICATION ROLE. This is the article in the Books on Line for it:

Establishing Application Security and Application Roles
The security system in Microsoft SQL Server is implemented at the lowest level: the database itself. This is the best method for controlling user activities regardless of the application used to communicate with SQL Server. However, sometimes security controls must be customized to accommodate the special requirements of an individual application, especially when dealing with complex databases and databases with large tables.

Additionally, you may want users to be restricted to accessing data only through a specific application (for example using SQL Query Analyzer or Microsoft Excel) or to be prevented from accessing data directly. Restricting user access in this way prohibits users from connecting to an instance of SQL Server using an application such as SQL Query Analyzer and executing a poorly written query, which can negatively affect the performance of the whole server.

SQL Server accommodates these needs through the use of application roles. Application roles are different than standard roles in that:

Application roles contain no members.
Microsoft Windows NT 4.0 or Windows 2000 groups, users, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user's connection through a specific application or applications. A user's association with an application role is due to his ability to run an application that activates the role, rather than his being a member of the role.

Application roles are inactive by default and require a password to be activated.

Application roles bypass standard permissions.
When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only through permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object, regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of an instance of SQL Server.

To ensure that all the functions of the application can be performed, a connection must lose default permissions applied to the login and user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked so the user can use the application successfully. Application roles overcome any conflicts with user's default permissions by temporarily suspending the user's default permissions and assigning them only the permissions of the application role.

Application roles allow the application, rather than SQL Server, to take over the responsibility of user authentication. However, because SQL Server still must authenticate the application when it accesses databases, the application must provide a password because there is no other way to authenticate an application.

If ad hoc access to a database is not required, users and Windows NT 4.0 or Windows 2000 groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, standardizing on one system-wide password assigned to an application role is possible, assuming access to the applications is secure.

There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or a SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to an instance of SQL Server, when the role is activated.

When an application user connects to an instance of SQL Server using Windows Authentication Mode, an application role can be used to set the permissions the Windows NT 4.0 or Windows 2000 user has in a database when using the application. This method allows Windows NT 4.0 or Windows 2000 auditing of the user account and control over user permissions, while she uses the application, to be easily maintained.

If SQL Server Authentication is used and auditing user access in the database is not required, it can be easier for the application to connect to an instance of SQL Server using a predefined SQL Server login. For example, an order entry application authenticates users running the application itself, and then connects to an instance of SQL Server using the same OrderEntry login. All connections use the same login, and relevant permissions are granted to this login.

Note Application roles work with both authentication modes.

Example
As an example of application role usage, a user Sue runs a sales application that requires SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in database Sales to work, but she should not have any SELECT, INSERT, or UPDATE permissions when accessing the Products or Orders tables using SQL Query Analyzer or any other tool. To ensure this, create one user-database role that denies SELECT, INSERT, or UPDATE permissions on the Products and Orders tables, and add Sue as a member of that database role. Then create an application role in the Sales database with SELECT, INSERT, and UPDATE permissions on the Products and Orders tables. When the application runs, it provides the password to activate the application role by using sp_setapprole, and gains the permissions to access the Products and Orders tables. If Sue tries to log in to an instance of SQL Server using any tool except the application, she will not be able to access the Products or Orders tables.

To create an application role

Transact-SQL

Enterprise Manager

How to create an application role (Enterprise Manager)
To create an application role

Expand a server group, and then expand a server.

Expand Databases, and then expand the database in which to create a role.

Right-click Roles, and then click New Database Role.

In the Name box, enter the name of the new application role.

Under Database role type, click Application role, and then enter a password.

See Also

Establishing Application Security and Application Roles

SQL-DMO

To set an application role

Transact-SQL

To change the password of an application role

Transact-SQL

SQL-DMO

To remove an application role

Transact-SQL

Enterprise Manager

How to remove an application role (Enterprise Manager)
To remove an application role

Expand a server group, and then expand a server.

Expand Databases, and then expand the database in which the application role exists.

Click Roles.

In the details pane, right-click the application role to remove, and then click Delete.

Confirm the deletion.

See Also

Establishing Application Security and Application Roles

SQL-DMO


For more information search the web with google and ask for application role sql server

Good luck
ionut

Originally posted by Joeller
Our company's customer wants to prevent anyone from logging into SQL Server unless they are actually logged on the server machine. (In other words they do not want a user to be able to connect unless he or she is sitting at the server's keyboard). They do not wish to utilize NT authenication. In addition, there are a number of ASP and MTS apps that are required to connect using ADO connections. These apps are located on another server which is acting as the Web Server and the MTS Server. This server is on the other side of a firewall. I have been searching the Web for weeks now and have been unable to find any reference to anything similar. Any advice would be most appreciated.

JLLR
MCDBA MCSD
Database Developer|||to inut: As stated earlier that is what I would do if I were able to do what I want. But I can't and that's all there is to that.

To blindman: It would be nice to tell these people what I think of their office politics, their so-called back up plans, their horrific excuse for a database etc., but that would probably cost our company it major customer and put 3/4 of the people here out of work. So I smile and tell them I will investigate ways to do what they want. It appears that the suggestion earlier re Killing the process after it starts will end up ing the only way to do this, as that is the third time I have gotten this suggestion. Then maybe I can put forward my plan for role based security. sigh.|||...but make sure it isn't YOUR head that rolls when (not if) the house of cards collapses.

blindman

Preventing Injection - Client Side

Thanks
I am building an office application which uses Microsoft Access as a
client and SQL Server as the server. I am new to SQL Server and I
don't want to anything that is too stupid. I am assuming the db
administrator would create a database specifically for this
application. The program must be able to create and drop tables and
works fine when hosted over the internet.
I am preventing everything but characters and numbers in my WHERE
clauses and data to prevent injection.
But besides this measure, the program has these access/security
needs/issues.
Tables - Create Drop Read Write
SPs -- Create Execute
In addition the program needs to read from
system_user
db_name()
information_schema.tables
Information_schema.columns
sysobjects
Am I doing anything too stupid if the admin would prefer a more secure
situation, maybe on a company database?
Willywilly wrote:
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
Who is the owner of these tables? Are they owned by "dbo"? If so, all
users would have to be aliased as the dbo in the database, which means
they probably have too many rights. They could be limited to creating
tables under their user names (e.g. Joe.MyTable), but this would not
give other users access to these tables. Creating and Dropping tables is
generally a system admin or database owner function and not normal for
an application. Unless, of course, you mean temp tables.
To perform DML operations on tables, I would use stored procedures. You
can use them for SELECT statements as well, but some users choose to
embed SQL in the app (I prefer SPs all around).
The other objects/functions are available to all users.
Maybe you could explain the need to create/drop tables a little more.
Same for stored procedures.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
Consider using parameters rather than concatenating user-supplied values to
build the SQL statement. This is more secure.
Hope this helps.
Dan Guzman
SQL Server MVP
"willy" <willrich33@.yahoo.com> wrote in message
news:1131557696.739603.25830@.g14g2000cwa.googlegroups.com...
> Thanks
> I am building an office application which uses Microsoft Access as a
> client and SQL Server as the server. I am new to SQL Server and I
> don't want to anything that is too stupid. I am assuming the db
> administrator would create a database specifically for this
> application. The program must be able to create and drop tables and
> works fine when hosted over the internet.
> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
> But besides this measure, the program has these access/security
> needs/issues.
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
> Am I doing anything too stupid if the admin would prefer a more secure
> situation, maybe on a company database?
> Willy
>|||Dan,David:
Thank you for making me think about security.
My application does not allow the user to use tables under any other
username (such as dbo) so he is fairly isolated. I realize Access
defaults to dbo but I will be shutting the database window down.
I tried to import a file to my database under a different username (like
dbo) but Access converted it back to the username of the new
database/login so that seems somewhat secure. This is why I am using
Access for a client program.
I am not coding for characters other than Like *[A-Z0-9] so the purging
of other characters from the column and table names that the user has
control of will have to do for now.
In the documentation I am going to highly recommend "isolating" the
application in separate db for "security reasons."
Thank you for suggesting parameters and DML as solutions for preventing
injection. I will keep my eye out for information on them.
I really need to learn more about SQL Server permissions and security.
Willy
*** Sent via Developersdex http://www.codecomments.com ***|||Comments inline
"willy" <willrich33@.yahoo.com> wrote in message
news:1131557696.739603.25830@.g14g2000cwa.googlegroups.com...
> Thanks
> I am building an office application which uses Microsoft Access as a
> client and SQL Server as the server. I am new to SQL Server and I
> don't want to anything that is too stupid.
You already missed this goal by using Access as the front end.

> I am assuming the db
> administrator would create a database specifically for this
> application. The program must be able to create and drop tables and
> works fine when hosted over the internet.
>
This would require opening a SQL port to the internet. Not a particularly
good idea from a security standpoint, especially when you are talking about
the privileges necessary to do what you ask.

> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
>
Doesn't matter. With open network ports and SQL credentials in the Access
app, your server is open to conection via Query Analyzer or any other SQL
client app. Who cares about SQL injection when I can send any SQL command I
desire.
> But besides this measure, the program has these access/security
> needs/issues.
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
> Am I doing anything too stupid if the admin would prefer a more secure
> situation, maybe on a company database?
Maybe on a throwaway system. I certainly wouldn't allow any such
application anywhere near any of my servers.
> Willy
>
Sorry if I sound harsh, but I am trying to discourage you from making some
fundamental mistakes in building a SQL application. Access front end
'applications' have caused me more headaches than any other single app dev
environment when connecting to SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Preventing Enterprise Manager access from Windows users

Hello,
Scenario: SQL 2000 or 2005 server in an ADS domain. ADS groups are created
as SQL logins, and those logins are created as database users with datareade
r
and/or datawriter roles to a specific database. An intranet web site is
created to allow the ADS users in those ADS groups access to the data.
Problem: How do I prevent the ADS users in those ADS groups from running
Enterprise Manager (or creating their own ODBC connections, etc.) and messin
g
with my database? Or, put another way, how do I prevent the users from using
their own interface to the database?
Thanks much,
-TonyA common practice for something like this would be too...
1. Create Stored Procedures that the application uses to manipulate data.
2. Assign those groups Execute Permissions on the Stored Procedures.
3. Deny Data Reader and Data Writer to those groups.
If you are using ADHOC in your program this is going to be a little tougher.
What do you think?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:ADF6BDE5-11D7-496E-994D-F9B56EF645E3@.microsoft.com...
> Hello,
> Scenario: SQL 2000 or 2005 server in an ADS domain. ADS groups are created
> as SQL logins, and those logins are created as database users with
> datareader
> and/or datawriter roles to a specific database. An intranet web site is
> created to allow the ADS users in those ADS groups access to the data.
> Problem: How do I prevent the ADS users in those ADS groups from running
> Enterprise Manager (or creating their own ODBC connections, etc.) and
> messing
> with my database? Or, put another way, how do I prevent the users from
> using
> their own interface to the database?
> Thanks much,
> -Tony
>|||Warren,
Thanks for the info. That does sound like a good solution. I'm assuming this
would allow users to run Enterprise Manager and see my databases and objects
listed, but not interact with them.
BTW, these applications are web apps using .net. So, perhaps another
alternative would be for me to autherize and authenticate users to the web
site using IIS and ADS groups, then use a SQL login and password (encrypted
in the web.config file) to handle db access. Adding your suggestion to this,
I could restrict the SQL login to execute only the needed sprocs.
Seems like this would prevent any access to my database by those ADS users
via Enterprise Manager or whatever. If the SQL login should be hacked, at
least it would only allow execute on the sprocs and nothing else.
Thanks,
-Tony
"Warren Brunk" wrote:

> A common practice for something like this would be too...
> 1. Create Stored Procedures that the application uses to manipulate data.
> 2. Assign those groups Execute Permissions on the Stored Procedures.
> 3. Deny Data Reader and Data Writer to those groups.
> If you are using ADHOC in your program this is going to be a little toughe
r.
> What do you think?
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:ADF6BDE5-11D7-496E-994D-F9B56EF645E3@.microsoft.com...
>
>|||Hello Tony
Please drop me a note if the following description sounds interesting to
you.
Best regards
Adrian
Here we go:
Corrupt users and compromised user-accounts, cf Phishing, account for the
majority of attacks in the commercial world. We present a two-stage
anti-corruption system (ACS) for database servers that, in the first stage,
makes it very hard to gain access to a database with an unauthorised client
application or from an unauthorised client PC and, in the second stage,
provides
early precise hints on users performing suspicious activities. Our first
implementation of the ACS is for the MS SQL Server 2000. It uses only
documented
functions and relies completely on mechanisms and data already provided by
the
database server. Its smooth and efficient operation for several months in a
middle-sized decentralised company proves it a valuable addition to the pool
of
security measures.
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:5D786B85-382F-49DD-8CE8-3B8A77DB8EF4@.microsoft.com...[vbcol=seagreen]
> Warren,
> Thanks for the info. That does sound like a good solution. I'm assuming
> this
> would allow users to run Enterprise Manager and see my databases and
> objects
> listed, but not interact with them.
> BTW, these applications are web apps using .net. So, perhaps another
> alternative would be for me to autherize and authenticate users to the web
> site using IIS and ADS groups, then use a SQL login and password
> (encrypted
> in the web.config file) to handle db access. Adding your suggestion to
> this,
> I could restrict the SQL login to execute only the needed sprocs.
> Seems like this would prevent any access to my database by those ADS users
> via Enterprise Manager or whatever. If the SQL login should be hacked, at
> least it would only allow execute on the sprocs and nothing else.
> Thanks,
> -Tony
>
> "Warren Brunk" wrote:
>

Preventing database access from server..

Hello,
I'm a bit new to SQL, so bear with me please.
There is myself and an assitant in charge of our SQL server. I have 2
databases attached and I want to block my assistant from accessing one of
them.
How do I actually prevent him from accessing this other DB at all?
Note that he will however need to be able to BACK IT UP.
Is it possible to block all access of him opening tables or doing quiries
but allowing him to schedulae its backups (data and logs)?
Thanks much,
MarkCreate a Login for your assistant that does not belong to a server
role. Make that login a user in a database and a member of the
db_backupoperator role in that database.
Mrpush wrote:
> Hello,
> I'm a bit new to SQL, so bear with me please.
> There is myself and an assitant in charge of our SQL server. I have 2
> databases attached and I want to block my assistant from accessing one of
> them.
> How do I actually prevent him from accessing this other DB at all?
> Note that he will however need to be able to BACK IT UP.
> Is it possible to block all access of him opening tables or doing quiries
> but allowing him to schedulae its backups (data and logs)?
> Thanks much,
> Mark