help in bulk load - first time user

Hi all,
I've a xml file like this:
<ROOT>
<customerlist>
<Customers sequence="1">
<details>
<PersonID>1</PersonID>
</details>
<name>
<LastName>Vinod</LastName>
<FirstName>Kumar</FirstName>
</name>
</Customers>
<Customers sequence="2">
<details>
<PersonID>2</PersonID>
</details>
<name>
<LastName>Saravana</LastName>
<FirstName>Kumar</FirstName>
</name>
</Customers>
</customerlist>
</ROOT>
how should I write an xsd file to store the data in the following sql server table (Person) with columns:
personId - primary key, LastName and FirstName.
Thanks.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:exDw0UcdEHA.320@.TK2MSFTNGP10.phx.gbl...
[snip]
> how should I write an xsd file to store the data in the following sql
> server table (Person) with columns:
I would start with the examples given in the SQLXML documentation. Look at
how the XSD is mapped to the XML. That is the best way to learn.
Bryant

Help How Make Backup and Restore ?

Hi all. I'm newbie user of sqlserver 7.

I need make Backup from specific tables of my database and my application send this file by mail to other users that restore this information in yours Database.

How i can do it (i dont know the specifics commands to backup and Restore)

Other question

if I only backup the log file and restores this in other database
I'll have the same information in all users ?

Help me please

Thanks in AdvanceCheck BOL on the syntax. You can place your specific tables on filegroups and have your users restore those filegroups after initial full db restore.|||Check BOL on the syntax. You can place your specific tables on filegroups and have your users restore those filegroups after initial full db restore.

Excuse me, but i don't understand what is BOL. ?

How i said i'm newbie.|||Books
On
Line|||Originally posted by rdjabarov
Books
On
Line

Thanks, I will Try.|||I connect like database admin and

try to make differential backup using this command

1- Back up Database Cyber to XXX With Differential

2- Back up Log Cyber to XXX

The to test the back up

I run then this restore command

1- Restore database Cyber from XXX with norecovery and

2- Restore Log Cyber from XXX with recovery.

All is perfect

I close the connection

When I try to connect to Cyber database the server send me this message

Server Msg 927, Level 14 State 2

Database Cyber Cannot Be opened. It is in the middle of restore ?

Why the database cannot be opened ?

Thank In advance

Franklin|||Are you trying to open it from Enterprise Manager? I suspect that if you right-mouse click on Databases folder and select Refresh, - you won't get this error.|||I'm trying to open from query analizer, because my application must be do it (the backup - restore) using transaq - sql.

thanks
franklin

Originally posted by rdjabarov
Are you trying to open it from Enterprise Manager? I suspect that if you right-mouse click on Databases folder and select Refresh, - you won't get this error.sql

Help for printing reports programatically

Hi.....
I have developed a application which create the report and save it in
user selected location.
All this i done in one class. this application shows all the reports on
report server and render the report for user selected parameters. and
save it with folder browser option on a location. file name is report
name.
Now i have to write another class which will take this saved file name
as input and print that.
i found only printing method for a particular report but i dont want to
again render that report in print class. i have sen the available
articles on net which takes the report name and render it and print
that.
how i can access that saved file in another class and if i have to
again render that report then how i can pass those parameters value
again in render method in print class.
But for my application i have already render that report, so any one
please tell me how i can do that.
Thanks & Regards
DineshDinesh,
This is just a suggestion - what if you saved the report as a pdf file ?
Displaying a pdf file from a location on the hard disk doesn't need the
report engine anymore.
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1164717797.911571.169550@.j72g2000cwa.googlegroups.com...
> Hi.....
> I have developed a application which create the report and save it in
> user selected location.
> All this i done in one class. this application shows all the reports on
> report server and render the report for user selected parameters. and
> save it with folder browser option on a location. file name is report
> name.
>
> Now i have to write another class which will take this saved file name
> as input and print that.
> i found only printing method for a particular report but i dont want to
> again render that report in print class. i have sen the available
> articles on net which takes the report name and render it and print
> that.
>
> how i can access that saved file in another class and if i have to
> again render that report then how i can pass those parameters value
> again in render method in print class.
>
> But for my application i have already render that report, so any one
> please tell me how i can do that.
>
> Thanks & Regards
> Dinesh
>|||hi Andrei.......
Thanks for your reply.....
Ya you are right......It will not require report engine
again........
i want to know only that how i can print that saved file which is saved
in hard disk.
My problem is that i wrote print function in a seprate class. at that
place i am not able to access that file name with full path.......how
i can do this.......
if you know this please tell me..........
Thanks & Regards
Dinesh
Andrei wrote:
> Dinesh,
> This is just a suggestion - what if you saved the report as a pdf file ?
> Displaying a pdf file from a location on the hard disk doesn't need the
> report engine anymore.
> "Dinesh" <dinesht15@.gmail.com> wrote in message
> news:1164717797.911571.169550@.j72g2000cwa.googlegroups.com...
> > Hi.....
> >
> > I have developed a application which create the report and save it in
> > user selected location.
> >
> > All this i done in one class. this application shows all the reports on
> > report server and render the report for user selected parameters. and
> > save it with folder browser option on a location. file name is report
> > name.
> >
> >
> >
> > Now i have to write another class which will take this saved file name
> > as input and print that.
> >
> > i found only printing method for a particular report but i dont want to
> > again render that report in print class. i have sen the available
> > articles on net which takes the report name and render it and print
> > that.
> >
> >
> >
> > how i can access that saved file in another class and if i have to
> > again render that report then how i can pass those parameters value
> > again in render method in print class.
> >
> >
> >
> > But for my application i have already render that report, so any one
> > please tell me how i can do that.
> >
> >
> >
> > Thanks & Regards
> >
> > Dinesh
> >

