Pessimistic locking

I am attempting to try a pesimistic lock, meaning that i want to lock a row or table for a period of time and then relase it when i am done. To test this i wrote the following:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANsaction
Select * From configurationitem WITH (ROWLOCK,xlock)
where name = 'NextReceiptNumber' and category = 'AR';

Declare @.i int
set @.i = 0
while @.i < 300000
Begin
print @.i
set @.i = @.i + 1
end
COMMIT TRANsaction

To test, while the above is looping i open another query window and select from the same table using the following:

Select ConfigurationItemValue From configurationitem where ItemID = 418

This does not work because this query returns IMMEDIATELY. However, if I change the query to the following:

Select ConfigurationItemValue From configurationitem where name = 'NextReceiptNumber' and category = 'AR';

It does not return until the transaction query above is finished (which is the way it should work).

So, my question is, why does it not lock when i select by a primary key but lock when i do NOT select by a primary key (ItemID is a primary key).

thanks in advance.

Ok, I think we are missing something here. Is the primary key value for this row = 418? You should only have an exclusive lock on the row.

This is the table that I tested with, and it did wait when I looked for 418, and not for any other row. Any query that requires a table scan (snapshot isolation not withstanding) will not be able to complete (which would be the case for a query that looks for name and category, no matter what your values are.) This is because other queries will take a lock on every row in the table eventually and will get stuck on the locked rows.

drop table configurationItem
go
create table configurationItem
(
itemId int primary key,
name varchar(100),
category char(2),
configurationItemValue varchar(10)
)
insert into configurationItem
select 418,'NextReceiptNumber','AR','sals'
union all
select 2,'asldfjlka','AT','sals'
union all
select 3,'aqjsadklfaj','DR','sals'
union all
select 4,'ao2ioi23jkasd','DD','sals'
union all
select 5,'alifdjald','CD','sals'
union all
select 6,'ajsdflkasdlkja','CF','sals'
union all
select 7,'juqoiwfewoijlk','TT','sals'
union all
select 8,'asdancas','QR','sals'

|||Using XLOCK in SELECT statements will not prevent reads from happening. This is because SQL Server has a special optimization under read committed isolation level that checks if the row is dirty or not and ignores the xlock if the row has not changed. Since this is acceptable under the read committed isolation level semantics it is by design. So you will have to use a more aggressive locking hint like UPDLOCK with ROWLOCK. But what are you trying that requires such pessimistic locking strategies? Why do you want to do row-by-row procedural processing? Can't you use set-based operations instead?

Persistence of Time... DateTime bugs, that is

Guess what? The DateTime rounding bug never got fixed in SQL Server
2005. You know, this one:
SELECT CAST('10/25/2005 23:59:59.990' AS DATETIME) AS [990]
SELECT CAST('10/25/2005 23:59:59.991' AS DATETIME) AS [991]
SELECT CAST('10/25/2005 23:59:59.992' AS DATETIME) AS [992]
SELECT CAST('10/25/2005 23:59:59.993' AS DATETIME) AS [993]
SELECT CAST('10/25/2005 23:59:59.994' AS DATETIME) AS [994]
SELECT CAST('10/25/2005 23:59:59.995' AS DATETIME) AS [995]
SELECT CAST('10/25/2005 23:59:59.996' AS DATETIME) AS [996]
SELECT CAST('10/25/2005 23:59:59.997' AS DATETIME) AS [997]
SELECT CAST('10/25/2005 23:59:59.998' AS DATETIME) AS [998]
SELECT CAST('10/25/2005 23:59:59.999' AS DATETIME) AS [999]
There's an issue for it in MSDN Feedback Center, which you can vote on:
http://lab.msdn.microsoft.com/produ...37-10b837e28bc3
At this point it probably won't happen until a service pack, but at
least if we all vote, maybe it will get noticed. I could have sworn
someone promised relief years ago when I first read about this problem,
but it appears that was a hallucination.
argh..
Well, back to my stupid date-trimmed unit tests. Good day,
-ChrisChris Durkin wrote:
> Guess what? The DateTime rounding bug never got fixed in SQL Server
> 2005. You know, this one:
> SELECT CAST('10/25/2005 23:59:59.990' AS DATETIME) AS [990]
> SELECT CAST('10/25/2005 23:59:59.991' AS DATETIME) AS [991]
> SELECT CAST('10/25/2005 23:59:59.992' AS DATETIME) AS [992]
> SELECT CAST('10/25/2005 23:59:59.993' AS DATETIME) AS [993]
> SELECT CAST('10/25/2005 23:59:59.994' AS DATETIME) AS [994]
> SELECT CAST('10/25/2005 23:59:59.995' AS DATETIME) AS [995]
> SELECT CAST('10/25/2005 23:59:59.996' AS DATETIME) AS [996]
> SELECT CAST('10/25/2005 23:59:59.997' AS DATETIME) AS [997]
> SELECT CAST('10/25/2005 23:59:59.998' AS DATETIME) AS [998]
> SELECT CAST('10/25/2005 23:59:59.999' AS DATETIME) AS [999]
> There's an issue for it in MSDN Feedback Center, which you can vote
> on:
>
http://lab.msdn.microsoft.com/produ...
7-10b837e28bc3
> At this point it probably won't happen until a service pack, but at
> least if we all vote, maybe it will get noticed. I could have sworn
> someone promised relief years ago when I first read about this
> problem, but it appears that was a hallucination.
>
Didn't you read the MS response on that page? It's been "Resolved as By
Design "
In other words, it's not a bug: it's the way it's supposed to work. No
matter how many votes it gets, it is not going to be "fixed".
This rounding has occurred since the SQL 6.5 days, at least. If more
resolution is needed, a different datatype needs to be used.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Not a bug. From Books Online:
"Date and time data from January 1, 1753 through December 31, 9999, to
an accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds or 0.00333 seconds). Values are rounded to increments of
.000, .003, or .007 seconds, as shown in the table."
Exactly what enhancement are you looking for? Increasing the accuracy
from 0.00333 to 0.001 would seem to add very little value. Increasing
precision by 1 or 2 more decimals might be a more useful enhancement
but would cost extra strorage.
David Portas
SQL Server MVP
--|||<snip>