Help for a new user

I've created a DB in SQL Server, and a user 'X', administrator on it. The DB
is genereted by a file, using osql. The tables' owner is 'X', but when I
try to insert some rows into the tables (by osql), the response is:
"INSERT permission denied on object 'DUMMY', database 'EC_PROVA', owner
'X' "

Is there anybody that can help me?
Thank you.
Fedefede (fedina_no_chicca_spam@.libero_spam_no.it) writes:
> I've created a DB in SQL Server, and a user 'X', administrator on it.
> The DB is genereted by a file, using osql. The tables' owner is 'X',
> but when I try to insert some rows into the tables (by osql), the
> response is: "INSERT permission denied on object 'DUMMY', database
> 'EC_PROVA', owner 'X' "

As whom do you run the INSERT statement?

What does "sp_helprotect DUMMY" say?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> ha scritto nel messaggio
> As whom do you run the INSERT statement?
I launch a script using this command line:
osql -U X -P passX -D SQLPROVA -i "Schema20040723.sql" -o "SchemaCrea.log"
So, I think the user is X...
> What does "sp_helprotect DUMMY" say?
It doesn't say anything...|||fede (fedina_no_chicca_spam@.libero_spam_no.it) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> ha scritto nel messaggio
>> As whom do you run the INSERT statement?
> I launch a script using this command line:
> osql -U X -P passX -D SQLPROVA -i "Schema20040723.sql" -o "SchemaCrea.log"
> So, I think the user is X...

But I don't what it's that script. It is difficult to assist when I don't
have full information of what you are doing. If that script performs

CREATE TABLE dummy (a int NOT NULL)
go
INSERT dummy (a) VALUES(12)

This should succeed. So I assume you are doing something else, but I don't
know what.

>> What does "sp_helprotect DUMMY" say?
> It doesn't say anything...

Not even?

Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.

By the way, what does SELECT @.@.version say on your server?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> ha scritto nel messaggio
news:Xns953279F885DYazorman@.127.0.0.1...

> But I don't what it's that script. It is difficult to assist when I don't
> have full information of what you are doing. If that script performs
This is a piece of code in the script:

CREATE TABLE X.DUMMY (
ATTIVITA varchar(30) NOT NULL,
DATA datetime NOT NULL,
UTENTE varchar(30) NOT NULL,
TEMPO datetime NULL,
NOTA varchar(50) NULL,
DBVERS varchar(15) NULL,
EURO varchar(1) NOT NULL,
PRIMARY KEY (DATA, ATTIVITA, UTENTE)
)

go
insert into X.DUMMY (data, attivita, utente, nota, euro, dbvers)
values ('01/01/1999','POTWIN3','PROJECT','NON ELIMINARE MAI!',
'S', '038')

But the problem is not on the script (this script, with obviously
differences is used to create db in DB2 and Oracle), because I've tried to
launch it from SQL Query Analyzer and it works well (using as default the
administrator user 'sa').
The problem seems to be the authorizations for user X, used to create the
schema of the DB, created as administrator in SQL Server. When I look the
properties of the table, it seems that X has the rights to insert, delete
and update data in the table.

> By the way, what does SELECT @.@.version say on your server?

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)

Help convert MS Access function to MS SQL User Defined Function

I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.
For example:
TrimZero("000000001023") > "1023"
TrimZero("E1025") > "E1025"
TrimZero("000000021021") > "21021"
TrimZero("R5545") > "R5545"
Here is the function that works in access:
Public Function TrimZero(strField As Variant) As String
Dim strReturn As String
If IsNull(strField) = True Then
strReturn = ""
Else
strReturn = strField
Do While Left(strReturn, 1) = "0"
strReturn = Mid(strReturn, 2)
Loop
End If
TrimZero = strReturn
End Function
Is this not possible? I seem not to be able to figure it out onmy own. If I am missing any information in my question, whichinformation should I include?
|||Okay.. with the help from experts-exchange.com I was able to get the answer.
CREATE FUNCTION [dbo].[TrimZero] (@.MyString varchar(50))
RETURNS varchar(50)
AS
BEGIN
If ISNUMERIC(@.MyString) = 1
Begin
While Left (@.MyString,1) = '0'
Begin
Set @.MyString = Right(@.Mystring,Len(@.Mystring) -1)
End
End
Return @.MyString
END
|||A even better function to do the same thing:
create function dbo.trimzero (@.mystring varchar(50))
returns varchar(50)
AS
begin
declare @.myStrInt int
if isnumeric(@.mystring)=1
set @.myString = cast(@.mystring as int)
return @.mystring
end
GO

Help connecting pages which has Database connection.

I am a completely new asp.net / SQL server user.
I've been only working with PHP + MYSQL server before which is very different.

There are databases which come with the SQLserver, such as "master"
and there are databases which i create by my own.

When using "master" database, everything works fine.
here's a screenshot.
http://img528.imageshack.us/img528/57/d1if7.jpg

but when using database which i created by my own, i get an error
whether i use ip or localhost.
http://img513.imageshack.us/img513/4598/d2iz7.jpg

only when i use the debugger, it gives me some port, then i am able to connect but only using localhost... therefore i can't give the adress to anyone else.
http://img528.imageshack.us/img528/3394/d3jn6.jpg

What's the difference between the Built in databases and those i create?
how can i make that i could access pages which has database connection to databases i created by my own??

Sorry for my English, i know it's not really understandable.Anyone? it seems like something which everyone who uses asp.net with sql server for a while should know.
ty.

help and advice on waittype 0x0044

Hi,
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
6 13 151115052 10 0x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write of
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.

help and advice on waittype 0x0044

Hi,
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
613151115052100x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.
Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write of
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:

> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 613151115052100x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>
|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange
|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.

help and advice on waittype 0x0044

Hi,
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
6 13 151115052 10 0x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write o
f
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:

> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.

Help again

I have a database with SQL server 2000 as the engine and Access 2000 on the client end (GUI user interface) I was wanting to make a switchboard like the original one in accesss only I dont have that option to use it when the tables are sql tables instead of access tables. The only I could come up with is a form with buttons on it that lead to different forms but it doesnt have the same abilities as the original switchboard in access. When I click on a particular form button the form comes up but the original form is not hidden, its just not the same..and I dont know vb so what can I do?I'm not sure I completely understand your question, but you should be able to export all your Access table to SQL, drop your existing Access tables, link the SQL tables (via ODBC), rename the tables to the appropriate names and everything should work like a charm. Unless I'm missing something, of course. :confused: :o|||You're from Lakeside? Well, Yee Haw!!!.

Just kidding! :)

Help ! Nested Stored Procedure, is this the best way???

Hi all,
Excuse my lack of knowledge in this area but what I am trying to do is
1) The user requests that say 4 tickets are to be held
2) The VB program calls a sproc that executes and tries to update the
status of each ticket in turn
3) If 4 are available then all is dandy
4) If 4 are not then any updates are rolled back and the user notifed
Note this is a true multi user environment, the tickets don't have to
be locked in sequence, they are just places on an event
Tried this but got odd rollback messages like
Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 6, current count =
7.
Line 23 is the return statement
SQL 2000 is the beast in use
HELP!! Ideas
Sproc is
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 1
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId)
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
END
COMMIT TRAN BIP
SET @.Resp = 0
GO
"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411020824.24ac7479@.posting.google.c om...
> Hi all,
> Excuse my lack of knowledge in this area but what I am trying to do is
> 1) The user requests that say 4 tickets are to be held
> 2) The VB program calls a sproc that executes and tries to update the
> status of each ticket in turn
> 3) If 4 are available then all is dandy
> 4) If 4 are not then any updates are rolled back and the user notifed
> Note this is a true multi user environment, the tickets don't have to
> be locked in sequence, they are just places on an event
> Tried this but got odd rollback messages like
> Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 6, current count =
> 7.
> Line 23 is the return statement
> SQL 2000 is the beast in use
> HELP!! Ideas
> Sproc is
>
Your immediate problem is that TSQL's IF only affects the next statement.
You wrote
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
which is equivilent to
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
Id] = @.EventId) < 1
BEGIN
ROLLBACK TRAN BIP
END
SET @.Resp = 1
RETURN
So you are always returning at line 23.
You probably have some more concurrency problems after fixing this, so test
and post again when you get deadlocks or double bookings.
David
|||Cheers David, good point, not used the IF too much and it was always
single statement before
I'll rework, give it a go and post back if necessary
Thanks again
Shaun
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message news:<OqHo#4PwEHA.3108@.TK2MSFTNGP14.phx.gbl>...
> "Shaun" <shaunsizen@.msn.com> wrote in message
> news:4a2f9143.0411020824.24ac7479@.posting.google.c om...
> Your immediate problem is that TSQL's IF only affects the next statement.
> You wrote
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < 1
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> which is equivilent to
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
> Id] = @.EventId) < 1
> BEGIN
> ROLLBACK TRAN BIP
> END
> SET @.Resp = 1
> RETURN
> So you are always returning at line 23.
> You probably have some more concurrency problems after fixing this, so test
> and post again when you get deadlocks or double bookings.
> David

Help ! Nested Stored Procedure, is this the best way???

Hi all,
Excuse my lack of knowledge in this area but what I am trying to do is
1) The user requests that say 4 tickets are to be held
2) The VB program calls a sproc that executes and tries to update the
status of each ticket in turn
3) If 4 are available then all is dandy
4) If 4 are not then any updates are rolled back and the user notifed
Note this is a true multi user environment, the tickets don't have to
be locked in sequence, they are just places on an event
Tried this but got odd rollback messages like
Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 6, current count = 7.
Line 23 is the return statement
SQL 2000 is the beast in use
HELP!! Ideas :)
Sproc is
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 1
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId)
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
END
COMMIT TRAN BIP
SET @.Resp = 0
GO"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Hi all,
> Excuse my lack of knowledge in this area but what I am trying to do is
> 1) The user requests that say 4 tickets are to be held
> 2) The VB program calls a sproc that executes and tries to update the
> status of each ticket in turn
> 3) If 4 are available then all is dandy
> 4) If 4 are not then any updates are rolled back and the user notifed
> Note this is a true multi user environment, the tickets don't have to
> be locked in sequence, they are just places on an event
> Tried this but got odd rollback messages like
> Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 6, current count => 7.
> Line 23 is the return statement
> SQL 2000 is the beast in use
> HELP!! Ideas :)
> Sproc is
>
Your immediate problem is that TSQL's IF only affects the next statement.
You wrote
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
which is equivilent to
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
Id] = @.EventId) < 1
BEGIN
ROLLBACK TRAN BIP
END
SET @.Resp = 1
RETURN
So you are always returning at line 23.
You probably have some more concurrency problems after fixing this, so test
and post again when you get deadlocks or double bookings.
David|||Cheers David, good point, not used the IF too much and it was always
single statement before
I'll rework, give it a go and post back if necessary :)
Thanks again
Shaun
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message news:<OqHo#4PwEHA.3108@.TK2MSFTNGP14.phx.gbl>...
> "Shaun" <shaunsizen@.msn.com> wrote in message
> news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> > Hi all,
> > Excuse my lack of knowledge in this area but what I am trying to do is
> >
> > 1) The user requests that say 4 tickets are to be held
> > 2) The VB program calls a sproc that executes and tries to update the
> > status of each ticket in turn
> > 3) If 4 are available then all is dandy
> > 4) If 4 are not then any updates are rolled back and the user notifed
> >
> > Note this is a true multi user environment, the tickets don't have to
> > be locked in sequence, they are just places on an event
> >
> > Tried this but got odd rollback messages like
> > Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> > Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> > TRANSACTION statement is missing. Previous count = 6, current count => > 7.
> >
> > Line 23 is the return statement
> >
> > SQL 2000 is the beast in use
> >
> > HELP!! Ideas :)
> >
> > Sproc is
> >
> >
> Your immediate problem is that TSQL's IF only affects the next statement.
> You wrote
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < 1
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> which is equivilent to
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
> Id] = @.EventId) < 1
> BEGIN
> ROLLBACK TRAN BIP
> END
> SET @.Resp = 1
> RETURN
> So you are always returning at line 23.
> You probably have some more concurrency problems after fixing this, so test
> and post again when you get deadlocks or double bookings.
> David