> This rounding has occurred since the SQL 6.5 days, at least. If more
> resolution is needed, a different datatype needs to be used.
What datatype are we supposed to use - is there a new datetime type in
2005 with more precision?|||I realize it's by design. You're being disingenuous. My point is that
it's bad design, which should have been fixed. A database product as
mature as SQL Server should support datetime precision in milliseconds
- at least! If it costs extra storage, who cares? GUIDs take lots of
storage, look how popular they've become. But give us the option.
The fact that SQL rounds to "one three-hundredth" of a second is
counter intuitive, and clearly shows that it is a design flaw, one that
has persisted to this day. What rational justification is there for
this behavior? Hundredths of a second, tenths, that I could see, but
every 3 milliseconds? Lame.
Maybe in the next version (SQL Server 2010) they can give us a new data
type called "bigdatetime", which allows greater precision. The old
datetime can be preserved for those purists who prefer working with
1/300 second intervals.|||Chris Durkin wrote:
> <snip>
>
> What datatype are we supposed to use - is there a new datetime type in
> 2005 with more precision?
You need to come up with your own scheme: perhaps use an int column to store
the number of milliseconds since midnight...something like that
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||>> A database product as mature as SQL Server should support datetime preci
sion in milliseconds - at least! <<
Actually, the FIPS-127 specs required at least 5 decimal places in a
TIMESTAMP; more would be better and it is getting a LOT easier to do.
I have a cheap alarm clock that sets itself to in milliseconds using
the NIST radio signal.
This is implementation is a "code museum" problem. Sybase started on
UNIX, which did a "clock tick" model of time. DB2 uses a "Cobol" model
of time which keeps separate fields for
"year-month-day-hour-minute-second-subseconds", etc.
But I agree that this is a serious flaw when you are trying to sell
your product for enterprise level and DW apps.

Perplexing Problem with tsql join.

I have been trying to figure this out for some time and I am sure there
must be an easier way than using a cursor. Given the following table
(simplified example):
ID CUSTID DTE
1 1 7/1/05
2 1 9/1/05
3 2 6/1/05
4 2 10/25/06
5 3 5/2/05
6 3 5/2/06
7 4 5/5/05
Return the max dte value record for each custid
So ie the return recordset would contain the following:
ID CUSTID DTE
2 1 9/1/05
4 2 10/25/06
6 3 5/2/06
7 4 5/5/05
Thanks in advance.
PaulYou could have more 1 or more records for each custid in my example
table I forgot to create on custid with 3 records.|||The following would work of course but what if you had another column
that could not be calculated by an aggregate column.
select max(id) id,cid,max(dte) dte from fred group by cid
for example lets add the folowing random values to column rnd
ID CUSTID DTE RND
1 1 7/1/05 32
2 1 9/1/05 42
3 2 6/1/05 68
4 2 10/25/06 2
5 3 5/2/05 5
6 3 5/2/06 9
7 4 5/5/05 3
8 3 6/2/06 7
So the return set would now include
ID CUSTID DTE RND
2 1 9/1/05 42
4 2 10/25/06 2
8 3 6/2/06 7
7 4 5/5/05 3|||Try,
select a.*
from t1 as a inner join (select custid, max(dte) as max_dte from t1 group by
custid) as b on a.custid = b.custid and a.dte = b.max_dte
AMB
"firebalrog" wrote:

> I have been trying to figure this out for some time and I am sure there
> must be an easier way than using a cursor. Given the following table
> (simplified example):
> ID CUSTID DTE
> 1 1 7/1/05
> 2 1 9/1/05
> 3 2 6/1/05
> 4 2 10/25/06
> 5 3 5/2/05
> 6 3 5/2/06
> 7 4 5/5/05
> Return the max dte value record for each custid
> So ie the return recordset would contain the following:
> ID CUSTID DTE
> 2 1 9/1/05
> 4 2 10/25/06
> 6 3 5/2/06
> 7 4 5/5/05
> Thanks in advance.
> Paul
>|||Thanks for the quick response. That is exactly what I needed. I forgot
you could use a query as part of the join. And even if I did I don't
think that I would have thought of using it in that way. Thats
beautiful. Now I will have to go through all my stored procedures and
check for places where I was using a cursor method to look for those
records in that type of situation.
Thanks again.

Perplexing Join

Hi,

I'm having a hard time figuring this out. Let me start by explaining my setup. I have a database that will hold high school football statistics. The two tables to focus on are the games table and the schools table--schema below:

schools
----
school_id (varchar, 4, unique)
school_name (varchar, 32)
school_city (varchar, 32)
school_district (varchar, 32)
games
----
game_id (int, identity, unique)
game_datetime (smalldatetime)
game_stadium (varchar, 32)
team_home (varchar, 4)
team_away (varchar, 4)