Help ! Nested Stored Procedure, is this the best way???

Hi all,
Excuse my lack of knowledge in this area but what I am trying to do is
1) The user requests that say 4 tickets are to be held
2) The VB program calls a sproc that executes and tries to update the
status of each ticket in turn
3) If 4 are available then all is dandy
4) If 4 are not then any updates are rolled back and the user notifed
Note this is a true multi user environment, the tickets don't have to
be locked in sequence, they are just places on an event
Tried this but got odd rollback messages like
Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 6, current count =
7.
Line 23 is the return statement
SQL 2000 is the beast in use
HELP!! Ideas
Sproc is
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 1
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId)
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
END
COMMIT TRAN BIP
SET @.Resp = 0
GO"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Hi all,
> Excuse my lack of knowledge in this area but what I am trying to do is
> 1) The user requests that say 4 tickets are to be held
> 2) The VB program calls a sproc that executes and tries to update the
> status of each ticket in turn
> 3) If 4 are available then all is dandy
> 4) If 4 are not then any updates are rolled back and the user notifed
> Note this is a true multi user environment, the tickets don't have to
> be locked in sequence, they are just places on an event
> Tried this but got odd rollback messages like
> Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 6, current count =
> 7.
> Line 23 is the return statement
> SQL 2000 is the beast in use
> HELP!! Ideas
> Sproc is
>
Your immediate problem is that TSQL's IF only affects the next statement.
You wrote
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
which is equivilent to
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].&#
91;Event
Id] = @.EventId) < 1
BEGIN
ROLLBACK TRAN BIP
END
SET @.Resp = 1
RETURN
So you are always returning at line 23.
You probably have some more concurrency problems after fixing this, so test
and post again when you get deadlocks or double bookings.
David|||Cheers David, good point, not used the IF too much and it was always
single statement before
I'll rework, give it a go and post back if necessary
Thanks again
Shaun
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message news:<OqHo#4P
wEHA.3108@.TK2MSFTNGP14.phx.gbl>...
> "Shaun" <shaunsizen@.msn.com> wrote in message
> news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Your immediate problem is that TSQL's IF only affects the next statement.
> You wrote
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < 1
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> which is equivilent to
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tick
ets].[Event
> Id] = @.EventId) < 1
> BEGIN
> ROLLBACK TRAN BIP
> END
> SET @.Resp = 1
> RETURN
> So you are always returning at line 23.
> You probably have some more concurrency problems after fixing this, so tes
t
> and post again when you get deadlocks or double bookings.
> David

HELP ! Login failed for user MachineName\ASPNET

Hi,

I am new to ASP.NET and have an ever approaching deadline to develop a database driven web application. Having read a lot of articles on the Internet I decided on Visual Studio 2003, MSSQL Server 2000 Developer Edition, all running off Windowns XP Pro.

I cannot get the ASP to access the database, I keep getting the following message

System.Data.SqlClient.SqlException: Login failed for user 'MachineName\ASPNET'

I do not know what to do to sort this problem and I cannot test anything I have developed as a result. I am very desperate for help as I have been trying to sort this out for the last 5 days.

Somebody please please please help me!!!!!!!!!

Thank you if you can.

RW#1
install Connector/ODBC - MySQL ODBC driver
http://dev.mysql.com/downloads/

#2
check out Connector/ODBC Programmer's Reference Manual
http://www.mysql.com/search/?q=myodbc

#3
Here is my code to populate a datagrid from a mySQL database.

IN GLOBAL.ASAX
Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
'Create Database Connection String
Dim globalConnStr As String
globalConnStr = "driver={MySQL ODBC 3.51 Driver};"
globalConnStr = globalConnStr + "server=localhost;"
globalConnStr = globalConnStr + "port=3306;"
globalConnStr = globalConnStr + "Stmt=;"
globalConnStr = globalConnStr + "uid=root;"
globalConnStr = globalConnStr + "pwd=;"
globalConnStr = globalConnStr + "database=smfr_wildland;"
globalConnStr = globalConnStr + "Option=3;"

'Assign Database Connection String To
'global connection object
Application("globalConnStr") = globalConnStr
End Sub

in your code behind page that contains a datagrid...It should look KINDA like this.

Dim strSQL As New System.Text.StringBuilder
strSQL.Append("SELECT users.*,smfr_shifts.shift_name")
strSQL.Append(" FROM users")
strSQL.Append(" INNER JOIN smfr_shifts ON users.user_shift_id = smfr_shifts.shift_id")
strSQL.Append(" WHERE users.user_deploymentYN = " & x)
strSQL.Append(" ORDER BY users.user_deploymentYN DESC , users.user_sort;")