I didn't bother creating a separate stadiums table, because games will take place in only two stadiums. But here's what the row(s) I want to look like:

game_datetime, game_stadium, school_name (for team_home), school_name (for team_away)

The trouble is with pulling the school names for both teams from the schools table. I tried this query:

SELECT *FROM gamesINNERJOIN schoolsON team_home = school_idOR team_away = school_id

That pulls two rows for each game (one with the home_team's info and another with the away team's info).

The way the tables are constructed makes sense to me, but I'm not opposed to changing the schema. Perhaps you can point me in a different direction.

Thanks

Try this and see if it gives you the results your looking for

Select g.game_datetime, g.game_stadium, h.school_name, a.school_name

FROM Games g

Inner Join schools h on g.team_home = h.school_id

Inner Join schools a on g.team_away = a.school_id

|||

Oh wow! I didn't know you could do that. That's awesome!

Thanks!

PS: Is this a perfectly normal way to do this, or should I reconsider changing my tables?

|||

your tables are fine. Only thing I would do in changing your tables is to maybe change the names of the team_home | team_away to team_home_id | team_away_id. It has no affect at all on the outcome of your data. But 6 months from now you will know that those two fields are FK relations to school_id in your school table. Which helps from a management point of view.

And yes doing joins like this is perfectly fine.

|||

Thanks! You have no idea how much easier my life just got

Perpetual connection

Does anyone know the purpose of sp_trace_getdata and if it is normal for it
to run all the time. We have 2 production servers (mostly the same SQL
settings) so I am not sure why this is constantly running on one server and
not the other?
thanks
Meenal
sp_trace_getdata shows up when you are tracing SQL server activity, using
profiler or other means. You should see a number next to this. and that is
the ID of the trace. You can run the following sql to get more information
on that trace:
select * from ::fn_trace_getinfo(id)
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:%232tCyirOFHA.3668@.TK2MSFTNGP14.phx.gbl...
> Does anyone know the purpose of sp_trace_getdata and if it is normal for
> it
> to run all the time. We have 2 production servers (mostly the same SQL
> settings) so I am not sure why this is constantly running on one server
> and
> not the other?
> thanks
> Meenal
>
sql

Perpetual connection

Does anyone know the purpose of sp_trace_getdata and if it is normal for it
to run all the time. We have 2 production servers (mostly the same SQL
settings) so I am not sure why this is constantly running on one server and
not the other?
thanks
Meenalsp_trace_getdata shows up when you are tracing SQL server activity, using
profiler or other means. You should see a number next to this. and that is
the ID of the trace. You can run the following sql to get more information
on that trace:
select * from ::fn_trace_getinfo(id)
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:%232tCyirOFHA.3668@.TK2MSFTNGP14.phx.gbl...
> Does anyone know the purpose of sp_trace_getdata and if it is normal for
> it
> to run all the time. We have 2 production servers (mostly the same SQL
> settings) so I am not sure why this is constantly running on one server
> and
> not the other?
> thanks
> Meenal
>

Perpetual connection

Does anyone know the purpose of sp_trace_getdata and if it is normal for it
to run all the time. We have 2 production servers (mostly the same SQL
settings) so I am not sure why this is constantly running on one server and
not the other?
thanks
Meenalsp_trace_getdata shows up when you are tracing SQL server activity, using
profiler or other means. You should see a number next to this. and that is
the ID of the trace. You can run the following sql to get more information
on that trace:
select * from ::fn_trace_getinfo(id)
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:%232tCyirOFHA.3668@.TK2MSFTNGP14.phx.gbl...
> Does anyone know the purpose of sp_trace_getdata and if it is normal for
> it
> to run all the time. We have 2 production servers (mostly the same SQL
> settings) so I am not sure why this is constantly running on one server
> and
> not the other?
> thanks
> Meenal
>

Permissions to run DBCC SHOWFILESTATS