Dim DBConn As System.Data.Odbc.OdbcConnection
Dim DBCommand As System.Data.Odbc.OdbcDataAdapter

Dim DSPageData As New DataSet
DBConn = New System.Data.Odbc.OdbcConnection(Application.Item("globalConnStr"))
DBCommand = New System.Data.Odbc.OdbcDataAdapter(strSQL.ToString, DBConn)

DBCommand.Fill(DSPageData, "users")

dgTeam.DataSource = DSPageData.Tables("users").DefaultView

dgTeam.DataBind()

'Close and Clean-Up
DBConn.Close()
DBConn.Dispose()
DBConn = Nothing

DBCommand.Dispose()
DBCommand = Nothing

DSPageData.Dispose()
DSPageData.Clear()
DSPageData = Nothing

-------
NO System.Data.SqlClient
USE System.Data.Odbc

Hope this helps?

Tom|||Ralph,

If you are indeed using Microsoft SQL Server, then your code should be OK. You've posted in the MySQL section.

What you need to do is add the MACHINENAME\ASPNET user as a Login in your database, and give that user rights to the database objects it needs.

When an ASP.NET application trys to access your SQL Server database, it does so by using that account.

[now moved from MySQL forum to SQL Server forum]|||A few important remarks:
1. ASPNET will only work on W2K and Windows XP, not on Windows Server 2003 where the identity of the application pool will be used instead (network service account).
2. Even if yo'ure on W2K, the ASPNET user can be bypassed if you're using (Windows authentication with) impersonation or you've changed the processModel section; in that case, use that user account on SQL Server instead to give permissions to.
3. However, a big fat remark: if you grant ASPNET or NETWORK SERVICE rights to the database, every other service/application running in that identity's context will have rights on the database as well. On a hosted environment where others can upload code to the machine as well, this is a big big security leak since others can write code to mess up your database. If you still need to do this, consider to give the user only read rights on the database!
4. It would be better from this side of the picture to use SQL authentication instead, although the password is not encrypted then when it's sent to the server. This is a less "heavy" issue if SQL Server is running on the same box as the web server (definitely not a scalable solution, but still it's out there in many places) or when the server environment is heavy controlled (a switched network between the servers).
5. Another approach is to impersonate as a certain identity from within the code whenever you want to talk to the database and to undo that impersonation afterward, as shown on my blog intip 7.

In any case, try to lock down the number of users that can access the database as much as you can by creating separate users on the level of the database for each database (which has only rights on that database). Never ever connect in a lazy way (e.g. using a sysadmin user).|||I did not read the posting close enough...it was in the mySQL group when I read it and replied.

MONITOR...you may delete/remove my two post for this discussion...SORRY

Tom|||Put this into a text file called grantpermission.sql. Change the 'MachineName\ASPNET' to your computer name and change Use dbname to your database name. Then use SQL Query Analyzer and execute the code to add the user and grant permissions. ASPNET will become owner as you can see in the code. This assumes that you have everything installed correctly. I have the almost the exact same setup. Although I'm also using WebMatrix. SQL Desktop Edition on Win XP Pro.

-- Begin --
DECLARE @.username sysname
SELECT @.username = 'MachineName\ASPNET'

USE master
EXEC sp_grantlogin @.username

USE dbname
EXEC sp_grantdbaccess @.username
EXEC sp_addrolemember N'db_owner', @.username
-- End --

Help ! Dangerous user with SA permissions

Hi guys,

I have been told that only users with SA priviledges could check the
status of a job. For this reason, I had to give SA priviledges to this
user so he could check the results of a job (succesful or not) and do
his work. Now, he makes changes on the database without telling me and
last time we nearly lost one day of work as he changed the db option
to truncate on checkpoint. I don't want to be the one to log on and
verify the results of the job everyday so is there any way that I can
give him limited permissions so he could only check the job and leave
the database alone ? I am pregnant so I am afraid that next time he
does something like that I may hit him on the head with the keyboard
(hormones... :-)Any suggestions would help,
Thanks !!!1) Change the sa password immeditaely.

2) Change the job so that it emails you and the target user every time it
completes and/or fails.|||And give him a written warning if you can't sack him immediately.

dcmfan@.aol.comSPNOAM (DCM Fan) wrote in message news:<20040301150956.18347.00000503@.mb-m06.aol.com>...
> 1) Change the sa password immeditaely.
> 2) Change the job so that it emails you and the target user every time it
> completes and/or fails.|||2) Or grant him permissions only to execute sp_help_jobhistory.

"DCM Fan" <dcmfan@.aol.comSPNOAM> wrote in message
news:20040301150956.18347.00000503@.mb-m06.aol.com...
> 1) Change the sa password immeditaely.
> 2) Change the job so that it emails you and the target user every time it
> completes and/or fails.|||pramos00@.bellsouth.net (Patricia) wrote in message news:<b50e122b.0403011202.463c7c0e@.posting.google.com>...
> Hi guys,
> I have been told that only users with SA priviledges could check the
> status of a job. For this reason, I had to give SA priviledges to this
> user so he could check the results of a job (succesful or not) and do
> his work. Now, he makes changes on the database without telling me and
> last time we nearly lost one day of work as he changed the db option
> to truncate on checkpoint. I don't want to be the one to log on and
> verify the results of the job everyday so is there any way that I can
> give him limited permissions so he could only check the job and leave
> the database alone ? I am pregnant so I am afraid that next time he
> does something like that I may hit him on the head with the keyboard
> (hormones... :-)Any suggestions would help,
> Thanks !!!

Create a view that points to the job tables in msdb and give the user
SELECT permissions to this view. If the view is create with DBO the
user should be able to access the information without having SA
priviledges.

Something like:

IF OBJECT_ID('vcheckjobstatus') IS NOT NULL
DROP VIEW vcheckjobstatus
GO

CREATE VIEW vcheckjobstatus
AS
SELECT sj.name, sh.run_status, MAX(run_date) as MAXDATE, MAX(run_time)
as MAXTIME
FROM MSDB.DBO.sysjobs sj
INNER JOIN MSDB.DBO.sysjobhistory sh
ON sj.job_id = sh.job_id
GROUP BY sj.name, sh.run_status
GO

GRANT SELECT ON vcheckjobstatus TO user

Randy
www.Database-Security.Info

Help - user permissions question

Hi,
I have a database user with read-only permissions in the database.
The user would like to be able to display a list of stored procedures
in the database. I would like to grant this user the permission to do
this, but without granting permission to *change* the sp's in any way.
Is there a way to do this?
THANKSRead only permission is enough to display the stored procedures list. Here i
s
the query:
/* It will list the names of all stored proc */
select name from sysobjects
where type = 'P'
order by name
/* It will list names of user stored procs only */
select name from sysobjects
where type = 'P' and status not like '-%'
order by name
Bob
"tootsuite@.gmail.com" wrote:

> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||What version of SQL Server?
In 2000, you can see all objects in the database.
In 2005, you can see the objects that you have permissions to use (execute).
You can grant
permission to see an object even if you cannot execute/select from etc it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1156963477.201608.121260@.74g2000cwt.googlegroups
.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||SQL 2000 or SQL 2005?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||Yes, sorry forgot to mention - SQL Server 2005
I know permissions work differently on 2005 than 2000. Please tell me I
do *not* have to grant execute permission to the user for each and
every user stored procedure in the database'
Is there another way to allow the user to see?
Arnie Rowland wrote:[vbcol=seagreen]
> SQL 2000 or SQL 2005?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...|||Yes, in SQL Server 2005 you can grant user "View Definition" permission so
that they can see the stored procedures but not alter or execute it.
For example:
GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
TO User;
GO
"tootsuite@.gmail.com" wrote:

> Yes, sorry forgot to mention - SQL Server 2005
> I know permissions work differently on 2005 than 2000. Please tell me I
> do *not* have to grant execute permission to the user for each and
> every user stored procedure in the database'
> Is there another way to allow the user to see?
>
> Arnie Rowland wrote:
>|||Hi,
Thanks - do I have to run this statement for each and every stored
procedure I wish to grant user permissions on? Or is there a single
stmt I can use?
Thanks
Bob wrote:[vbcol=seagreen]
> Yes, in SQL Server 2005 you can grant user "View Definition" permission so
> that they can see the stored procedures but not alter or execute it.
> For example:
> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> TO User;
> GO
>
> "tootsuite@.gmail.com" wrote:
>|||You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'.
You may wish to filter out procedures starting with 'dt'.
Then using dynamic SQL, cycle through the list making the appropriate
changes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...
> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
>|||There is no single statement, you have to run the grant statement for every
single stored proc. Or you can write a query which would print the grant
statements for you for every stored proc.
"tootsuite@.gmail.com" wrote:

> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
>|||Yes, that is what I thought - thanks
Arnie Rowland wrote:[vbcol=seagreen]
> You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINE
S
> where ROUTINE_TYPE = 'PROCEDURE'.
> You may wish to filter out procedures starting with 'dt'.
> Then using dynamic SQL, cycle through the list making the appropriate
> changes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...

Help - user permissions question