All,
We have a custom app (that unfortunately can't be modified any time soon)
that uses the undocumented command "DBCC SHOWFILESTATS" to get data file
information. However, we need to allow non-admins/dbowners to use this
function. As I am not an SQL Server Admin, I haven't the faintest idea
how to allow this. I've tried GRANTing the right with no luck. Can anyone
help this poor schmoe out and tell me exactly how to grant execute rights
for DBCC SHOWFILESTATS on a given database (let's say Northwind for
example)?
Many thanks in advance.
--
--Sasquatch
"And I thought 'Reverend Billy ...', you know, which is good 'cause when I
think 'Reverend Debra ...', that's another set of problems altogether."
-- Rev. Billy C. WirtzTry Assigning System administrator role to that user (who
has to perform the dbcc showfilestats command).
>--Original Message--
>All,
>We have a custom app (that unfortunately can't be
modified any time soon)
>that uses the undocumented command "DBCC SHOWFILESTATS"
to get data file
>information. However, we need to allow non-
admins/dbowners to use this
>function. As I am not an SQL Server Admin, I haven't the
faintest idea
>how to allow this. I've tried GRANTing the right with no
luck. Can anyone
>help this poor schmoe out and tell me exactly how to
grant execute rights
>for DBCC SHOWFILESTATS on a given database (let's say
Northwind for
>example)?
>Many thanks in advance.
>--
>--Sasquatch
>"And I thought 'Reverend Billy ...', you know, which is
good 'cause when I
> think 'Reverend Debra ...', that's another set of
problems altogether."
> -- Rev. Billy C. Wirtz
>.
>|||Hi Sasquatch
There are many commands for which permission cannot be granted; many DBCC
commands, including this one, are in that category.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sasquatch" <me@.nowhereinparticular.com> wrote in message
news:vroctgnrlcqt5b@.corp.supernews.com...
> All,
> We have a custom app (that unfortunately can't be modified any time soon)
> that uses the undocumented command "DBCC SHOWFILESTATS" to get data file
> information. However, we need to allow non-admins/dbowners to use this
> function. As I am not an SQL Server Admin, I haven't the faintest idea
> how to allow this. I've tried GRANTing the right with no luck. Can anyone
> help this poor schmoe out and tell me exactly how to grant execute rights
> for DBCC SHOWFILESTATS on a given database (let's say Northwind for
> example)?
> Many thanks in advance.
> --
> --Sasquatch
> "And I thought 'Reverend Billy ...', you know, which is good 'cause when I
> think 'Reverend Debra ...', that's another set of problems altogether."
> -- Rev. Billy C. Wirtz|||Sasquatch
Please do not post the same question simultaneously in multiple newsgroups.
This has been answered in another group already.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sasquatch" <me@.nowhereinparticular.com> wrote in message
news:vroctgnrlcqt5b@.corp.supernews.com...
> All,
> We have a custom app (that unfortunately can't be modified any time soon)
> that uses the undocumented command "DBCC SHOWFILESTATS" to get data file
> information. However, we need to allow non-admins/dbowners to use this
> function. As I am not an SQL Server Admin, I haven't the faintest idea
> how to allow this. I've tried GRANTing the right with no luck. Can anyone
> help this poor schmoe out and tell me exactly how to grant execute rights
> for DBCC SHOWFILESTATS on a given database (let's say Northwind for
> example)?
> Many thanks in advance.
> --
> --Sasquatch
> "And I thought 'Reverend Billy ...', you know, which is good 'cause when I
> think 'Reverend Debra ...', that's another set of problems altogether."
> -- Rev. Billy C. Wirtz|||Mangai
This is NOT recommended if you care at all about security and integrity of
your database. Putting someone in the sysadmin role gives them far too much
privilege, and that role should be restricted to those who really need full
privilege.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mangai" <anonymous@.discussions.microsoft.com> wrote in message
news:06ac01c3af25$5e739630$a501280a@.phx.gbl...
> Try Assigning System administrator role to that user (who
> has to perform the dbcc showfilestats command).
> >--Original Message--
> >All,
> >
> >We have a custom app (that unfortunately can't be
> modified any time soon)
> >that uses the undocumented command "DBCC SHOWFILESTATS"
> to get data file
> >information. However, we need to allow non-
> admins/dbowners to use this
> >function. As I am not an SQL Server Admin, I haven't the
> faintest idea
> >how to allow this. I've tried GRANTing the right with no
> luck. Can anyone
> >help this poor schmoe out and tell me exactly how to
> grant execute rights
> >for DBCC SHOWFILESTATS on a given database (let's say
> Northwind for
> >example)?
> >
> >Many thanks in advance.
> >
> >--
> >--Sasquatch
> >
> >"And I thought 'Reverend Billy ...', you know, which is
> good 'cause when I
> > think 'Reverend Debra ...', that's another set of
> problems altogether."
> > -- Rev. Billy C. Wirtz
> >.
> >|||You can use
select
fileid,
groupid,
size/8 as TotalExtents,
fileproperty(name,'SpaceUsed')/8 as UsedExtents,
name,
filename
from sysfiles
where groupid <> 0
to give you the same output as DBCC SHOWFILESTATS. You
only need public access to run this. You can comment out
the where clause to give you figures for the log files as
well.
As you can't change the app, this probably doesn't help.
>--Original Message--
>All,
>We have a custom app (that unfortunately can't be
modified any time soon)
>that uses the undocumented command "DBCC SHOWFILESTATS"
to get data file
>information. However, we need to allow non-
admins/dbowners to use this
>function. As I am not an SQL Server Admin, I haven't the
faintest idea
>how to allow this. I've tried GRANTing the right with no
luck. Can anyone
>help this poor schmoe out and tell me exactly how to
grant execute rights
>for DBCC SHOWFILESTATS on a given database (let's say
Northwind for
>example)?
>Many thanks in advance.
>--
>--Sasquatch
>"And I thought 'Reverend Billy ...', you know, which is
good 'cause when I
> think 'Reverend Debra ...', that's another set of
problems altogether."
> -- Rev. Billy C. Wirtz
>.
>

permissions not 'sticking' with Yukon June CTP?

Hi,
I'm trying to assing permissions using the MS SQL Management studio on the
Yukon June CTP, but every time i assign permissions and press 'OK' and go
back to the permissions screen, it is gone. I am new to MSSQL -- do i need
to run a FLUSH PRIVELEGES or some equivalent command?
Thanks,
StephenStephen wrote:
> Hi,
> I'm trying to assing permissions using the MS SQL Management studio
> on the Yukon June CTP, but every time i assign permissions and press
> 'OK' and go back to the permissions screen, it is gone. I am new to
> MSSQL -- do i need to run a FLUSH PRIVELEGES or some equivalent
> command?
> Thanks,
> Stephen
Please post SQL Server 2005 issues in the 2005 newsgroups. These
newsgroups are currently for versions other than 2005.
http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
--
David Gugick
Quest Software
www.imceda.com
www.quest.comsql

permissions not 'sticking' with Yukon June CTP?

Hi,
I'm trying to assing permissions using the MS SQL Management studio on the
Yukon June CTP, but every time i assign permissions and press 'OK' and go
back to the permissions screen, it is gone. I am new to MSSQL -- do i need
to run a FLUSH PRIVELEGES or some equivalent command?
Thanks,
Stephen
Stephen wrote:
> Hi,
> I'm trying to assing permissions using the MS SQL Management studio
> on the Yukon June CTP, but every time i assign permissions and press
> 'OK' and go back to the permissions screen, it is gone. I am new to
> MSSQL -- do i need to run a FLUSH PRIVELEGES or some equivalent
> command?
> Thanks,
> Stephen
Please post SQL Server 2005 issues in the 2005 newsgroups. These
newsgroups are currently for versions other than 2005.
http://www.microsoft.com/technet/com...erver/sql.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com

permissions not 'sticking' with Yukon June CTP?

Hi,
I'm trying to assing permissions using the MS SQL Management studio on the
Yukon June CTP, but every time i assign permissions and press 'OK' and go
back to the permissions screen, it is gone. I am new to MSSQL -- do i need
to run a FLUSH PRIVELEGES or some equivalent command?
Thanks,
StephenStephen wrote:
> Hi,
> I'm trying to assing permissions using the MS SQL Management studio
> on the Yukon June CTP, but every time i assign permissions and press
> 'OK' and go back to the permissions screen, it is gone. I am new to
> MSSQL -- do i need to run a FLUSH PRIVELEGES or some equivalent
> command?
> Thanks,
> Stephen
Please post SQL Server 2005 issues in the 2005 newsgroups. These
newsgroups are currently for versions other than 2005.
http://www.microsoft.com/technet/co...server/sql.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com

Permission tracking

Hi all,
i'm facing the following situation: user rights must be granted for a very
short period of time, so that the user does his insert/update/delete job.
The problem is that there are many users who have requests and giving &
revoking permissions are taking a lot of time. Did anybody implemented an
automated tracking system, and if yes, could you give me a hint please? A
good way to implement permission tracking would be triggers but unluckily,
in Sql 2000, they can't be used on system tables...:-(
--
Tudor Sofron
MCSE, MCSA
Ipsos- NMRTudot
I am not sure what did you mean?
Do you want the users to be able INSERT/UPDATE/DELETE operations for a short
time?
What is a short time ( one an hour, two minutes) ?
I suggest you using STORED PROCEDURES for security reasons. Don't grant
access on underlying tables ,instead grant EXECUTE permissions for the users
on stored procedures that they need to be run.
"Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
news:uc9kgMXxEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> i'm facing the following situation: user rights must be granted for a very
> short period of time, so that the user does his insert/update/delete job.
> The problem is that there are many users who have requests and giving &
> revoking permissions are taking a lot of time. Did anybody implemented an
> automated tracking system, and if yes, could you give me a hint please? A
> good way to implement permission tracking would be triggers but unluckily,
> in Sql 2000, they can't be used on system tables...:-(
> --
> Tudor Sofron
> MCSE, MCSA
> Ipsos- NMR
>
>|||Well...
this is the table i've created:
CREATE TABLE [User_RightsGranted] (
[User_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[User_ID] [smallint] NULL ,
[User_SID] [varbinary] (85) NULL ,
[DB_Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[Object_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[Db_Owner] [tinyint] NULL ,
[Data_Reader] [tinyint] NULL ,
[Data_Writer] [tinyint] NULL ,
[Exec] [tinyint] NULL ,
[Select] [tinyint] NULL ,
[Insert] [tinyint] NULL ,
[Update] [tinyint] NULL ,
[Delete] [tinyint] NULL ,
[DateStart] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_DateS
tart]
DEFAULT (getdate()),
[DateEnd] [datetime] NULL ,
[OpDate] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_OpDate]
DEFAULT
(getdate()),
[OpUser] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL
CONSTRAINT [DF_User_Rights_OpUser] DEFAULT (suser_sname() + '.' +
host_name())
) ON [PRIMARY]
GO
The column names are pretty explicit so it's no need to explain their
function. The problem I have is that I can't automatize the whole process,
so I have to complete the table manually. So...did anybody faced such
problems, and if yes, how did you solved them? See my comments to your post
below...
Thanks,
Tudor Sofron
MCSE, MCSA
Ipsos- NMR
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eUVFySXxEHA.2040@.tk2msftngp13.phx.gbl...
> Tudot
> I am not sure what did you mean?
> Do you want the users to be able INSERT/UPDATE/DELETE operations for a
short
> time?
yes. I grant users INSERT/UPDATE/DELETE rights and complete the above table.
> What is a short time ( one an hour, two minutes) ?
about 30 minutes, after that I revoke the granted permissions...but I have
to do that manually...and update a similar table...

> I suggest you using STORED PROCEDURES for security reasons. Don't grant
> access on underlying tables ,instead grant EXECUTE permissions for the
users
> on stored procedures that they need to be run.
well...it's not that easy to implement the use of sp's...
>
>
> "Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
> news:uc9kgMXxEHA.1988@.TK2MSFTNGP12.phx.gbl...
very[vbcol=seagreen]
job.[vbcol=seagreen]
an[vbcol=seagreen]
A[vbcol=seagreen]
unluckily,[vbcol=seagreen]
>|||Tudor
I am sorry but the table looks like a mess.
There is no primary key, lots of colums does allow NULL's

> The problem I have is that I can't automatize the whole process,
> so I have to complete the table manually
You mean that you would like to insert into the table all DML that users do?
If so, create a trigger on this table and start to manipuilate with DELETED
and INSERTED virtual tables
In my opinion I'd create an AUDIT table that will be gathering all info
about new/old columns
Something like that
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
news:%23QH8akXxEHA.3896@.TK2MSFTNGP10.phx.gbl...
> Well...
> this is the table i've created:
> CREATE TABLE [User_RightsGranted] (
> [User_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

,
> [User_ID] [smallint] NULL ,
> [User_SID] [varbinary] (85) NULL ,
> [DB_Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
> [Object_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [Db_Owner] [tinyint] NULL ,
> [Data_Reader] [tinyint] NULL ,
> [Data_Writer] [tinyint] NULL ,
> [Exec] [tinyint] NULL ,
> [Select] [tinyint] NULL ,
> [Insert] [tinyint] NULL ,
> [Update] [tinyint] NULL ,
> [Delete] [tinyint] NULL ,
> [DateStart] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_Da
teStart]
> DEFAULT (getdate()),
> [DateEnd] [datetime] NULL ,
> [OpDate] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_OpDat
e] DEFAULT
> (getdate()),
> [OpUser] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> CONSTRAINT [DF_User_Rights_OpUser] DEFAULT (suser_sname() + '.' +
> host_name())
> ) ON [PRIMARY]
> GO
> The column names are pretty explicit so it's no need to explain their
> function. The problem I have is that I can't automatize the whole process,
> so I have to complete the table manually. So...did anybody faced such
> problems, and if yes, how did you solved them? See my comments to your
post
> below...
> Thanks,
> Tudor Sofron
> MCSE, MCSA
> Ipsos- NMR
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eUVFySXxEHA.2040@.tk2msftngp13.phx.gbl...
> short
> yes. I grant users INSERT/UPDATE/DELETE rights and complete the above
table.
> about 30 minutes, after that I revoke the granted permissions...but I have
> to do that manually...and update a similar table...
>
grant[vbcol=seagreen]
> users
> well...it's not that easy to implement the use of sp's...
> very
> job.
&[vbcol=seagreen]
> an
please?[vbcol=seagreen]
> A
> unluckily,
>|||well...the table design is in 'development phase' :-)...but i hope that soon
this will be done.
Tudor Sofron
MCSE, MCSA
Ipsos- NMR
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuqlkwXxEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Tudor
> I am sorry but the table looks like a mess.
> There is no primary key, lots of colums does allow NULL's
>
>
> You mean that you would like to insert into the table all DML that users
do?
> If so, create a trigger on this table and start to manipuilate with
DELETED
> and INSERTED virtual tables
> In my opinion I'd create an AUDIT table that will be gathering all info
> about new/old columns
> Something like that
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
>
>
> "Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
> news:%23QH8akXxEHA.3896@.TK2MSFTNGP10.phx.gbl...
NULL[vbcol=seagreen]
> ,
,[vbcol=seagreen]
,[vbcol=seagreen]
process,[vbcol=seagreen]
> post
> table.
have[vbcol=seagreen]
> grant
a[vbcol=seagreen]
giving[vbcol=seagreen]
> &
implemented[vbcol=seagreen]
> please?
>

Permission Denied error

Windows Pro
VS.Net
.Net Framework
ASP.NET
My 1st time using Web Forms in .Net
1st time using SQL
I am receiving the following error:
System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in
database 'master'. Could not attach database 'pubs' to file 'D:\Program
Files\Microsoft SQL Server\MSSQL$NETSDK\Data\pubs.mdf'.
I tried the following:
Reboot 4-5 times
Share the folder
Web share the folder
Here is some advanced help from another SQL forum.
sp_helpsrvrolemember 'sysadmin' --> command is not on my box.
isql /usa/p/server\sdk -i\instpubs.sql --> did not work. isql is not a
command found on my box.
Any ideas?
Thanks for your help,
THello,
What are you trying to do here? Can you post the commands
To create databases you need system administrator rights and dbcreator
Permissions (from BOL)
CREATE DATABASE permission defaults to members of the sysadmin and dbcreator
fixed server roles. Members of the sysadmin and securityadmin fixed server
roles can grant CREATE DATABASE permissions to other logins. Members of the
sysadmin and dbcreator fixed server role can add other logins to the
dbcreator role. The CREATE DATABASE permission must be explicitly granted;
it is not granted by the GRANT ALL statement.
CREATE DATABASE permission is usually limited to a few logins to maintain
control over disk usage on an instance of SQL Server.
I wouldn't suggest granting the owner of a .NET IIS app either of these
rights
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.aspx. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Taishi" <taishi_bak@.hotmail.com> wrote in message
news:eJuJfV16DHA.360@.TK2MSFTNGP12.phx.gbl...
> Windows Pro
> VS.Net
> .Net Framework
> ASP.NET
> My 1st time using Web Forms in .Net
> 1st time using SQL
> I am receiving the following error:
> System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in
> database 'master'. Could not attach database 'pubs' to file 'D:\Program
> Files\Microsoft SQL Server\MSSQL$NETSDK\Data\pubs.mdf'.
> I tried the following:
> Reboot 4-5 times
> Share the folder
> Web share the folder
> Here is some advanced help from another SQL forum.
> sp_helpsrvrolemember 'sysadmin' --> command is not on my box.
> isql /usa/p/server\sdk -i\instpubs.sql --> did not work. isql is not a
> command found on my box.
>
> Any ideas?
> Thanks for your help,
> T
>|||How can I check the permissions/rights?
How can I grant the correct permissions/rights?
Thanks,
T
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
message news:udfB$CE7DHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hello,
> What are you trying to do here? Can you post the commands
> To create databases you need system administrator rights and dbcreator
> Permissions (from BOL)
> CREATE DATABASE permission defaults to members of the sysadmin and
dbcreator
> fixed server roles. Members of the sysadmin and securityadmin fixed server
> roles can grant CREATE DATABASE permissions to other logins. Members of
the
> sysadmin and dbcreator fixed server role can add other logins to the
> dbcreator role. The CREATE DATABASE permission must be explicitly granted;
> it is not granted by the GRANT ALL statement.
> CREATE DATABASE permission is usually limited to a few logins to maintain
> control over disk usage on an instance of SQL Server.
> I wouldn't suggest granting the owner of a .NET IIS app either of these
> rights
>
> --
> I hope this helps
> regards
> Greg O MCSD
> http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe
Documentation
> Builder, the quickest way to document your database
> http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
> Property Extended properties manager for SQL 2000
> http://www.ag-software.com/IconExtractionProgram.aspx. Free icon
extraction
> program
> http://www.ag-software.com. Free programming tools
>
>
> "Taishi" <taishi_bak@.hotmail.com> wrote in message
> news:eJuJfV16DHA.360@.TK2MSFTNGP12.phx.gbl...
> > Windows Pro
> > VS.Net
> > .Net Framework
> > ASP.NET
> > My 1st time using Web Forms in .Net
> > 1st time using SQL
> > I am receiving the following error:
> >
> > System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in
> > database 'master'. Could not attach database 'pubs' to file 'D:\Program
> > Files\Microsoft SQL Server\MSSQL$NETSDK\Data\pubs.mdf'.
> >
> > I tried the following:
> >
> > Reboot 4-5 times
> > Share the folder
> > Web share the folder
> >
> > Here is some advanced help from another SQL forum.
> > sp_helpsrvrolemember 'sysadmin' --> command is not on my box.
> > isql /usa/p/server\sdk -i\instpubs.sql --> did not work. isql is not
a
> > command found on my box.
> >
> >
> > Any ideas?
> >
> > Thanks for your help,
> > T
> >
> >
>|||Hi Tashi!
You can ue Enterprise manager to set the rights. Locate the table, and right
click. Select All Tasks, then Manage Permissions. Locate the user that you
are using in your connection object, and then grant the rights. I agree with
Greg that the web user should not normally have that level of permission.
One false move and your server and data could be toast. If you are really
trying to create a table, or attach a database, you should be doing that
through Enterprise Manager, and not the web interface.
Sloan
"Taishi" <taishi_bak@.hotmail.com> wrote in message
news:u54GDcN7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> How can I check the permissions/rights?
> How can I grant the correct permissions/rights?
> Thanks,
> T
> "Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
> message news:udfB$CE7DHA.2568@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> > What are you trying to do here? Can you post the commands
> >
> > To create databases you need system administrator rights and dbcreator
> >
> > Permissions (from BOL)
> > CREATE DATABASE permission defaults to members of the sysadmin and
> dbcreator
> > fixed server roles. Members of the sysadmin and securityadmin fixed
server
> > roles can grant CREATE DATABASE permissions to other logins. Members of
> the
> > sysadmin and dbcreator fixed server role can add other logins to the
> > dbcreator role. The CREATE DATABASE permission must be explicitly
granted;
> > it is not granted by the GRANT ALL statement.
> >
> > CREATE DATABASE permission is usually limited to a few logins to
maintain
> > control over disk usage on an instance of SQL Server.
> >
> > I wouldn't suggest granting the owner of a .NET IIS app either of these
> > rights
> >
> >
> > --
> > I hope this helps
> > regards
> > Greg O MCSD
> > http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe
> Documentation
> > Builder, the quickest way to document your database
> > http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server
Extended
> > Property Extended properties manager for SQL 2000
> > http://www.ag-software.com/IconExtractionProgram.aspx. Free icon
> extraction
> > program
> > http://www.ag-software.com. Free programming tools
> >
> >
> >
> >
> > "Taishi" <taishi_bak@.hotmail.com> wrote in message
> > news:eJuJfV16DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > Windows Pro
> > > VS.Net
> > > .Net Framework
> > > ASP.NET
> > > My 1st time using Web Forms in .Net
> > > 1st time using SQL
> > > I am receiving the following error:
> > >
> > > System.Data.SqlClient.SqlException: CREATE DATABASE permission denied
in
> > > database 'master'. Could not attach database 'pubs' to file
'D:\Program
> > > Files\Microsoft SQL Server\MSSQL$NETSDK\Data\pubs.mdf'.
> > >
> > > I tried the following:
> > >
> > > Reboot 4-5 times
> > > Share the folder
> > > Web share the folder
> > >
> > > Here is some advanced help from another SQL forum.
> > > sp_helpsrvrolemember 'sysadmin' --> command is not on my box.
> > > isql /usa/p/server\sdk -i\instpubs.sql --> did not work. isql is
not
> a
> > > command found on my box.
> > >
> > >
> > > Any ideas?
> > >
> > > Thanks for your help,
> > > T
> > >
> > >
> >
> >
>|||Sloan,
I found the following url.
http://www.aspenterprisemanager.com/
Is Enterprise Manager free?
If so, where can I download it?
Thanks so much for the help,
T.
"Sloan Thrasher" <cst2000@.comcast.net> wrote in message
news:cuQUb.232283$I06.2592550@.attbi_s01...
> Hi Tashi!
> You can ue Enterprise manager to set the rights. Locate the table, and
right
> click. Select All Tasks, then Manage Permissions. Locate the user that you
> are using in your connection object, and then grant the rights. I agree
with
> Greg that the web user should not normally have that level of permission.
> One false move and your server and data could be toast. If you are really
> trying to create a table, or attach a database, you should be doing that
> through Enterprise Manager, and not the web interface.
> Sloan
> "Taishi" <taishi_bak@.hotmail.com> wrote in message
> news:u54GDcN7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > How can I check the permissions/rights?
> >
> > How can I grant the correct permissions/rights?
> >
> > Thanks,
> > T
> >
> > "Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
> > message news:udfB$CE7DHA.2568@.TK2MSFTNGP10.phx.gbl...
> > > Hello,
> > > What are you trying to do here? Can you post the commands
> > >
> > > To create databases you need system administrator rights and dbcreator
> > >
> > > Permissions (from BOL)
> > > CREATE DATABASE permission defaults to members of the sysadmin and
> > dbcreator
> > > fixed server roles. Members of the sysadmin and securityadmin fixed
> server
> > > roles can grant CREATE DATABASE permissions to other logins. Members
of
> > the
> > > sysadmin and dbcreator fixed server role can add other logins to the
> > > dbcreator role. The CREATE DATABASE permission must be explicitly
> granted;
> > > it is not granted by the GRANT ALL statement.
> > >
> > > CREATE DATABASE permission is usually limited to a few logins to
> maintain
> > > control over disk usage on an instance of SQL Server.
> > >
> > > I wouldn't suggest granting the owner of a .NET IIS app either of
these
> > > rights
> > >
> > >
> > > --
> > > I hope this helps
> > > regards
> > > Greg O MCSD
> > > http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe
> > Documentation
> > > Builder, the quickest way to document your database
> > > http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server
> Extended
> > > Property Extended properties manager for SQL 2000
> > > http://www.ag-software.com/IconExtractionProgram.aspx. Free icon
> > extraction
> > > program
> > > http://www.ag-software.com. Free programming tools
> > >
> > >
> > >
> > >
> > > "Taishi" <taishi_bak@.hotmail.com> wrote in message
> > > news:eJuJfV16DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > > Windows Pro
> > > > VS.Net
> > > > .Net Framework
> > > > ASP.NET
> > > > My 1st time using Web Forms in .Net
> > > > 1st time using SQL
> > > > I am receiving the following error:
> > > >
> > > > System.Data.SqlClient.SqlException: CREATE DATABASE permission
denied
> in
> > > > database 'master'. Could not attach database 'pubs' to file
> 'D:\Program
> > > > Files\Microsoft SQL Server\MSSQL$NETSDK\Data\pubs.mdf'.
> > > >
> > > > I tried the following:
> > > >
> > > > Reboot 4-5 times
> > > > Share the folder
> > > > Web share the folder
> > > >
> > > > Here is some advanced help from another SQL forum.
> > > > sp_helpsrvrolemember 'sysadmin' --> command is not on my box.
> > > > isql /usa/p/server\sdk -i\instpubs.sql --> did not work. isql is
> not
> > a
> > > > command found on my box.
> > > >
> > > >
> > > > Any ideas?
> > > >
> > > > Thanks for your help,
> > > > T
> > > >
> > > >
> > >
> > >
> >
> >
>|||You can use MSDE and Access as a backend for a website.
I am uninitiated but I know this for a fact.
"Sloan Thrasher" <cst2000@.comcast.net> wrote in message
news:b3FVb.259176$na.418905@.attbi_s04...
> Hi Tashi!
> I haven't installed MSDE, but it should have asked for a sa password
during
> installation. If so, and you remember it, you could use asp to connect and
> add the DBs you need, but definately not something for the uninitiated.
> Also, I'm not sure, but I don't think you're allowed to use MSDE as a
> backend for a web site.
> Since you're new to ASP.net, SQL, etc. you might want to start out with
> something a bit simplier, like an Access DB (if you have MS Office, you
have
> Access) That way you have a GUI to create your database and tables in and
> you can focus on one thing at a time.
> If you really want to move forward with MSDE, then look at this link:
>
http://msdn.microsoft.com/library/?url=/library/en-us/distsql/distsql_84xl.a
> sp?frame=true
> The topic is Customizing MDSE Setup.exe. In there you will see how to
setup
> the SA password, the default directory for your data files, and a lot
more.
> I found it by D/L the app and the HTML readme file.
> Sloan
>
> "Taishi" <taishi_bak@.hotmail.com> wrote in message
> news:#zV2Aes7DHA.3804@.tk2msftngp13.phx.gbl...
> > Sloan,
> >
> > I installed the MSDE 2000 from the following web site:
> >
> > ww.microsoft.com/downloads
> >
> > Featured download
> > Microsoft SQL Server 2000
> > Desktop Engine(MSDE 2000) Release A
> >
> > I don't have the SQL server disks.
> >
> > Any ideas?
> >
> > Thanks,
> > T.
> >
> > "Sloan Thrasher" <cst2000@.comcast.net> wrote in message
> > news:J1EVb.125043$U%5.607067@.attbi_s03...
> > > Hi Tashi!
> > >
> > > Enterprise Manager comes with SQL Server. You should be able to
install
> it
> > > from the MS SQL Server CDs
> > >
> > > Sloan
> > >
>