Hi,
I have a database user with read-only permissions in the database.
The user would like to be able to display a list of stored procedures
in the database. I would like to grant this user the permission to do
this, but without granting permission to *change* the sp's in any way.
Is there a way to do this?
THANKSRead only permission is enough to display the stored procedures list. Here is
the query:
/* It will list the names of all stored proc */
select name from sysobjects
where type = 'P'
order by name
/* It will list names of user stored procs only */
select name from sysobjects
where type = 'P' and status not like '-%'
order by name
Bob
"tootsuite@.gmail.com" wrote:
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||What version of SQL Server?
In 2000, you can see all objects in the database.
In 2005, you can see the objects that you have permissions to use (execute). You can grant
permission to see an object even if you cannot execute/select from etc it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||SQL 2000 or SQL 2005?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||Yes, sorry forgot to mention - SQL Server 2005
I know permissions work differently on 2005 than 2000. Please tell me I
do *not* have to grant execute permission to the user for each and
every user stored procedure in the database'
Is there another way to allow the user to see?
Arnie Rowland wrote:
> SQL 2000 or SQL 2005?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > Hi,
> >
> > I have a database user with read-only permissions in the database.
> >
> > The user would like to be able to display a list of stored procedures
> > in the database. I would like to grant this user the permission to do
> > this, but without granting permission to *change* the sp's in any way.
> >
> > Is there a way to do this?
> >
> > THANKS
> >|||Yes, in SQL Server 2005 you can grant user "View Definition" permission so
that they can see the stored procedures but not alter or execute it.
For example:
GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
TO User;
GO
"tootsuite@.gmail.com" wrote:
> Yes, sorry forgot to mention - SQL Server 2005
> I know permissions work differently on 2005 than 2000. Please tell me I
> do *not* have to grant execute permission to the user for each and
> every user stored procedure in the database'
> Is there another way to allow the user to see?
>
> Arnie Rowland wrote:
> > SQL 2000 or SQL 2005?
> >
> > --
> > Arnie Rowland, Ph.D.
> > Westwood Consulting, Inc
> >
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> >
> > <tootsuite@.gmail.com> wrote in message
> > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > Hi,
> > >
> > > I have a database user with read-only permissions in the database.
> > >
> > > The user would like to be able to display a list of stored procedures
> > > in the database. I would like to grant this user the permission to do
> > > this, but without granting permission to *change* the sp's in any way.
> > >
> > > Is there a way to do this?
> > >
> > > THANKS
> > >
>|||Hi,
Thanks - do I have to run this statement for each and every stored
procedure I wish to grant user permissions on? Or is there a single
stmt I can use?
Thanks
Bob wrote:
> Yes, in SQL Server 2005 you can grant user "View Definition" permission so
> that they can see the stored procedures but not alter or execute it.
> For example:
> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> TO User;
> GO
>
> "tootsuite@.gmail.com" wrote:
> > Yes, sorry forgot to mention - SQL Server 2005
> >
> > I know permissions work differently on 2005 than 2000. Please tell me I
> > do *not* have to grant execute permission to the user for each and
> > every user stored procedure in the database'
> >
> > Is there another way to allow the user to see?
> >
> >
> >
> > Arnie Rowland wrote:
> > > SQL 2000 or SQL 2005?
> > >
> > > --
> > > Arnie Rowland, Ph.D.
> > > Westwood Consulting, Inc
> > >
> > > Most good judgment comes from experience.
> > > Most experience comes from bad judgment.
> > > - Anonymous
> > >
> > >
> > > <tootsuite@.gmail.com> wrote in message
> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > > Hi,
> > > >
> > > > I have a database user with read-only permissions in the database.
> > > >
> > > > The user would like to be able to display a list of stored procedures
> > > > in the database. I would like to grant this user the permission to do
> > > > this, but without granting permission to *change* the sp's in any way.
> > > >
> > > > Is there a way to do this?
> > > >
> > > > THANKS
> > > >
> >
> >|||You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'.
You may wish to filter out procedures starting with 'dt'.
Then using dynamic SQL, cycle through the list making the appropriate
changes.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...
> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
>> Yes, in SQL Server 2005 you can grant user "View Definition" permission
>> so
>> that they can see the stored procedures but not alter or execute it.
>> For example:
>> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
>> TO User;
>> GO
>>
>> "tootsuite@.gmail.com" wrote:
>> > Yes, sorry forgot to mention - SQL Server 2005
>> >
>> > I know permissions work differently on 2005 than 2000. Please tell me I
>> > do *not* have to grant execute permission to the user for each and
>> > every user stored procedure in the database'
>> >
>> > Is there another way to allow the user to see?
>> >
>> >
>> >
>> > Arnie Rowland wrote:
>> > > SQL 2000 or SQL 2005?
>> > >
>> > > --
>> > > Arnie Rowland, Ph.D.
>> > > Westwood Consulting, Inc
>> > >
>> > > Most good judgment comes from experience.
>> > > Most experience comes from bad judgment.
>> > > - Anonymous
>> > >
>> > >
>> > > <tootsuite@.gmail.com> wrote in message
>> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
>> > > > Hi,
>> > > >
>> > > > I have a database user with read-only permissions in the database.
>> > > >
>> > > > The user would like to be able to display a list of stored
>> > > > procedures
>> > > > in the database. I would like to grant this user the permission to
>> > > > do
>> > > > this, but without granting permission to *change* the sp's in any
>> > > > way.
>> > > >
>> > > > Is there a way to do this?
>> > > >
>> > > > THANKS
>> > > >
>> >
>> >
>|||There is no single statement, you have to run the grant statement for every
single stored proc. Or you can write a query which would print the grant
statements for you for every stored proc.
"tootsuite@.gmail.com" wrote:
> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
> > Yes, in SQL Server 2005 you can grant user "View Definition" permission so
> > that they can see the stored procedures but not alter or execute it.
> >
> > For example:
> >
> > GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> > TO User;
> > GO
> >
> >
> >
> > "tootsuite@.gmail.com" wrote:
> >
> > > Yes, sorry forgot to mention - SQL Server 2005
> > >
> > > I know permissions work differently on 2005 than 2000. Please tell me I
> > > do *not* have to grant execute permission to the user for each and
> > > every user stored procedure in the database'
> > >
> > > Is there another way to allow the user to see?
> > >
> > >
> > >
> > > Arnie Rowland wrote:
> > > > SQL 2000 or SQL 2005?
> > > >
> > > > --
> > > > Arnie Rowland, Ph.D.
> > > > Westwood Consulting, Inc
> > > >
> > > > Most good judgment comes from experience.
> > > > Most experience comes from bad judgment.
> > > > - Anonymous
> > > >
> > > >
> > > > <tootsuite@.gmail.com> wrote in message
> > > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > > > Hi,
> > > > >
> > > > > I have a database user with read-only permissions in the database.
> > > > >
> > > > > The user would like to be able to display a list of stored procedures
> > > > > in the database. I would like to grant this user the permission to do
> > > > > this, but without granting permission to *change* the sp's in any way.
> > > > >
> > > > > Is there a way to do this?
> > > > >
> > > > > THANKS
> > > > >
> > >
> > >
>|||Yes, that is what I thought - thanks
Arnie Rowland wrote:
> You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_TYPE = 'PROCEDURE'.
> You may wish to filter out procedures starting with 'dt'.
> Then using dynamic SQL, cycle through the list making the appropriate
> changes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...
> > Hi,
> >
> > Thanks - do I have to run this statement for each and every stored
> > procedure I wish to grant user permissions on? Or is there a single
> > stmt I can use?
> >
> > Thanks
> >
> >
> > Bob wrote:
> >> Yes, in SQL Server 2005 you can grant user "View Definition" permission
> >> so
> >> that they can see the stored procedures but not alter or execute it.
> >>
> >> For example:
> >>
> >> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> >> TO User;
> >> GO
> >>
> >>
> >>
> >> "tootsuite@.gmail.com" wrote:
> >>
> >> > Yes, sorry forgot to mention - SQL Server 2005
> >> >
> >> > I know permissions work differently on 2005 than 2000. Please tell me I
> >> > do *not* have to grant execute permission to the user for each and
> >> > every user stored procedure in the database'
> >> >
> >> > Is there another way to allow the user to see?
> >> >
> >> >
> >> >
> >> > Arnie Rowland wrote:
> >> > > SQL 2000 or SQL 2005?
> >> > >
> >> > > --
> >> > > Arnie Rowland, Ph.D.
> >> > > Westwood Consulting, Inc
> >> > >
> >> > > Most good judgment comes from experience.
> >> > > Most experience comes from bad judgment.
> >> > > - Anonymous
> >> > >
> >> > >
> >> > > <tootsuite@.gmail.com> wrote in message
> >> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> >> > > > Hi,
> >> > > >
> >> > > > I have a database user with read-only permissions in the database.
> >> > > >
> >> > > > The user would like to be able to display a list of stored
> >> > > > procedures
> >> > > > in the database. I would like to grant this user the permission to
> >> > > > do
> >> > > > this, but without granting permission to *change* the sp's in any
> >> > > > way.
> >> > > >
> >> > > > Is there a way to do this?
> >> > > >
> >> > > > THANKS
> >> > > >
> >> >
> >> >
> >

Help - All Users can Access All Databases

Dan wrote:
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and read/wr
ite
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?
http://msdn.microsoft.com/msdnmag/i...ServerSecurity/Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
having an issue where, when I create a new user, even before I assign them
any rights they can connect to any database in my SQL instance and read/writ
e
everywhere. I can't imagine this is by design.
Anyone know whats going on here?|||Dan wrote:
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and read/wr
ite
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?
http://msdn.microsoft.com/msdnmag/i...ServerSecurity/|||Are any of these users in Windows groups (such as the administrators group)?
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:D49A8E3D-1788-48E6-B4D6-F42F6A9B16C3@.microsoft.com...
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and
> read/write
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?|||We ran into a similar issue. The issue for us is that we granted
permissions to the public role, and since the guest account is enabled
by default, everyone has access to that database via the guest role.
Since the public role includes guest, granting public role gives them
access. I dont know if this is the scenario you have encountered, but
it can be handled by not using the public role and just granting
permissions to roles or groups that you are in control of.|||Are any of these users in Windows groups (such as the administrators group)?
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:D49A8E3D-1788-48E6-B4D6-F42F6A9B16C3@.microsoft.com...
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and
> read/write
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?|||We ran into a similar issue. The issue for us is that we granted
permissions to the public role, and since the guest account is enabled
by default, everyone has access to that database via the guest role.
Since the public role includes guest, granting public role gives them
access. I dont know if this is the scenario you have encountered, but
it can be handled by not using the public role and just granting
permissions to roles or groups that you are in control of.

Help - All Users can Access All Databases

Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
having an issue where, when I create a new user, even before I assign them
any rights they can connect to any database in my SQL instance and read/write
everywhere. I can't imagine this is by design.
Anyone know whats going on here?Dan wrote:
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and read/write
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/|||Are any of these users in Windows groups (such as the administrators group)?
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:D49A8E3D-1788-48E6-B4D6-F42F6A9B16C3@.microsoft.com...
> Hello. I am using SQL Server 2005 Std. 32bit on Windows 2003 R2. I am
> having an issue where, when I create a new user, even before I assign them
> any rights they can connect to any database in my SQL instance and
> read/write
> everywhere. I can't imagine this is by design.
> Anyone know whats going on here?|||We ran into a similar issue. The issue for us is that we granted
permissions to the public role, and since the guest account is enabled
by default, everyone has access to that database via the guest role.
Since the public role includes guest, granting public role gives them
access. I dont know if this is the scenario you have encountered, but
it can be handled by not using the public role and just granting
permissions to roles or groups that you are in control of.

Help

When a user logs in the system I mark a Table in the database(that is a new id is generated)
Now I need to write a query which actually gets the following things--

1)Total no of people logged in the system for a given date
2)Minimum no of people logged in the system for a given date
3)Maximum no of people logged in the system for a given date
4)Average no of people logged in the system for a given date.

Hope You guys would help me in writing this queryYou can get the total number of people who logged in by doing something like:
SELECT COUNT(*) FROM LoginTable WHERE LoginDate = '2003/09/15'

I'm not sure what you mean by (2), (3), and (4). Do you mean the mininum number of people who were logged in simultaneously at a given moment in time during the day in question? (etc)

Cheers
Ken|||Thanks mate .
I will try to explain

DataBase fields are

id
date

So when a user is logged in I add a line to this table (id is identity field).

I am creating a report to show how many users logged on
between two dates (start date and end date (between date) ).

I will provide two Dates text boxes for the report i.e
Start Date and End Date and then search button
so for example if

say on these dates the logged users are as
On Date 01/08/2003 Logged users are 10
On Date 02/08/2003 Logged users are 8
On Date 03/08/2003 Logged users are 3
On Date 04/08/2003 Logged users are 5

So query should return
Count of user-->26 on these dates
Minimum users-->3 on these dates
Maximum users-->10
Average users-->sum of users/Count of users

I hope this makes some sense.