Hi All
I want to prevent windows users from accessing my database on SQL server
express 2005
I don't want users to be able to login with SSME on Windows authentication,
only by SQl Server Authentication and only on the sa and another specific
login with the password I have set
For the life of me I can't seem to find how to do this
Can anybody advise me
Regards
SteveSteve (ga630sf@.newsgroups.nospam) writes:
> I want to prevent windows users from accessing my database on SQL server
> express 2005
> I don't want users to be able to login with SSME on Windows
> authentication, only by SQl Server Authentication and only on the sa and
> another specific login with the password I have set
> For the life of me I can't seem to find how to do this
First of all, if you granted access to any Windows login or groups,
remove these. Second, also revoke access to BUILTIN\Administrators,
which gives permission to all Windows logins that have admin rights
on the machine.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Steve
I think the following has been introduced in SP2 and I did not test on
Express Edition ,sorry
/*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
Issue a rollback*/
for logon
as begin
if exists (select * from sys.server_principals
where type_desc ='Windows_Login'
and name=original_login() )
begin
ROLLBACK;
end
end
go
For more details please
"Steve" <ga630sf@.newsgroups.nospam> wrote in message
news:edw2AkkzHHA.4004@.TK2MSFTNGP05.phx.gbl...
> Hi All
> I want to prevent windows users from accessing my database on SQL server
> express 2005
> I don't want users to be able to login with SSME on Windows
> authentication, only by SQl Server Authentication and only on the sa and
> another specific login with the password I have set
> For the life of me I can't seem to find how to do this
> Can anybody advise me
>
> Regards
> Steve
>|||Erland
Thanks worked a treat
Regards
steve
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99786FED46931Yazorman@.127.0.0.1...
> Steve (ga630sf@.newsgroups.nospam) writes:
> First of all, if you granted access to any Windows login or groups,
> remove these. Second, also revoke access to BUILTIN\Administrators,
> which gives permission to all Windows logins that have admin rights
> on the machine.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label preventing. Show all posts
Showing posts with label preventing. Show all posts
Saturday, February 25, 2012
preventing update thru view
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs
Hi
Yes, you can
As far as I know there are two ways to accomplish that
1) CREATE VIEW ... WITH VIEW_METADATA
2) CREATE TRIGGER ...INSTEAD OF UPDATE
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
> Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
> thks & rdgs
|||On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
Hi Maxzsim,
CREATE TRIGGER DontUpdate
ON MyView
INSTEAD OF INSERT, UPDATE, DELETE
AS
RAISERROR ('This view is read-only', 16, 1)
ROLLBACK TRANSACTION
go
Note: the rollback isn't even necessary, as this trigger is defined as an
"instead of" trigger. Without the rollback, the attempt to update the view
will be disregarded but the rest of the transaction will stick; with the
rollback, the complete transaction will be rolled back. To see this
difference, try the following code with both versions of the trigger:
BEGIN TRANSACTION
UPDATE SomeOtherTable
SET SomeThing = SomeThingElse
WHERE Whatever = WhatYouLike
UPDATE MyView
SET YouNameIt = YouGotIt
WHERE Foo = Bar
COMMIT TRANSACTION
SELECT SomeThing
FROM SomeOtherTable
WHERE Whatever = WhatYouLike
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo
If you have a big update transaction I would not use a trigger with
rollback.
Instead
create table t1 (col1 int,col2 int)
insert into t1 values (1,11)
insert into t1 values (8,10)
select * from t1
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1+0 AS col1,
col2+0 AS col2
FROM T1
select * from v1
--error
update v1 set col1=100 where col2=11
go
drop table t1
drop view v1
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5ll8i0tvdhfblupp7cbfoaakaqaj1jn4rb@.4ax.com...
> On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>
> Hi Maxzsim,
> CREATE TRIGGER DontUpdate
> ON MyView
> INSTEAD OF INSERT, UPDATE, DELETE
> AS
> RAISERROR ('This view is read-only', 16, 1)
> ROLLBACK TRANSACTION
> go
> Note: the rollback isn't even necessary, as this trigger is defined as an
> "instead of" trigger. Without the rollback, the attempt to update the view
> will be disregarded but the rest of the transaction will stick; with the
> rollback, the complete transaction will be rolled back. To see this
> difference, try the following code with both versions of the trigger:
> BEGIN TRANSACTION
> UPDATE SomeOtherTable
> SET SomeThing = SomeThingElse
> WHERE Whatever = WhatYouLike
> UPDATE MyView
> SET YouNameIt = YouGotIt
> WHERE Foo = Bar
> COMMIT TRANSACTION
> SELECT SomeThing
> FROM SomeOtherTable
> WHERE Whatever = WhatYouLike
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||How about using permissions, to control access to this view. You can have a
view, and grant only SELECT permissions on that view to your users.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs
Hi
Yes, you can
As far as I know there are two ways to accomplish that
1) CREATE VIEW ... WITH VIEW_METADATA
2) CREATE TRIGGER ...INSTEAD OF UPDATE
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
> Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
> thks & rdgs
|||On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
Hi Maxzsim,
CREATE TRIGGER DontUpdate
ON MyView
INSTEAD OF INSERT, UPDATE, DELETE
AS
RAISERROR ('This view is read-only', 16, 1)
ROLLBACK TRANSACTION
go
Note: the rollback isn't even necessary, as this trigger is defined as an
"instead of" trigger. Without the rollback, the attempt to update the view
will be disregarded but the rest of the transaction will stick; with the
rollback, the complete transaction will be rolled back. To see this
difference, try the following code with both versions of the trigger:
BEGIN TRANSACTION
UPDATE SomeOtherTable
SET SomeThing = SomeThingElse
WHERE Whatever = WhatYouLike
UPDATE MyView
SET YouNameIt = YouGotIt
WHERE Foo = Bar
COMMIT TRANSACTION
SELECT SomeThing
FROM SomeOtherTable
WHERE Whatever = WhatYouLike
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo
If you have a big update transaction I would not use a trigger with
rollback.
Instead
create table t1 (col1 int,col2 int)
insert into t1 values (1,11)
insert into t1 values (8,10)
select * from t1
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1+0 AS col1,
col2+0 AS col2
FROM T1
select * from v1
--error
update v1 set col1=100 where col2=11
go
drop table t1
drop view v1
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5ll8i0tvdhfblupp7cbfoaakaqaj1jn4rb@.4ax.com...
> On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>
> Hi Maxzsim,
> CREATE TRIGGER DontUpdate
> ON MyView
> INSTEAD OF INSERT, UPDATE, DELETE
> AS
> RAISERROR ('This view is read-only', 16, 1)
> ROLLBACK TRANSACTION
> go
> Note: the rollback isn't even necessary, as this trigger is defined as an
> "instead of" trigger. Without the rollback, the attempt to update the view
> will be disregarded but the rest of the transaction will stick; with the
> rollback, the complete transaction will be rolled back. To see this
> difference, try the following code with both versions of the trigger:
> BEGIN TRANSACTION
> UPDATE SomeOtherTable
> SET SomeThing = SomeThingElse
> WHERE Whatever = WhatYouLike
> UPDATE MyView
> SET YouNameIt = YouGotIt
> WHERE Foo = Bar
> COMMIT TRANSACTION
> SELECT SomeThing
> FROM SomeOtherTable
> WHERE Whatever = WhatYouLike
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||How about using permissions, to control access to this view. You can have a
view, and grant only SELECT permissions on that view to your users.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs
preventing update thru view
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgsHi
Yes, you can
As far as I know there are two ways to accomplish that
1) CREATE VIEW ... WITH VIEW_METADATA
2) CREATE TRIGGER ...INSTEAD OF UPDATE
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
> Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
> thks & rdgs|||On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
Hi Maxzsim,
CREATE TRIGGER DontUpdate
ON MyView
INSTEAD OF INSERT, UPDATE, DELETE
AS
RAISERROR ('This view is read-only', 16, 1)
ROLLBACK TRANSACTION
go
Note: the rollback isn't even necessary, as this trigger is defined as an
"instead of" trigger. Without the rollback, the attempt to update the view
will be disregarded but the rest of the transaction will stick; with the
rollback, the complete transaction will be rolled back. To see this
difference, try the following code with both versions of the trigger:
BEGIN TRANSACTION
UPDATE SomeOtherTable
SET SomeThing = SomeThingElse
WHERE Whatever = WhatYouLike
UPDATE MyView
SET YouNameIt = YouGotIt
WHERE Foo = Bar
COMMIT TRANSACTION
SELECT SomeThing
FROM SomeOtherTable
WHERE Whatever = WhatYouLike
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
If you have a big update transaction I would not use a trigger with
rollback.
Instead
create table t1 (col1 int,col2 int)
insert into t1 values (1,11)
insert into t1 values (8,10)
select * from t1
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1+0 AS col1,
col2+0 AS col2
FROM T1
select * from v1
--error
update v1 set col1=100 where col2=11
go
drop table t1
drop view v1
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5ll8i0tvdhfblupp7cbfoaakaqaj1jn4rb@.
4ax.com...
> On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>
> Hi Maxzsim,
> CREATE TRIGGER DontUpdate
> ON MyView
> INSTEAD OF INSERT, UPDATE, DELETE
> AS
> RAISERROR ('This view is read-only', 16, 1)
> ROLLBACK TRANSACTION
> go
> Note: the rollback isn't even necessary, as this trigger is defined as an
> "instead of" trigger. Without the rollback, the attempt to update the view
> will be disregarded but the rest of the transaction will stick; with the
> rollback, the complete transaction will be rolled back. To see this
> difference, try the following code with both versions of the trigger:
> BEGIN TRANSACTION
> UPDATE SomeOtherTable
> SET SomeThing = SomeThingElse
> WHERE Whatever = WhatYouLike
> UPDATE MyView
> SET YouNameIt = YouGotIt
> WHERE Foo = Bar
> COMMIT TRANSACTION
> SELECT SomeThing
> FROM SomeOtherTable
> WHERE Whatever = WhatYouLike
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||How about using permissions, to control access to this view. You can have a
view, and grant only SELECT permissions on that view to your users.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgsHi
Yes, you can
As far as I know there are two ways to accomplish that
1) CREATE VIEW ... WITH VIEW_METADATA
2) CREATE TRIGGER ...INSTEAD OF UPDATE
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
> Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
> thks & rdgs|||On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
Hi Maxzsim,
CREATE TRIGGER DontUpdate
ON MyView
INSTEAD OF INSERT, UPDATE, DELETE
AS
RAISERROR ('This view is read-only', 16, 1)
ROLLBACK TRANSACTION
go
Note: the rollback isn't even necessary, as this trigger is defined as an
"instead of" trigger. Without the rollback, the attempt to update the view
will be disregarded but the rest of the transaction will stick; with the
rollback, the complete transaction will be rolled back. To see this
difference, try the following code with both versions of the trigger:
BEGIN TRANSACTION
UPDATE SomeOtherTable
SET SomeThing = SomeThingElse
WHERE Whatever = WhatYouLike
UPDATE MyView
SET YouNameIt = YouGotIt
WHERE Foo = Bar
COMMIT TRANSACTION
SELECT SomeThing
FROM SomeOtherTable
WHERE Whatever = WhatYouLike
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
If you have a big update transaction I would not use a trigger with
rollback.
Instead
create table t1 (col1 int,col2 int)
insert into t1 values (1,11)
insert into t1 values (8,10)
select * from t1
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1+0 AS col1,
col2+0 AS col2
FROM T1
select * from v1
--error
update v1 set col1=100 where col2=11
go
drop table t1
drop view v1
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5ll8i0tvdhfblupp7cbfoaakaqaj1jn4rb@.
4ax.com...
> On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>
> Hi Maxzsim,
> CREATE TRIGGER DontUpdate
> ON MyView
> INSTEAD OF INSERT, UPDATE, DELETE
> AS
> RAISERROR ('This view is read-only', 16, 1)
> ROLLBACK TRANSACTION
> go
> Note: the rollback isn't even necessary, as this trigger is defined as an
> "instead of" trigger. Without the rollback, the attempt to update the view
> will be disregarded but the rest of the transaction will stick; with the
> rollback, the complete transaction will be rolled back. To see this
> difference, try the following code with both versions of the trigger:
> BEGIN TRANSACTION
> UPDATE SomeOtherTable
> SET SomeThing = SomeThingElse
> WHERE Whatever = WhatYouLike
> UPDATE MyView
> SET YouNameIt = YouGotIt
> WHERE Foo = Bar
> COMMIT TRANSACTION
> SELECT SomeThing
> FROM SomeOtherTable
> WHERE Whatever = WhatYouLike
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||How about using permissions, to control access to this view. You can have a
view, and grant only SELECT permissions on that view to your users.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs
preventing update thru view
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgsHi
Yes, you can
As far as I know there are two ways to accomplish that
1) CREATE VIEW ... WITH VIEW_METADATA
2) CREATE TRIGGER ...INSTEAD OF UPDATE
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
> Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
> thks & rdgs|||On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
Hi Maxzsim,
CREATE TRIGGER DontUpdate
ON MyView
INSTEAD OF INSERT, UPDATE, DELETE
AS
RAISERROR ('This view is read-only', 16, 1)
ROLLBACK TRANSACTION
go
Note: the rollback isn't even necessary, as this trigger is defined as an
"instead of" trigger. Without the rollback, the attempt to update the view
will be disregarded but the rest of the transaction will stick; with the
rollback, the complete transaction will be rolled back. To see this
difference, try the following code with both versions of the trigger:
BEGIN TRANSACTION
UPDATE SomeOtherTable
SET SomeThing = SomeThingElse
WHERE Whatever = WhatYouLike
UPDATE MyView
SET YouNameIt = YouGotIt
WHERE Foo = Bar
COMMIT TRANSACTION
SELECT SomeThing
FROM SomeOtherTable
WHERE Whatever = WhatYouLike
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>--Original Message--
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
>.
>|||Hugo
If you have a big update transaction I would not use a trigger with
rollback.
Instead
create table t1 (col1 int,col2 int)
insert into t1 values (1,11)
insert into t1 values (8,10)
select * from t1
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1+0 AS col1,
col2+0 AS col2
FROM T1
select * from v1
--error
update v1 set col1=100 where col2=11
go
drop table t1
drop view v1
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5ll8i0tvdhfblupp7cbfoaakaqaj1jn4rb@.4ax.com...
> On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
> >Hi ,
> >
> > A view can actually update the data of a table.
> >
> > Is there any way for me to create a read-only view ?
> >
> >thks & rdgs
> Hi Maxzsim,
> CREATE TRIGGER DontUpdate
> ON MyView
> INSTEAD OF INSERT, UPDATE, DELETE
> AS
> RAISERROR ('This view is read-only', 16, 1)
> ROLLBACK TRANSACTION
> go
> Note: the rollback isn't even necessary, as this trigger is defined as an
> "instead of" trigger. Without the rollback, the attempt to update the view
> will be disregarded but the rest of the transaction will stick; with the
> rollback, the complete transaction will be rolled back. To see this
> difference, try the following code with both versions of the trigger:
> BEGIN TRANSACTION
> UPDATE SomeOtherTable
> SET SomeThing = SomeThingElse
> WHERE Whatever = WhatYouLike
> UPDATE MyView
> SET YouNameIt = YouGotIt
> WHERE Foo = Bar
> COMMIT TRANSACTION
> SELECT SomeThing
> FROM SomeOtherTable
> WHERE Whatever = WhatYouLike
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||How about using permissions, to control access to this view. You can have a
view, and grant only SELECT permissions on that view to your users.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgsHi
Yes, you can
As far as I know there are two ways to accomplish that
1) CREATE VIEW ... WITH VIEW_METADATA
2) CREATE TRIGGER ...INSTEAD OF UPDATE
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
> Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
> thks & rdgs|||On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
Hi Maxzsim,
CREATE TRIGGER DontUpdate
ON MyView
INSTEAD OF INSERT, UPDATE, DELETE
AS
RAISERROR ('This view is read-only', 16, 1)
ROLLBACK TRANSACTION
go
Note: the rollback isn't even necessary, as this trigger is defined as an
"instead of" trigger. Without the rollback, the attempt to update the view
will be disregarded but the rest of the transaction will stick; with the
rollback, the complete transaction will be rolled back. To see this
difference, try the following code with both versions of the trigger:
BEGIN TRANSACTION
UPDATE SomeOtherTable
SET SomeThing = SomeThingElse
WHERE Whatever = WhatYouLike
UPDATE MyView
SET YouNameIt = YouGotIt
WHERE Foo = Bar
COMMIT TRANSACTION
SELECT SomeThing
FROM SomeOtherTable
WHERE Whatever = WhatYouLike
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>--Original Message--
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
>.
>|||Hugo
If you have a big update transaction I would not use a trigger with
rollback.
Instead
create table t1 (col1 int,col2 int)
insert into t1 values (1,11)
insert into t1 values (8,10)
select * from t1
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1+0 AS col1,
col2+0 AS col2
FROM T1
select * from v1
--error
update v1 set col1=100 where col2=11
go
drop table t1
drop view v1
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5ll8i0tvdhfblupp7cbfoaakaqaj1jn4rb@.4ax.com...
> On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
> >Hi ,
> >
> > A view can actually update the data of a table.
> >
> > Is there any way for me to create a read-only view ?
> >
> >thks & rdgs
> Hi Maxzsim,
> CREATE TRIGGER DontUpdate
> ON MyView
> INSTEAD OF INSERT, UPDATE, DELETE
> AS
> RAISERROR ('This view is read-only', 16, 1)
> ROLLBACK TRANSACTION
> go
> Note: the rollback isn't even necessary, as this trigger is defined as an
> "instead of" trigger. Without the rollback, the attempt to update the view
> will be disregarded but the rest of the transaction will stick; with the
> rollback, the complete transaction will be rolled back. To see this
> difference, try the following code with both versions of the trigger:
> BEGIN TRANSACTION
> UPDATE SomeOtherTable
> SET SomeThing = SomeThingElse
> WHERE Whatever = WhatYouLike
> UPDATE MyView
> SET YouNameIt = YouGotIt
> WHERE Foo = Bar
> COMMIT TRANSACTION
> SELECT SomeThing
> FROM SomeOtherTable
> WHERE Whatever = WhatYouLike
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||How about using permissions, to control access to this view. You can have a
view, and grant only SELECT permissions on that view to your users.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs
Monday, February 20, 2012
Preventing unauthorized access
I am building a windows forms VB .NET project that connects to an SQL server
database. The problem we have is that since we are using windows-based aut
hentication, anyone with access or even excel can connect to the sql server
and fiddle with the data.
I looked into application roles but using this we lose flexibility for givin
g (or removing) rights from specific users.
This problem wouldn't exist if we were building web apps, as we could set th
ings up in a way the web server would be the only one to connect to the data
base.
What do people use in enterprises to prevent users from connecting to databa
ses with unauthorized apps ?
Thanks in advance.Use only stored procedures to access the database in your application. Do
not grant any permissions on tables or views to users; only grant
permissions on stored procedures. This will effectively block access to the
database from any source except your application (or a keen user who knows
how to properly call the stored procedures).
"/dev/null" <anonymous@.discussions.microsoft.com> wrote in message
news:4AD3A344-840C-47B4-A9DE-9968BF748D0C@.microsoft.com...
> I am building a windows forms VB .NET project that connects to an SQL
server database. The problem we have is that since we are using
windows-based authentication, anyone with access or even excel can connect
to the sql server and fiddle with the data. I looked into application roles
but using this we lose flexibility for giving (or removing) rights from
specific users.
> This problem wouldn't exist if we were building web apps, as we could set
things up in a way the web server would be the only one to connect to the
database.
> What do people use in enterprises to prevent users from connecting to
databases with unauthorized apps ?
> Thanks in advance.
database. The problem we have is that since we are using windows-based aut
hentication, anyone with access or even excel can connect to the sql server
and fiddle with the data.
I looked into application roles but using this we lose flexibility for givin
g (or removing) rights from specific users.
This problem wouldn't exist if we were building web apps, as we could set th
ings up in a way the web server would be the only one to connect to the data
base.
What do people use in enterprises to prevent users from connecting to databa
ses with unauthorized apps ?
Thanks in advance.Use only stored procedures to access the database in your application. Do
not grant any permissions on tables or views to users; only grant
permissions on stored procedures. This will effectively block access to the
database from any source except your application (or a keen user who knows
how to properly call the stored procedures).
"/dev/null" <anonymous@.discussions.microsoft.com> wrote in message
news:4AD3A344-840C-47B4-A9DE-9968BF748D0C@.microsoft.com...
> I am building a windows forms VB .NET project that connects to an SQL
server database. The problem we have is that since we are using
windows-based authentication, anyone with access or even excel can connect
to the sql server and fiddle with the data. I looked into application roles
but using this we lose flexibility for giving (or removing) rights from
specific users.
> This problem wouldn't exist if we were building web apps, as we could set
things up in a way the web server would be the only one to connect to the
database.
> What do people use in enterprises to prevent users from connecting to
databases with unauthorized apps ?
> Thanks in advance.
Preventing the loading of duplicate data to a table - Best Option
Start at the file level; sort it, and scrub it with a 3GL program
before you load it. I would also look into Sunopsis. This is an ELT
tool -- it uses native SQL tools to move data rather than adding yet
another ETL language on top of everything.Thanks, I appreciate the tip; however, the constraints of the project
require that this be done in SQL Server. I am interested to know if this
(Primary Key or Unique Constraint) is the best way to do this within the
restrictions that are in place.
*** Sent via Developersdex http://www.examnotes.net ***|||A PK or UNIQUE constraint is the obvious way to generate the exception
condition. It's probably useful also to report the invalid data so you
may want to load to a staging table, without the constraint, and then
write a query using HAVING COUNT(*)>1 to find the duplicate rows.
David Portas
SQL Server MVP
--
before you load it. I would also look into Sunopsis. This is an ELT
tool -- it uses native SQL tools to move data rather than adding yet
another ETL language on top of everything.Thanks, I appreciate the tip; however, the constraints of the project
require that this be done in SQL Server. I am interested to know if this
(Primary Key or Unique Constraint) is the best way to do this within the
restrictions that are in place.
*** Sent via Developersdex http://www.examnotes.net ***|||A PK or UNIQUE constraint is the obvious way to generate the exception
condition. It's probably useful also to report the invalid data so you
may want to load to a staging table, without the constraint, and then
write a query using HAVING COUNT(*)>1 to find the duplicate rows.
David Portas
SQL Server MVP
--
Preventing subscrition expiration?
How can I prevent a subscription to a transactional publication from expiring?
Here's a brief overview of our situation. We have 2 db's on one server. One
of the db's is used for update, the other is used for search and retrieval
(read-only). Both db's have tables that are fulltext indexed. We would like
to be able to set up transactional replication so that updates can be applied
"on demand" without requiring that the indexes be rebuilt.
We've been successful at getting transactional replication to support this
scenario ... for a few days at a time! We have the need to go an extended
period of time (up to 2-3 weeks in extreme cases) without synchronizing,
however, it appears that the subscription expires and requires
re-synchronizing after 3 days.
The db that is getting updated is not very large (500mb) and only one user
performs updates on it. We have plenty of storage available, so space is not
a problem. I just need to figure out how to prevent to subscription from
expiring so that we can truly replicate "on-demand".
Thanks
MT
P.S. – we've ordered the recommended book on replication but it is not
scheduled to arrive until next week.
Hi MT,
If you right-click the publication in EM, you will see an option to change
the subscription expiration properties. You can set the hours to 32767
(about 3.7 years). The other option is to select the "Subscriptions never
expire, but they can be deactivated until they are reinitalized." This can
lead to other issues and I would opt for the former and not the later.
-Jose
"MTurner" <MTurner@.discussions.microsoft.com> wrote in message
news:F0F83E80-607C-482F-B3F5-CDCB797C63B2@.microsoft.com...
> How can I prevent a subscription to a transactional publication from
> expiring?
> Here's a brief overview of our situation. We have 2 db's on one server.
> One
> of the db's is used for update, the other is used for search and retrieval
> (read-only). Both db's have tables that are fulltext indexed. We would
> like
> to be able to set up transactional replication so that updates can be
> applied
> "on demand" without requiring that the indexes be rebuilt.
> We've been successful at getting transactional replication to support this
> scenario ... for a few days at a time! We have the need to go an extended
> period of time (up to 2-3 weeks in extreme cases) without synchronizing,
> however, it appears that the subscription expires and requires
> re-synchronizing after 3 days.
> The db that is getting updated is not very large (500mb) and only one user
> performs updates on it. We have plenty of storage available, so space is
> not
> a problem. I just need to figure out how to prevent to subscription from
> expiring so that we can truly replicate "on-demand".
> Thanks
> MT
> P.S. - we've ordered the recommended book on replication but it is not
> scheduled to arrive until next week.
>
|||You'll also need to change the transaction retention
period, and the history retention period.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks - I think this is the piece of the puzzle that I've been missing.
I hate to ask but I'm not finding anything in books on-line - how do you
change the retention periods?
Thanks again
"Paul Ibison" wrote:
> You'll also need to change the transaction retention
> period, and the history retention period.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Right-click on the replication monitor and select the distributor
properties - you'll see the settings there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Here's a brief overview of our situation. We have 2 db's on one server. One
of the db's is used for update, the other is used for search and retrieval
(read-only). Both db's have tables that are fulltext indexed. We would like
to be able to set up transactional replication so that updates can be applied
"on demand" without requiring that the indexes be rebuilt.
We've been successful at getting transactional replication to support this
scenario ... for a few days at a time! We have the need to go an extended
period of time (up to 2-3 weeks in extreme cases) without synchronizing,
however, it appears that the subscription expires and requires
re-synchronizing after 3 days.
The db that is getting updated is not very large (500mb) and only one user
performs updates on it. We have plenty of storage available, so space is not
a problem. I just need to figure out how to prevent to subscription from
expiring so that we can truly replicate "on-demand".
Thanks
MT
P.S. – we've ordered the recommended book on replication but it is not
scheduled to arrive until next week.
Hi MT,
If you right-click the publication in EM, you will see an option to change
the subscription expiration properties. You can set the hours to 32767
(about 3.7 years). The other option is to select the "Subscriptions never
expire, but they can be deactivated until they are reinitalized." This can
lead to other issues and I would opt for the former and not the later.
-Jose
"MTurner" <MTurner@.discussions.microsoft.com> wrote in message
news:F0F83E80-607C-482F-B3F5-CDCB797C63B2@.microsoft.com...
> How can I prevent a subscription to a transactional publication from
> expiring?
> Here's a brief overview of our situation. We have 2 db's on one server.
> One
> of the db's is used for update, the other is used for search and retrieval
> (read-only). Both db's have tables that are fulltext indexed. We would
> like
> to be able to set up transactional replication so that updates can be
> applied
> "on demand" without requiring that the indexes be rebuilt.
> We've been successful at getting transactional replication to support this
> scenario ... for a few days at a time! We have the need to go an extended
> period of time (up to 2-3 weeks in extreme cases) without synchronizing,
> however, it appears that the subscription expires and requires
> re-synchronizing after 3 days.
> The db that is getting updated is not very large (500mb) and only one user
> performs updates on it. We have plenty of storage available, so space is
> not
> a problem. I just need to figure out how to prevent to subscription from
> expiring so that we can truly replicate "on-demand".
> Thanks
> MT
> P.S. - we've ordered the recommended book on replication but it is not
> scheduled to arrive until next week.
>
|||You'll also need to change the transaction retention
period, and the history retention period.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks - I think this is the piece of the puzzle that I've been missing.
I hate to ask but I'm not finding anything in books on-line - how do you
change the retention periods?
Thanks again
"Paul Ibison" wrote:
> You'll also need to change the transaction retention
> period, and the history retention period.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Right-click on the replication monitor and select the distributor
properties - you'll see the settings there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Preventing SQL Injection with paramaterized queries
I use paramaterized queries when using ad-hoc queries in my code-behind. Everywhere website I visit says to use stored procedures or paramaterized queries if stored procedures cannot be used. I understand how SQL injection attacks work, but could someone please enlighten me why a paramaterized query helps prevent these attacks? It seems to me that the text that is entered on the web form would just be passed into the paramater, but I'm obviously missing something. Thanks.
Hi,
If you run the below statement it returns all the records.
declare @.pwd nvarchar(50)
declare @.username nvarchar(50)
set @.username = N''' or 1=1--'
set @.pwd = N'xxx'
declare @.sql nvarchar(1000)
SET @.sql = N'select username, password, userid from Users where username = N''' + @.username + ''' and Password = N''' + @.pwd + ''''
exec sp_executesql @.sql
select @.sql
But if you use create a procedure like below.
create proc login (
@.pwd nvarchar(50),
@.username nvarchar(50)
)
as
select username, password, userid from Users where username = @.username and Password = @.pwd
go
And run the below code, it returns nothing.
declare @.pwd nvarchar(50)
declare @.username nvarchar(50)
set @.username = N''' or 1=1--'
set @.pwd = N'xxx'
exec login @.pwd, @.username
In stored procedures parameter values are exactly used as values of the parameters, but if you are creating dynamically sql statements the final sql sentence may change according to the entered values
|||
Eralper is right, that's why. If his explaination wasn't clear enough, try this:
SELECT * FROM MyTable WHERE field='value' OR 1=1 OR ''=''
This would be similiar to someone building a SQL String like:
SQL="SELECT * FROM MyTable WHERE field='" & textbox1.text & "'"
and then someone types invalue' OR 1=1 OR ''=' into the textbox. This will return every row in MyTable instead of just the one you wanted. You could do sorts of things as well, like typingvalue' DELETE FROM MyTable SELECT ' will delete the contents of MyTable on you, etc. But this won't work:
DECLARE @.MyText varchar(8000)
SET @.MyText='value'' DELETE FROM MyTable SELECT '''
SELECT * FROM MyTable WHEREfield=@.MyText
This will return no records at all, because field never matches @.MyText (unless you have that weird value in a field![Big Smile [:D]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_sBM57B46Q2dtl5_mt8zO_sNoO4-siin6Xl0CSSBH9_UDPUAGiLjwy_Y-e7Wq6APyKVSe5OntxtoGfYT5hV2AnqpR38QcZMjA3shyvnEAAFyTFvgM3-Lw=s0-d)
Because @.MyText is a parameter value, it will not even attempt to execute or use anything within it as a command, partial command, etc. It is a value, and it can't be anything other than a value.
|||Sql injection really has me freaked but I'm trying to find an effective/efficient way of writing dynamic sql.
This this instance: You have a GridView binded to an ObjectDataSource with some TextBoxes used for Parameters that Filter the GridView. The GridView allows Sorting on all Fields and Custom Paging is implemented in a Sql Stored Procedure because the DataSource contains 100 million records.
With Custom Paging, Sorting must be done in the Stored Procedure. My issue is finding an appropriate method to allow Order By and Where to be dynamically created. Not all Fields would necessarily be filtered and the Order By may sort multiple Fields.
This would be easiest to do with Dynamic Sql, but I can't fathom the risk of exposing too much information because of an injection attack.
The only other option I know of is to use CASE statements in the SProc but that ends up being nasty and huge.
My concern with injection is the fact you have no idea what could be coming through. It may be a simple single quite or a hex derivative. You might say to use Regular Expressions to validate the input, but when filtering, some of the Fields may be free-text Fields.
Any thoughts related to this?
Nathan
|||Hi,
One more step further than the dynamic sql, if you have 100 million records and if you will let the user to sort the data on any column and filter on any column will cause the indexes on the sourcce tables to be insufficiently used or will not be used perhaps. So you will struggle with the performance problems. So you will limit the columns that the users will sort on or filter on, and will create indexes on those fields, otherwise performance will be a headache.
One more alternative is creating sp's for each condition. But this may be impossible if you have so many conditions.
As far as I remember, I once faced a problem with "order by" clause which was related with the record size. Since it was too big for sql server to manage an order process on it. So you can perhaps first build a table with only identity columns and necessary fields to be build for an order. And if you do not need a joined table for an order process do not include it. After managing the order by process, you can gather the other fields that will be displayed on the grid using the primary key values.
Eralper
http://www.kodyaz.com
|||
I don't think I understand what you mean by the Indexes to be insufficiently used. Assume that each Field has an Index set.
A quick question too now that I think about it. I saw in an instance of one of my databases where there were some indexes set to 1 field while some were set to multiple fields. How does this affect the order by clause? Let's say 3 fields, A, B and C each have a single index set on it.
If I order by A and B then are their indexes inconsequential since both are being ordered? Is that why you would have indexes with multiple fields. If that is the case, then you're right about limiting their sorting capabilities as this one table that has millions of records also has over 20 fields and doing combinations of indexes would be gnarly.
If that is the case that multiple field indexes must be created for multiple order by field parameters then I will stick with single indexes and order by field parameters.
It just came to mind as you can order multiple columns in an Excel Worksheet, I thought I'd allow that in a GridView.
Nathan
|||Hi,
For multiple field indexes, let's say you select A and B from Table1 and you have index on A
For the criterias or the sorting, the SELECT will reference the index A then will go to physical data pages and get the column B values.
But if you have an index both covering A and B, then the SELECT can get all information from the index and will not go the physical data pages. This will be very fast when compared with reading data pages. The end point of this approach is creating covering indexes.
For the first part of your reply, if you have an index for each colum you display on the grid then no problem. But if you display field A, and there is not an index on A. Then to filter all data in table for a specific value on field A, you will have do full table scan or at least a clustered index scan will run on the table.
Eralper
http://www.kodyaz.com
|||
So... Stored procedures provide protection against sql injection attacks... But what about parameterized queries?
I've always assumed that parameterized queries, even those generated dynamically, provide the same level of protection as stored procedures. Is this a proper assumption?
|||Yes, you are right. As I know they are simply the same like running a stored procedure. They can be used against sql injections.
Eralper
http://www.kodyaz.com
Preventing SQL Injection attacks
My site has come under attack from sql injections. I thought I had
things handled by replacing all single quotes with two single quotes,
aka
things handled by replacing all single quotes with two single quotes,
aka
Replace(inputString, "'", "''")
Alas, clever hackers have still managed to find a way to drop columns
from some of my tables. Can anybody direct me towards a best practice
document on preventing these attacks?
Thank you thank you,
KevinKevin Audleman wrote:
> My site has come under attack from sql injections. I thought I had
> things handled by replacing all single quotes with two single quotes,
> aka
> Replace(inputString, "'", "''")
> Alas, clever hackers have still managed to find a way to drop columns
> from some of my tables. Can anybody direct me towards a best practice
> document on preventing these attacks?
> Thank you thank you,
> Kevin|||http://www.wwwcoder.com/main/parent...68/default.aspx
http://www.vbmysql.com/articles/sqlinjection.html
http://msdn.microsoft.com/msdnmag/i...9/SQLInjection/
http://shiflett.org/articles/security-corner-apr2004
http://www.microsoft.com/technet/pr...n/sp3sec03.mspx|||Thank you Jennifer =)|||Kevin Audleman (audleman@.quasika.net) writes:
> My site has come under attack from sql injections. I thought I had
> things handled by replacing all single quotes with two single quotes,
> aka
> Replace(inputString, "'", "''")
> Alas, clever hackers have still managed to find a way to drop columns
> from some of my tables. Can anybody direct me towards a best practice
> document on preventing these attacks?
Learn about using parameterised commands in whichever API you are using.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
preventing second instance of SP
Is it possible to prevent second instance of my SP running
at the same time on SQL Server?
I'll appreciate a SQL example how-to define if the SP
already running.
Thank youAbout the only way you can do that is by exclusively locking something so
the second instance waits...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:075a01c3c315$d1d5b8c0$a101280a@.phx.gbl...
> Is it possible to prevent second instance of my SP running
> at the same time on SQL Server?
> I'll appreciate a SQL example how-to define if the SP
> already running.
> Thank you
at the same time on SQL Server?
I'll appreciate a SQL example how-to define if the SP
already running.
Thank youAbout the only way you can do that is by exclusively locking something so
the second instance waits...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:075a01c3c315$d1d5b8c0$a101280a@.phx.gbl...
> Is it possible to prevent second instance of my SP running
> at the same time on SQL Server?
> I'll appreciate a SQL example how-to define if the SP
> already running.
> Thank you
Preventing schedule job to run
Hi,
I have 2 jobs schedule to run after every alternate hour. Job A runs at 1 am, 3 am, 5 am etc. and job B runs at 2 am, 4 am , 6 am.
If job A is still running I would like Job B not to start at the scheduled time. How can I achieve this?
Thanks in Advance ... jVery simply... first step in Job A could be to update a flag on the DB, last step would be to remove it. First step in Job B would be to check the flag, if present it bombs if it's not there then continue.|||If you mean to have Job B not run at all, and let Job A potentially run twice in a row, then you could do it this way:
Create a table in say the pubs database called Status (col1 varchar(10))
The first step in Job A will be to put a row into this table. The last step in Job A will be to delete the table (think of it as an on/off switch)
The first step in Job B would be a check to see if a row exists, and if it does, then select 1/0 (or any other error of your choice that will trigger the OnJobStepFailure to trigger.
Set the failure action of step 1 of Job B to be exit the job, then you are done.
Not too sure what you would have to do to get Job B to run at say 2:30 or so, if you can not have Job A run twice in a row.
Hope this helps.|||Can I disable Job B or its schedule untill job A finishes? And once it finishes can I enable it?|||Yeah, you can do that, just do the following...
USE msdb
UPDATE sysjobs
SET enabled = 1
WHERE jobid = <jobid>
to enable the job and then set it back to 0 to disable it.
Another option would be to add job B as a step of job A. And just let it always run as soon as job A finishes. Not sure if that is an option though.
Hope this helps.|||Can I disable Job B or its schedule untill job A finishes? And once it finishes can I enable it?|||Sure... use the method Kuthula posted above, only instead of a flag in a table, have job A disable job B as the first step, and then enable it as the last step. using the update to sysjobs in msdb.
I have 2 jobs schedule to run after every alternate hour. Job A runs at 1 am, 3 am, 5 am etc. and job B runs at 2 am, 4 am , 6 am.
If job A is still running I would like Job B not to start at the scheduled time. How can I achieve this?
Thanks in Advance ... jVery simply... first step in Job A could be to update a flag on the DB, last step would be to remove it. First step in Job B would be to check the flag, if present it bombs if it's not there then continue.|||If you mean to have Job B not run at all, and let Job A potentially run twice in a row, then you could do it this way:
Create a table in say the pubs database called Status (col1 varchar(10))
The first step in Job A will be to put a row into this table. The last step in Job A will be to delete the table (think of it as an on/off switch)
The first step in Job B would be a check to see if a row exists, and if it does, then select 1/0 (or any other error of your choice that will trigger the OnJobStepFailure to trigger.
Set the failure action of step 1 of Job B to be exit the job, then you are done.
Not too sure what you would have to do to get Job B to run at say 2:30 or so, if you can not have Job A run twice in a row.
Hope this helps.|||Can I disable Job B or its schedule untill job A finishes? And once it finishes can I enable it?|||Yeah, you can do that, just do the following...
USE msdb
UPDATE sysjobs
SET enabled = 1
WHERE jobid = <jobid>
to enable the job and then set it back to 0 to disable it.
Another option would be to add job B as a step of job A. And just let it always run as soon as job A finishes. Not sure if that is an option though.
Hope this helps.|||Can I disable Job B or its schedule untill job A finishes? And once it finishes can I enable it?|||Sure... use the method Kuthula posted above, only instead of a flag in a table, have job A disable job B as the first step, and then enable it as the last step. using the update to sysjobs in msdb.
Preventing Remote Access to SQL Server
Our company's customer wants to prevent anyone from logging into SQL Server unless they are actually logged on the server machine. (In other words they do not want a user to be able to connect unless he or she is sitting at the server's keyboard). They do not wish to utilize NT authenication. In addition, there are a number of ASP and MTS apps that are required to connect using ADO connections. These apps are located on another server which is acting as the Web Server and the MTS Server. This server is on the other side of a firewall. I have been searching the Web for weeks now and have been unable to find any reference to anything similar. Any advice would be most appreciated.
JLLR
MCDBA MCSD
Database DeveloperThere is "a way" to prevent logging into the server unless sitting in front of it, but how do you picture those apps to continue functioning? Can you clarify your question?|||Here's the deal. SQL Server is running a machine we will call DBserver. There are several Web apps, and MTS apps running on another machine which is acting as an MTS Server and a Web Server. We will call this machine WBServer. There are also some VB exe's running on some client machines in the Network. WBServer is separated from DBServer by a firewall, (with WB server being outside the firewall). The servers are located in the server room. Inside the firewall, the are several machines which could connect to SQL Server using client tools (enterprise manager, Query analyzer, etc.) In addition, there were some machines linking to the data using ODBC connections in MS Access. Due to the fact there were alterations to the data in the tables which caused major complications to the National Defense readiness, the customer now requires all ad hoc connections to SQL Server be done on DBServer after the person making the change lsigns a logbook recording their entry into the server room and which machine on to which they will be logging. Due to the fact that individuals are violating the rules they wish to set up SQL Server to make it impossible to make an ad hoc connection other than from DBServer. However they do not want affect the way the various applications work. The obvious answer was to provide a limited number of logins and restrict their distribution. However, the passwords did not remain secret, and persons were able to get them and login. I have some thoughts bof a work around but I would like something better.
What do you think.|||If you wish to prevent users from connecting to SQL Server using Microsoft SQL Client Tools, if the are not sitting at the keyboard of the server. You could look at creating a script (job) that runs every 10 seconds let's say and looks for records in SYSPROCESSES for connections where program_name is in a list example :
SQL Query Analyzer
SQL Query Analyzer - Object Browser
SQL Profiler
ISQL-32
etc. and hostname is not equal to DBserver (using your name). Any matches would have there process terminated.
PS
What about users that create a link to SQL Server via MS Access and then perform ad-hoc queries via MS Access?|||Also Forbidden.|||Very interesting. There is the problem of server resources, and how often to run this procedure.. A user can do a lot of damage in 10 seconds. I will think about it.|||Actually a KILL is not forbidden, if you thought I meant to "DELETE" the record from sysprocesses your mistaking. As far as the 10 seconds goes that is an arbitrary number.
To correctly do what you want, you would not allow direct access to the database tables in the first place.
At our site all data queries and modifications are done via stored procedures which is in a controlled environment. Any ad-hoc queries are done on a separate server which is refreshed via database backup from production on a nightly bases.|||You misunderstand. MS Access ODBC links to the server are also forbidden.
It sounds like DBA's have some authority in your shop. Here they are just glorified data entry people until something goes wrong, then they do not listen to the advice anyway, or they ask you to build the impossible. If it were up to me, all apps would connect through Application roles., and no one would be able to connect to the DB if they were not in the an administrators role Then only certain NT Logins would be in that role. (Can't make that Admin group of Windows NT because everyone is in Admin group!!) But what do I know I am only an MCDBA.|||Tell them they can't screen their doors with chicken wire and then complain about flies in the house.
blindman|||There is a way to allow only certain applications to access a database on a SQLServer. It's called APPLICATION ROLE. This is the article in the Books on Line for it:
Establishing Application Security and Application Roles
The security system in Microsoft SQL Server is implemented at the lowest level: the database itself. This is the best method for controlling user activities regardless of the application used to communicate with SQL Server. However, sometimes security controls must be customized to accommodate the special requirements of an individual application, especially when dealing with complex databases and databases with large tables.
Additionally, you may want users to be restricted to accessing data only through a specific application (for example using SQL Query Analyzer or Microsoft Excel) or to be prevented from accessing data directly. Restricting user access in this way prohibits users from connecting to an instance of SQL Server using an application such as SQL Query Analyzer and executing a poorly written query, which can negatively affect the performance of the whole server.
SQL Server accommodates these needs through the use of application roles. Application roles are different than standard roles in that:
Application roles contain no members.
Microsoft Windows NT 4.0 or Windows 2000 groups, users, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user's connection through a specific application or applications. A user's association with an application role is due to his ability to run an application that activates the role, rather than his being a member of the role.
Application roles are inactive by default and require a password to be activated.
Application roles bypass standard permissions.
When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only through permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object, regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of an instance of SQL Server.
To ensure that all the functions of the application can be performed, a connection must lose default permissions applied to the login and user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked so the user can use the application successfully. Application roles overcome any conflicts with user's default permissions by temporarily suspending the user's default permissions and assigning them only the permissions of the application role.
Application roles allow the application, rather than SQL Server, to take over the responsibility of user authentication. However, because SQL Server still must authenticate the application when it accesses databases, the application must provide a password because there is no other way to authenticate an application.
If ad hoc access to a database is not required, users and Windows NT 4.0 or Windows 2000 groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, standardizing on one system-wide password assigned to an application role is possible, assuming access to the applications is secure.
There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or a SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to an instance of SQL Server, when the role is activated.
When an application user connects to an instance of SQL Server using Windows Authentication Mode, an application role can be used to set the permissions the Windows NT 4.0 or Windows 2000 user has in a database when using the application. This method allows Windows NT 4.0 or Windows 2000 auditing of the user account and control over user permissions, while she uses the application, to be easily maintained.
If SQL Server Authentication is used and auditing user access in the database is not required, it can be easier for the application to connect to an instance of SQL Server using a predefined SQL Server login. For example, an order entry application authenticates users running the application itself, and then connects to an instance of SQL Server using the same OrderEntry login. All connections use the same login, and relevant permissions are granted to this login.
Note Application roles work with both authentication modes.
Example
As an example of application role usage, a user Sue runs a sales application that requires SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in database Sales to work, but she should not have any SELECT, INSERT, or UPDATE permissions when accessing the Products or Orders tables using SQL Query Analyzer or any other tool. To ensure this, create one user-database role that denies SELECT, INSERT, or UPDATE permissions on the Products and Orders tables, and add Sue as a member of that database role. Then create an application role in the Sales database with SELECT, INSERT, and UPDATE permissions on the Products and Orders tables. When the application runs, it provides the password to activate the application role by using sp_setapprole, and gains the permissions to access the Products and Orders tables. If Sue tries to log in to an instance of SQL Server using any tool except the application, she will not be able to access the Products or Orders tables.
To create an application role
Transact-SQL
Enterprise Manager
How to create an application role (Enterprise Manager)
To create an application role
Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which to create a role.
Right-click Roles, and then click New Database Role.
In the Name box, enter the name of the new application role.
Under Database role type, click Application role, and then enter a password.
See Also
Establishing Application Security and Application Roles
SQL-DMO
To set an application role
Transact-SQL
To change the password of an application role
Transact-SQL
SQL-DMO
To remove an application role
Transact-SQL
Enterprise Manager
How to remove an application role (Enterprise Manager)
To remove an application role
Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which the application role exists.
Click Roles.
In the details pane, right-click the application role to remove, and then click Delete.
Confirm the deletion.
See Also
Establishing Application Security and Application Roles
SQL-DMO
For more information search the web with google and ask for application role sql server
Good luck
ionut
Originally posted by Joeller
Our company's customer wants to prevent anyone from logging into SQL Server unless they are actually logged on the server machine. (In other words they do not want a user to be able to connect unless he or she is sitting at the server's keyboard). They do not wish to utilize NT authenication. In addition, there are a number of ASP and MTS apps that are required to connect using ADO connections. These apps are located on another server which is acting as the Web Server and the MTS Server. This server is on the other side of a firewall. I have been searching the Web for weeks now and have been unable to find any reference to anything similar. Any advice would be most appreciated.
JLLR
MCDBA MCSD
Database Developer|||to inut: As stated earlier that is what I would do if I were able to do what I want. But I can't and that's all there is to that.
To blindman: It would be nice to tell these people what I think of their office politics, their so-called back up plans, their horrific excuse for a database etc., but that would probably cost our company it major customer and put 3/4 of the people here out of work. So I smile and tell them I will investigate ways to do what they want. It appears that the suggestion earlier re Killing the process after it starts will end up ing the only way to do this, as that is the third time I have gotten this suggestion. Then maybe I can put forward my plan for role based security. sigh.|||...but make sure it isn't YOUR head that rolls when (not if) the house of cards collapses.
blindman
JLLR
MCDBA MCSD
Database DeveloperThere is "a way" to prevent logging into the server unless sitting in front of it, but how do you picture those apps to continue functioning? Can you clarify your question?|||Here's the deal. SQL Server is running a machine we will call DBserver. There are several Web apps, and MTS apps running on another machine which is acting as an MTS Server and a Web Server. We will call this machine WBServer. There are also some VB exe's running on some client machines in the Network. WBServer is separated from DBServer by a firewall, (with WB server being outside the firewall). The servers are located in the server room. Inside the firewall, the are several machines which could connect to SQL Server using client tools (enterprise manager, Query analyzer, etc.) In addition, there were some machines linking to the data using ODBC connections in MS Access. Due to the fact there were alterations to the data in the tables which caused major complications to the National Defense readiness, the customer now requires all ad hoc connections to SQL Server be done on DBServer after the person making the change lsigns a logbook recording their entry into the server room and which machine on to which they will be logging. Due to the fact that individuals are violating the rules they wish to set up SQL Server to make it impossible to make an ad hoc connection other than from DBServer. However they do not want affect the way the various applications work. The obvious answer was to provide a limited number of logins and restrict their distribution. However, the passwords did not remain secret, and persons were able to get them and login. I have some thoughts bof a work around but I would like something better.
What do you think.|||If you wish to prevent users from connecting to SQL Server using Microsoft SQL Client Tools, if the are not sitting at the keyboard of the server. You could look at creating a script (job) that runs every 10 seconds let's say and looks for records in SYSPROCESSES for connections where program_name is in a list example :
SQL Query Analyzer
SQL Query Analyzer - Object Browser
SQL Profiler
ISQL-32
etc. and hostname is not equal to DBserver (using your name). Any matches would have there process terminated.
PS
What about users that create a link to SQL Server via MS Access and then perform ad-hoc queries via MS Access?|||Also Forbidden.|||Very interesting. There is the problem of server resources, and how often to run this procedure.. A user can do a lot of damage in 10 seconds. I will think about it.|||Actually a KILL is not forbidden, if you thought I meant to "DELETE" the record from sysprocesses your mistaking. As far as the 10 seconds goes that is an arbitrary number.
To correctly do what you want, you would not allow direct access to the database tables in the first place.
At our site all data queries and modifications are done via stored procedures which is in a controlled environment. Any ad-hoc queries are done on a separate server which is refreshed via database backup from production on a nightly bases.|||You misunderstand. MS Access ODBC links to the server are also forbidden.
It sounds like DBA's have some authority in your shop. Here they are just glorified data entry people until something goes wrong, then they do not listen to the advice anyway, or they ask you to build the impossible. If it were up to me, all apps would connect through Application roles., and no one would be able to connect to the DB if they were not in the an administrators role Then only certain NT Logins would be in that role. (Can't make that Admin group of Windows NT because everyone is in Admin group!!) But what do I know I am only an MCDBA.|||Tell them they can't screen their doors with chicken wire and then complain about flies in the house.
blindman|||There is a way to allow only certain applications to access a database on a SQLServer. It's called APPLICATION ROLE. This is the article in the Books on Line for it:
Establishing Application Security and Application Roles
The security system in Microsoft SQL Server is implemented at the lowest level: the database itself. This is the best method for controlling user activities regardless of the application used to communicate with SQL Server. However, sometimes security controls must be customized to accommodate the special requirements of an individual application, especially when dealing with complex databases and databases with large tables.
Additionally, you may want users to be restricted to accessing data only through a specific application (for example using SQL Query Analyzer or Microsoft Excel) or to be prevented from accessing data directly. Restricting user access in this way prohibits users from connecting to an instance of SQL Server using an application such as SQL Query Analyzer and executing a poorly written query, which can negatively affect the performance of the whole server.
SQL Server accommodates these needs through the use of application roles. Application roles are different than standard roles in that:
Application roles contain no members.
Microsoft Windows NT 4.0 or Windows 2000 groups, users, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user's connection through a specific application or applications. A user's association with an application role is due to his ability to run an application that activates the role, rather than his being a member of the role.
Application roles are inactive by default and require a password to be activated.
Application roles bypass standard permissions.
When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only through permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object, regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of an instance of SQL Server.
To ensure that all the functions of the application can be performed, a connection must lose default permissions applied to the login and user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked so the user can use the application successfully. Application roles overcome any conflicts with user's default permissions by temporarily suspending the user's default permissions and assigning them only the permissions of the application role.
Application roles allow the application, rather than SQL Server, to take over the responsibility of user authentication. However, because SQL Server still must authenticate the application when it accesses databases, the application must provide a password because there is no other way to authenticate an application.
If ad hoc access to a database is not required, users and Windows NT 4.0 or Windows 2000 groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, standardizing on one system-wide password assigned to an application role is possible, assuming access to the applications is secure.
There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or a SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to an instance of SQL Server, when the role is activated.
When an application user connects to an instance of SQL Server using Windows Authentication Mode, an application role can be used to set the permissions the Windows NT 4.0 or Windows 2000 user has in a database when using the application. This method allows Windows NT 4.0 or Windows 2000 auditing of the user account and control over user permissions, while she uses the application, to be easily maintained.
If SQL Server Authentication is used and auditing user access in the database is not required, it can be easier for the application to connect to an instance of SQL Server using a predefined SQL Server login. For example, an order entry application authenticates users running the application itself, and then connects to an instance of SQL Server using the same OrderEntry login. All connections use the same login, and relevant permissions are granted to this login.
Note Application roles work with both authentication modes.
Example
As an example of application role usage, a user Sue runs a sales application that requires SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in database Sales to work, but she should not have any SELECT, INSERT, or UPDATE permissions when accessing the Products or Orders tables using SQL Query Analyzer or any other tool. To ensure this, create one user-database role that denies SELECT, INSERT, or UPDATE permissions on the Products and Orders tables, and add Sue as a member of that database role. Then create an application role in the Sales database with SELECT, INSERT, and UPDATE permissions on the Products and Orders tables. When the application runs, it provides the password to activate the application role by using sp_setapprole, and gains the permissions to access the Products and Orders tables. If Sue tries to log in to an instance of SQL Server using any tool except the application, she will not be able to access the Products or Orders tables.
To create an application role
Transact-SQL
Enterprise Manager
How to create an application role (Enterprise Manager)
To create an application role
Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which to create a role.
Right-click Roles, and then click New Database Role.
In the Name box, enter the name of the new application role.
Under Database role type, click Application role, and then enter a password.
See Also
Establishing Application Security and Application Roles
SQL-DMO
To set an application role
Transact-SQL
To change the password of an application role
Transact-SQL
SQL-DMO
To remove an application role
Transact-SQL
Enterprise Manager
How to remove an application role (Enterprise Manager)
To remove an application role
Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which the application role exists.
Click Roles.
In the details pane, right-click the application role to remove, and then click Delete.
Confirm the deletion.
See Also
Establishing Application Security and Application Roles
SQL-DMO
For more information search the web with google and ask for application role sql server
Good luck
ionut
Originally posted by Joeller
Our company's customer wants to prevent anyone from logging into SQL Server unless they are actually logged on the server machine. (In other words they do not want a user to be able to connect unless he or she is sitting at the server's keyboard). They do not wish to utilize NT authenication. In addition, there are a number of ASP and MTS apps that are required to connect using ADO connections. These apps are located on another server which is acting as the Web Server and the MTS Server. This server is on the other side of a firewall. I have been searching the Web for weeks now and have been unable to find any reference to anything similar. Any advice would be most appreciated.
JLLR
MCDBA MCSD
Database Developer|||to inut: As stated earlier that is what I would do if I were able to do what I want. But I can't and that's all there is to that.
To blindman: It would be nice to tell these people what I think of their office politics, their so-called back up plans, their horrific excuse for a database etc., but that would probably cost our company it major customer and put 3/4 of the people here out of work. So I smile and tell them I will investigate ways to do what they want. It appears that the suggestion earlier re Killing the process after it starts will end up ing the only way to do this, as that is the third time I have gotten this suggestion. Then maybe I can put forward my plan for role based security. sigh.|||...but make sure it isn't YOUR head that rolls when (not if) the house of cards collapses.
blindman
Preventing overlapping date records
Hi,
I have a SQL Server 2000 table which holds timesheet data. Each timesheet is
made up of one or more timesheet elements which would e.g. correspond to the
number of hours worked by an employee in any given day. I'm looking for a
way to ensure that employees do not enter duplicate or overlapping records.
E.g. let's say tblTimesheet holds the following records:
dtmStart dtmEnd
-- --
2003-09-15 09:15:00 2003-09-15 17:00:00
2003-09-16 09:15:00 2003-09-16 17:00:00
I need some help with a stored procedure which will accept a starting
datetime and an ending datetime as parameters and tell me whether that would
overlap in any way with any existing record. The two parameter dates will
already have been validated before being passed to the SP, so I know that
they are valid dates and that the ending datetime will be later than the
starting date time.
So,
CREATE PROCEDURE usp_ValidateTimesheetEntry
@.pdtmStart datetime,
@.pdtmEnd datetime
SELECT COUNT(*) FROM tblTimesheet
WHERE ...-- this is the bit I'm stuck on
Examples
=======
If @.pdtmStart is 2003-09-17 09:15:00 and @.pdtmEnd is 2003-09-17 17:00:00
this is valid.
If @.pdtmStart is 2003-09-16 18:30:00 and @.pdtmEnd is 2003-09-16 23:50:00
this is valid.
If @.pdtmStart is 2003-09-15 01:00:00 and @.pdtmEnd is 2003-09-15 09:30:00
this is invalid because it overlaps with the first record.
If @.pdtmStart is 2003-09-16 11:00:00 and @.pdtmEnd is 2003-09-16 16:30:00
this is invalid because it overlaps (or rather is totally contained within)
the second record.
Any assistance gratefully received.
MarkYou can use a combination of constraints and a trigger:
CREATE TABLE Timesheet (employee INTEGER NOT NULL /* REFERENCES
Employees (employee) */, dtmstart DATETIME NOT NULL, dtmend DATETIME
NOT NULL, CHECK (dtmstart<dtmend), UNIQUE (employee,dtmstart))
GO
CREATE TRIGGER trg_timesheet_no_overlap
ON Timesheet
FOR INSERT, UPDATE
AS
IF EXISTS
(SELECT * FROM inserted I
JOIN Timesheet T ON
I.employee = T.employee
AND I.dtmend > T.dtmstart
AND I.dtmstart < T.dtmend
AND I.dtmstart <> T.dtmstart)
BEGIN
RAISERROR ('Overlapping times not allowed', 16, 1)
ROLLBACK TRANSACTION
END
Notice that this will allow double shifts, i.e. 1st Shift End = 2nd
Shift Start. If you don't want that just change > and < to >= and <=.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110719703.701430.301990@.f14g2000cwb.googlegroups.com...
> You can use a combination of constraints and a trigger:
Perfect! Thanks very much.
I have a SQL Server 2000 table which holds timesheet data. Each timesheet is
made up of one or more timesheet elements which would e.g. correspond to the
number of hours worked by an employee in any given day. I'm looking for a
way to ensure that employees do not enter duplicate or overlapping records.
E.g. let's say tblTimesheet holds the following records:
dtmStart dtmEnd
-- --
2003-09-15 09:15:00 2003-09-15 17:00:00
2003-09-16 09:15:00 2003-09-16 17:00:00
I need some help with a stored procedure which will accept a starting
datetime and an ending datetime as parameters and tell me whether that would
overlap in any way with any existing record. The two parameter dates will
already have been validated before being passed to the SP, so I know that
they are valid dates and that the ending datetime will be later than the
starting date time.
So,
CREATE PROCEDURE usp_ValidateTimesheetEntry
@.pdtmStart datetime,
@.pdtmEnd datetime
SELECT COUNT(*) FROM tblTimesheet
WHERE ...-- this is the bit I'm stuck on
Examples
=======
If @.pdtmStart is 2003-09-17 09:15:00 and @.pdtmEnd is 2003-09-17 17:00:00
this is valid.
If @.pdtmStart is 2003-09-16 18:30:00 and @.pdtmEnd is 2003-09-16 23:50:00
this is valid.
If @.pdtmStart is 2003-09-15 01:00:00 and @.pdtmEnd is 2003-09-15 09:30:00
this is invalid because it overlaps with the first record.
If @.pdtmStart is 2003-09-16 11:00:00 and @.pdtmEnd is 2003-09-16 16:30:00
this is invalid because it overlaps (or rather is totally contained within)
the second record.
Any assistance gratefully received.
MarkYou can use a combination of constraints and a trigger:
CREATE TABLE Timesheet (employee INTEGER NOT NULL /* REFERENCES
Employees (employee) */, dtmstart DATETIME NOT NULL, dtmend DATETIME
NOT NULL, CHECK (dtmstart<dtmend), UNIQUE (employee,dtmstart))
GO
CREATE TRIGGER trg_timesheet_no_overlap
ON Timesheet
FOR INSERT, UPDATE
AS
IF EXISTS
(SELECT * FROM inserted I
JOIN Timesheet T ON
I.employee = T.employee
AND I.dtmend > T.dtmstart
AND I.dtmstart < T.dtmend
AND I.dtmstart <> T.dtmstart)
BEGIN
RAISERROR ('Overlapping times not allowed', 16, 1)
ROLLBACK TRANSACTION
END
Notice that this will allow double shifts, i.e. 1st Shift End = 2nd
Shift Start. If you don't want that just change > and < to >= and <=.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110719703.701430.301990@.f14g2000cwb.googlegroups.com...
> You can use a combination of constraints and a trigger:
Perfect! Thanks very much.
Preventing overlapping data entry
Hello Everyone,
I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.
The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.
Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.
After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.
Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.
Thanks in advance,
Brian
This query should give you the timeIDs that have not been scheduled:
SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeIDFROM tblCalendar cWHERE c.date ='4/20/2007' )Replace the date with the date the user has selected.|||
This query leaves out the prior and next time slots to avoid overlapping a one hour appointment.
SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT unavailableTimes.timeIDFROM (SELECT c.timeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDUNION SELECT t1.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t1ON t1.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, -30, c.date +' ' + t.time)), 14, 7)UNION SELECT t2.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t2ON t2.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, 30, c.date +' ' + t.time)), 14, 7) ) unavailableTimesWHERE unavailableTimes.date ='4/20/2007' )This query is not optimal but you get the idea. Again, replace the date with the date the user has selected. You may also need to tweak the SUBSTRING params based on the format of your DATETIMEs.|||
Thank you so much for your help. I think I was a little to vague on my description but this solution helped me get a solution that worked for me. I needed to check for the previous and next pkTimeID value to eliminate not the actual datetime values. Just to keep this short my statement is as follows:
SELECT t.pkTimeID, t.timeFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeID - 1FROM tblCalendar cUNIONSELECT c1.timeIDFROM tblCalendar c1UNIONSELECT c2.timeID + 1FROM tblCalendar c2WHERE (c2.[date] ='4/20/2007'))
Thank you again for your help, and if you have any suggestions on how I might improve what I did come up with I would appreciate it. This is my first "real" project in the work place and I would like to learn the best way to approach a problem where ever I can, so that I will not have to learn these things later.
Brian
Preventing invalid data from being entered
Hi,
I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle.
In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data.
If this is not possible, is it viable to remove the character after insert and still send the email withe the required info?
Any one any ideas on the cleanest way to achieve this?
Thanks
Hi,
you could use a trigger for that. Check the incoming values for information about special character and put in a auditing record in another table (sending EMails from triggers is not suggested as it runs in the same transaction and can slow down your system or break your code). So a sample for doing this would be the following:
--Check the triggernestlevel to ensure that the trigger does not call an infinite loop
BEGIN TRANSACTION
INSERT INTO AuditTable
SELECT TheColumns
FROM INSERTED
WHERE CHARINDEX(SomeColumn,'SomeChartoExclude') > 0
--Then either Replace the Value or do something else
UPDATE SomeTable
SET SomeColumn = REPLACE(SomeColumn, 'SomeChartoExclude','ReplacedChar')
INNER JOIN INSERTED
ON I.PrimaryKeyCol 0 S.PrimaryKeyCol
WHERE CHARINDEX(SomeColumn,'SomeChartoExclude') > 0
COMMIT
In addition put error handling in your trigger if you want to catch the error that might occur.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Preventing Injection - Client Side
Thanks
I am building an office application which uses Microsoft Access as a
client and SQL Server as the server. I am new to SQL Server and I
don't want to anything that is too stupid. I am assuming the db
administrator would create a database specifically for this
application. The program must be able to create and drop tables and
works fine when hosted over the internet.
I am preventing everything but characters and numbers in my WHERE
clauses and data to prevent injection.
But besides this measure, the program has these access/security
needs/issues.
Tables - Create Drop Read Write
SPs -- Create Execute
In addition the program needs to read from
system_user
db_name()
information_schema.tables
Information_schema.columns
sysobjects
Am I doing anything too stupid if the admin would prefer a more secure
situation, maybe on a company database?
Willywilly wrote:
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
Who is the owner of these tables? Are they owned by "dbo"? If so, all
users would have to be aliased as the dbo in the database, which means
they probably have too many rights. They could be limited to creating
tables under their user names (e.g. Joe.MyTable), but this would not
give other users access to these tables. Creating and Dropping tables is
generally a system admin or database owner function and not normal for
an application. Unless, of course, you mean temp tables.
To perform DML operations on tables, I would use stored procedures. You
can use them for SELECT statements as well, but some users choose to
embed SQL in the app (I prefer SPs all around).
The other objects/functions are available to all users.
Maybe you could explain the need to create/drop tables a little more.
Same for stored procedures.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
Consider using parameters rather than concatenating user-supplied values to
build the SQL statement. This is more secure.
Hope this helps.
Dan Guzman
SQL Server MVP
"willy" <willrich33@.yahoo.com> wrote in message
news:1131557696.739603.25830@.g14g2000cwa.googlegroups.com...
> Thanks
> I am building an office application which uses Microsoft Access as a
> client and SQL Server as the server. I am new to SQL Server and I
> don't want to anything that is too stupid. I am assuming the db
> administrator would create a database specifically for this
> application. The program must be able to create and drop tables and
> works fine when hosted over the internet.
> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
> But besides this measure, the program has these access/security
> needs/issues.
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
> Am I doing anything too stupid if the admin would prefer a more secure
> situation, maybe on a company database?
> Willy
>|||Dan,David:
Thank you for making me think about security.
My application does not allow the user to use tables under any other
username (such as dbo) so he is fairly isolated. I realize Access
defaults to dbo but I will be shutting the database window down.
I tried to import a file to my database under a different username (like
dbo) but Access converted it back to the username of the new
database/login so that seems somewhat secure. This is why I am using
Access for a client program.
I am not coding for characters other than Like *[A-Z0-9] so the purging
of other characters from the column and table names that the user has
control of will have to do for now.
In the documentation I am going to highly recommend "isolating" the
application in separate db for "security reasons."
Thank you for suggesting parameters and DML as solutions for preventing
injection. I will keep my eye out for information on them.
I really need to learn more about SQL Server permissions and security.
Willy
*** Sent via Developersdex http://www.codecomments.com ***|||Comments inline
"willy" <willrich33@.yahoo.com> wrote in message
news:1131557696.739603.25830@.g14g2000cwa.googlegroups.com...
> Thanks
> I am building an office application which uses Microsoft Access as a
> client and SQL Server as the server. I am new to SQL Server and I
> don't want to anything that is too stupid.
You already missed this goal by using Access as the front end.
> I am assuming the db
> administrator would create a database specifically for this
> application. The program must be able to create and drop tables and
> works fine when hosted over the internet.
>
This would require opening a SQL port to the internet. Not a particularly
good idea from a security standpoint, especially when you are talking about
the privileges necessary to do what you ask.
> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
>
Doesn't matter. With open network ports and SQL credentials in the Access
app, your server is open to conection via Query Analyzer or any other SQL
client app. Who cares about SQL injection when I can send any SQL command I
desire.
> But besides this measure, the program has these access/security
> needs/issues.
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
> Am I doing anything too stupid if the admin would prefer a more secure
> situation, maybe on a company database?
Maybe on a throwaway system. I certainly wouldn't allow any such
application anywhere near any of my servers.
> Willy
>
Sorry if I sound harsh, but I am trying to discourage you from making some
fundamental mistakes in building a SQL application. Access front end
'applications' have caused me more headaches than any other single app dev
environment when connecting to SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
I am building an office application which uses Microsoft Access as a
client and SQL Server as the server. I am new to SQL Server and I
don't want to anything that is too stupid. I am assuming the db
administrator would create a database specifically for this
application. The program must be able to create and drop tables and
works fine when hosted over the internet.
I am preventing everything but characters and numbers in my WHERE
clauses and data to prevent injection.
But besides this measure, the program has these access/security
needs/issues.
Tables - Create Drop Read Write
SPs -- Create Execute
In addition the program needs to read from
system_user
db_name()
information_schema.tables
Information_schema.columns
sysobjects
Am I doing anything too stupid if the admin would prefer a more secure
situation, maybe on a company database?
Willywilly wrote:
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
Who is the owner of these tables? Are they owned by "dbo"? If so, all
users would have to be aliased as the dbo in the database, which means
they probably have too many rights. They could be limited to creating
tables under their user names (e.g. Joe.MyTable), but this would not
give other users access to these tables. Creating and Dropping tables is
generally a system admin or database owner function and not normal for
an application. Unless, of course, you mean temp tables.
To perform DML operations on tables, I would use stored procedures. You
can use them for SELECT statements as well, but some users choose to
embed SQL in the app (I prefer SPs all around).
The other objects/functions are available to all users.
Maybe you could explain the need to create/drop tables a little more.
Same for stored procedures.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
Consider using parameters rather than concatenating user-supplied values to
build the SQL statement. This is more secure.
Hope this helps.
Dan Guzman
SQL Server MVP
"willy" <willrich33@.yahoo.com> wrote in message
news:1131557696.739603.25830@.g14g2000cwa.googlegroups.com...
> Thanks
> I am building an office application which uses Microsoft Access as a
> client and SQL Server as the server. I am new to SQL Server and I
> don't want to anything that is too stupid. I am assuming the db
> administrator would create a database specifically for this
> application. The program must be able to create and drop tables and
> works fine when hosted over the internet.
> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
> But besides this measure, the program has these access/security
> needs/issues.
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
> Am I doing anything too stupid if the admin would prefer a more secure
> situation, maybe on a company database?
> Willy
>|||Dan,David:
Thank you for making me think about security.
My application does not allow the user to use tables under any other
username (such as dbo) so he is fairly isolated. I realize Access
defaults to dbo but I will be shutting the database window down.
I tried to import a file to my database under a different username (like
dbo) but Access converted it back to the username of the new
database/login so that seems somewhat secure. This is why I am using
Access for a client program.
I am not coding for characters other than Like *[A-Z0-9] so the purging
of other characters from the column and table names that the user has
control of will have to do for now.
In the documentation I am going to highly recommend "isolating" the
application in separate db for "security reasons."
Thank you for suggesting parameters and DML as solutions for preventing
injection. I will keep my eye out for information on them.
I really need to learn more about SQL Server permissions and security.
Willy
*** Sent via Developersdex http://www.codecomments.com ***|||Comments inline
"willy" <willrich33@.yahoo.com> wrote in message
news:1131557696.739603.25830@.g14g2000cwa.googlegroups.com...
> Thanks
> I am building an office application which uses Microsoft Access as a
> client and SQL Server as the server. I am new to SQL Server and I
> don't want to anything that is too stupid.
You already missed this goal by using Access as the front end.
> I am assuming the db
> administrator would create a database specifically for this
> application. The program must be able to create and drop tables and
> works fine when hosted over the internet.
>
This would require opening a SQL port to the internet. Not a particularly
good idea from a security standpoint, especially when you are talking about
the privileges necessary to do what you ask.
> I am preventing everything but characters and numbers in my WHERE
> clauses and data to prevent injection.
>
Doesn't matter. With open network ports and SQL credentials in the Access
app, your server is open to conection via Query Analyzer or any other SQL
client app. Who cares about SQL injection when I can send any SQL command I
desire.
> But besides this measure, the program has these access/security
> needs/issues.
> Tables - Create Drop Read Write
> SPs -- Create Execute
> In addition the program needs to read from
> system_user
> db_name()
> information_schema.tables
> Information_schema.columns
> sysobjects
> Am I doing anything too stupid if the admin would prefer a more secure
> situation, maybe on a company database?
Maybe on a throwaway system. I certainly wouldn't allow any such
application anywhere near any of my servers.
> Willy
>
Sorry if I sound harsh, but I am trying to discourage you from making some
fundamental mistakes in building a SQL application. Access front end
'applications' have caused me more headaches than any other single app dev
environment when connecting to SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
Preventing history snapshot generation
Hello,
is there any way to prevent scheduled history snaphsot generation in case
there are no recerds retrieved from report underlaying query ?No there is not.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"jacek" <jacek@.discussions.microsoft.com> wrote in message
news:CFA802BC-F5B6-4509-AD93-00E1ACE2B30F@.microsoft.com...
> Hello,
> is there any way to prevent scheduled history snaphsot generation in case
> there are no recerds retrieved from report underlaying query ?
is there any way to prevent scheduled history snaphsot generation in case
there are no recerds retrieved from report underlaying query ?No there is not.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"jacek" <jacek@.discussions.microsoft.com> wrote in message
news:CFA802BC-F5B6-4509-AD93-00E1ACE2B30F@.microsoft.com...
> Hello,
> is there any way to prevent scheduled history snaphsot generation in case
> there are no recerds retrieved from report underlaying query ?
Preventing GroupBy clause
I had a question similar to this before and can't seem to make this one
work.
I have the following:
Select
ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
from logon l join Applicant a on (l.email=a.email) where l.email =
'tfs@.dlink.com'
This works fine if I don't have the max function, but it can give me
multiple responses so I just want the last ApplicantID. This also works if
I put the password, email,firstName and LastName in a Groupby clause. Can I
change the max statement to prevent having to use the Groupby Clause.
Thanks,
TomIs there any special reason you'd rather not use a GROUP BY clause?
Any way, you can use:
SELECT TOP 1 <col_list>
FROM <table>
ORDER BY <sort_list>
BG, SQL Server MVP
www.SolidQualityLearning.com
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
>I had a question similar to this before and can't seem to make this one
>work.
> I have the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> This works fine if I don't have the max function, but it can give me
> multiple responses so I just want the last ApplicantID. This also works
> if I put the password, email,firstName and LastName in a Groupby clause.
> Can I change the max statement to prevent having to use the Groupby
> Clause.
> Thanks,
> Tom
>|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:O1mgWnkAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> Is there any special reason you'd rather not use a GROUP BY clause?
Mainly, because it is just something more for SQL to do, when all I want is
the Max Applicant.
> Any way, you can use:
> SELECT TOP 1 <col_list>
> FROM <table>
> ORDER BY <sort_list>
Same problem as above - would like to just say Max instead of having to sort
it.
It may not be much different, but I assume that doing a sort or group is a
little more of a hit to the engine that getting the max number.
Thanks,
Tom
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
>|||> It may not be much different, but I assume that doing a sort or group is a
> little more of a hit to the engine that getting the max number.
Well, did you test your theory? We have the ability to view the plans,
stress test our apps, and time our code. Why are we relying on assumptions
to make our decisions for us?|||<snip>
> Same problem as above - would like to just say Max instead of having to so
rt
> it.
> It may not be much different, but I assume that doing a sort or group is a
> little more of a hit to the engine that getting the max number.
So how is SQL-Server supposed to determine the Max ApplicantID without
some way of comparing the different ApplicantIDs? Should SQL-Server just
guess it? In that case, you can leave out the ORDER BY clause, and you
will get 'any' ApplicantID.
Otherwise, just use the technology (i.e. use a GROUP BY clause) and
trust the product, or do some actual performance testing instead of
asking the impossible. Chances are that you would even see a significant
(measurable) performance difference between the different methods,
unless you're using a big table.
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41F567CA.CC06163B@.toomuchspamalready.nl...
> <snip>
> So how is SQL-Server supposed to determine the Max ApplicantID without
> some way of comparing the different ApplicantIDs? Should SQL-Server just
> guess it? In that case, you can leave out the ORDER BY clause, and you
> will get 'any' ApplicantID.
>
No, I was trying to use the following:
Select
ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
from logon l join Applicant a on (l.email=a.email) where l.email =
'tfs@.dlink.com'
which does work fine if I take all fields out. I want the max ApplicantID,
because it will be the last ApplicantID assigned for this email address.
Tom
> Otherwise, just use the technology (i.e. use a GROUP BY clause) and
> trust the product, or do some actual performance testing instead of
> asking the impossible. Chances are that you would even see a significant
> (measurable) performance difference between the different methods,
> unless you're using a big table.
> Gert-Jan|||> No, I was trying to use the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> which does work fine if I take all fields out. I want the max
ApplicantID,
> because it will be the last ApplicantID assigned for this email address.
You need to get the MAX from a subquery. Also note that SQL Server will
have no idea if you want the MAX(ApplicantID) to have an e-mail address of
tfs@.dlink.com or not.|||--Down with JOINS, up with table variables:
DECLARE @.MyApplicant TABLE
(
email varchar(50),
pw varchar(50),
fname varchar(50),
lname varchar(50),
AppID int
)
--grab records of interest
INSERT INTO @.MyApplicant (email, pw, fname, lname)
SELECT l.email, l.password, l.firstName, l.LastName
FROM logon l
WHERE l.email = 'tfs@.dlink.com'
--grab details pertaining to the records of interest by key information
UPDATE @.MyApplicant
SET AppID =
(
SELECT Max(a.ApplicantID)
FROM Applicant a
WHERE a.email = myapp.email
)
FROM @.MyApplicant myapp
SELECT *
FROM @.MyApplicant
--assuming indexes on email field in all tables, it doesn't get much
faster than this.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
> I had a question similar to this before and can't seem to make this one
> work.
> I have the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> This works fine if I don't have the max function, but it can give me
> multiple responses so I just want the last ApplicantID. This also works
if
> I put the password, email,firstName and LastName in a Groupby clause. Can
I
> change the max statement to prevent having to use the Groupby Clause.
> Thanks,
> Tom
>|||This may be unreasonable of me, but when you say max(applicantID), is this
really the last one? Could you not assign an applicantId that had already
been used? Do you not have a date when the assignment was made that you can
use to get the last assignment?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23yQDFHmAFHA.3820@.TK2MSFTNGP11.phx.gbl...
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41F567CA.CC06163B@.toomuchspamalready.nl...
> No, I was trying to use the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> which does work fine if I take all fields out. I want the max
> ApplicantID, because it will be the last ApplicantID assigned for this
> email address.
> Tom
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uwWf3EpAFHA.2112@.TK2MSFTNGP09.phx.gbl...
> This may be unreasonable of me, but when you say max(applicantID), is this
> really the last one? Could you not assign an applicantId that had already
> been used? Do you not have a date when the assignment was made that you
> can use to get the last assignment?
In this particular case the applicantID is just a numeric ID that is
sequentially assigned. It happens to be an identity field. The max
applicantID will always be the latest one assigned.
Tom
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23yQDFHmAFHA.3820@.TK2MSFTNGP11.phx.gbl...
>
work.
I have the following:
Select
ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
from logon l join Applicant a on (l.email=a.email) where l.email =
'tfs@.dlink.com'
This works fine if I don't have the max function, but it can give me
multiple responses so I just want the last ApplicantID. This also works if
I put the password, email,firstName and LastName in a Groupby clause. Can I
change the max statement to prevent having to use the Groupby Clause.
Thanks,
TomIs there any special reason you'd rather not use a GROUP BY clause?
Any way, you can use:
SELECT TOP 1 <col_list>
FROM <table>
ORDER BY <sort_list>
BG, SQL Server MVP
www.SolidQualityLearning.com
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
>I had a question similar to this before and can't seem to make this one
>work.
> I have the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> This works fine if I don't have the max function, but it can give me
> multiple responses so I just want the last ApplicantID. This also works
> if I put the password, email,firstName and LastName in a Groupby clause.
> Can I change the max statement to prevent having to use the Groupby
> Clause.
> Thanks,
> Tom
>|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:O1mgWnkAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> Is there any special reason you'd rather not use a GROUP BY clause?
Mainly, because it is just something more for SQL to do, when all I want is
the Max Applicant.
> Any way, you can use:
> SELECT TOP 1 <col_list>
> FROM <table>
> ORDER BY <sort_list>
Same problem as above - would like to just say Max instead of having to sort
it.
It may not be much different, but I assume that doing a sort or group is a
little more of a hit to the engine that getting the max number.
Thanks,
Tom
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
>|||> It may not be much different, but I assume that doing a sort or group is a
> little more of a hit to the engine that getting the max number.
Well, did you test your theory? We have the ability to view the plans,
stress test our apps, and time our code. Why are we relying on assumptions
to make our decisions for us?|||<snip>
> Same problem as above - would like to just say Max instead of having to so
rt
> it.
> It may not be much different, but I assume that doing a sort or group is a
> little more of a hit to the engine that getting the max number.
So how is SQL-Server supposed to determine the Max ApplicantID without
some way of comparing the different ApplicantIDs? Should SQL-Server just
guess it? In that case, you can leave out the ORDER BY clause, and you
will get 'any' ApplicantID.
Otherwise, just use the technology (i.e. use a GROUP BY clause) and
trust the product, or do some actual performance testing instead of
asking the impossible. Chances are that you would even see a significant
(measurable) performance difference between the different methods,
unless you're using a big table.
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41F567CA.CC06163B@.toomuchspamalready.nl...
> <snip>
> So how is SQL-Server supposed to determine the Max ApplicantID without
> some way of comparing the different ApplicantIDs? Should SQL-Server just
> guess it? In that case, you can leave out the ORDER BY clause, and you
> will get 'any' ApplicantID.
>
No, I was trying to use the following:
Select
ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
from logon l join Applicant a on (l.email=a.email) where l.email =
'tfs@.dlink.com'
which does work fine if I take all fields out. I want the max ApplicantID,
because it will be the last ApplicantID assigned for this email address.
Tom
> Otherwise, just use the technology (i.e. use a GROUP BY clause) and
> trust the product, or do some actual performance testing instead of
> asking the impossible. Chances are that you would even see a significant
> (measurable) performance difference between the different methods,
> unless you're using a big table.
> Gert-Jan|||> No, I was trying to use the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> which does work fine if I take all fields out. I want the max
ApplicantID,
> because it will be the last ApplicantID assigned for this email address.
You need to get the MAX from a subquery. Also note that SQL Server will
have no idea if you want the MAX(ApplicantID) to have an e-mail address of
tfs@.dlink.com or not.|||--Down with JOINS, up with table variables:
DECLARE @.MyApplicant TABLE
(
email varchar(50),
pw varchar(50),
fname varchar(50),
lname varchar(50),
AppID int
)
--grab records of interest
INSERT INTO @.MyApplicant (email, pw, fname, lname)
SELECT l.email, l.password, l.firstName, l.LastName
FROM logon l
WHERE l.email = 'tfs@.dlink.com'
--grab details pertaining to the records of interest by key information
UPDATE @.MyApplicant
SET AppID =
(
SELECT Max(a.ApplicantID)
FROM Applicant a
WHERE a.email = myapp.email
)
FROM @.MyApplicant myapp
SELECT *
FROM @.MyApplicant
--assuming indexes on email field in all tables, it doesn't get much
faster than this.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
> I had a question similar to this before and can't seem to make this one
> work.
> I have the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> This works fine if I don't have the max function, but it can give me
> multiple responses so I just want the last ApplicantID. This also works
if
> I put the password, email,firstName and LastName in a Groupby clause. Can
I
> change the max statement to prevent having to use the Groupby Clause.
> Thanks,
> Tom
>|||This may be unreasonable of me, but when you say max(applicantID), is this
really the last one? Could you not assign an applicantId that had already
been used? Do you not have a date when the assignment was made that you can
use to get the last assignment?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23yQDFHmAFHA.3820@.TK2MSFTNGP11.phx.gbl...
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41F567CA.CC06163B@.toomuchspamalready.nl...
> No, I was trying to use the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> which does work fine if I take all fields out. I want the max
> ApplicantID, because it will be the last ApplicantID assigned for this
> email address.
> Tom
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uwWf3EpAFHA.2112@.TK2MSFTNGP09.phx.gbl...
> This may be unreasonable of me, but when you say max(applicantID), is this
> really the last one? Could you not assign an applicantId that had already
> been used? Do you not have a date when the assignment was made that you
> can use to get the last assignment?
In this particular case the applicantID is just a numeric ID that is
sequentially assigned. It happens to be an identity field. The max
applicantID will always be the latest one assigned.
Tom
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23yQDFHmAFHA.3820@.TK2MSFTNGP11.phx.gbl...
>
Preventing Extended Properties
Is there a way to tell SQL Server not to bother storing extended
properties?
I see no use for these, except bothering me when i am creating SQL
scripts for recreation on another database...
Any way to do that?
TascienHow do you mean "bothering" you?
How are you generating the scripts? Extended properties are excluded by
default from scripts generated by sql server (both EM and QA).
tascienu@.ecoaches.com wrote:
>Is there a way to tell SQL Server not to bother storing extended
>properties?
>I see no use for these, except bothering me when i am creating SQL
>scripts for recreation on another database...
>Any way to do that?
>Tascien
>
>|||I am using Adept SQL diff to compare my databases and extended
properties are bothering me... beside that, i really don't need them.
Anyway, sorry for bothering the group at this time, i've found a
solution for anyone else who doesn't like extended properties like
me...
1. Right click the SQL Server > Properties > Server Settings > and
Enable the checkbox "Allow modifications to be made directly to the
system catalog".
2. run the script below in the Query Analyser:
use [YourDatabaseName]
DELETE FROM sysproperties
3. Right click the SQL Server > Properties > Server Settings > and
"Disable" the checkbox "Allow modifications to be made directly to the
system catalog".
That should do!!!
Thank you for anyone who tried to help.
Tascien|||> DELETE FROM sysproperties
YIKES! I'm not sure this is such a good recommendation.|||Not good, but it works for me... USE AT YOUR OWN RISK!!!!
properties?
I see no use for these, except bothering me when i am creating SQL
scripts for recreation on another database...
Any way to do that?
TascienHow do you mean "bothering" you?
How are you generating the scripts? Extended properties are excluded by
default from scripts generated by sql server (both EM and QA).
tascienu@.ecoaches.com wrote:
>Is there a way to tell SQL Server not to bother storing extended
>properties?
>I see no use for these, except bothering me when i am creating SQL
>scripts for recreation on another database...
>Any way to do that?
>Tascien
>
>|||I am using Adept SQL diff to compare my databases and extended
properties are bothering me... beside that, i really don't need them.
Anyway, sorry for bothering the group at this time, i've found a
solution for anyone else who doesn't like extended properties like
me...
1. Right click the SQL Server > Properties > Server Settings > and
Enable the checkbox "Allow modifications to be made directly to the
system catalog".
2. run the script below in the Query Analyser:
use [YourDatabaseName]
DELETE FROM sysproperties
3. Right click the SQL Server > Properties > Server Settings > and
"Disable" the checkbox "Allow modifications to be made directly to the
system catalog".
That should do!!!
Thank you for anyone who tried to help.
Tascien|||> DELETE FROM sysproperties
YIKES! I'm not sure this is such a good recommendation.|||Not good, but it works for me... USE AT YOUR OWN RISK!!!!
Preventing expansion of a cross-tab column
Is it possible to prevent certain users from expanding a column (or
row) in a cross-tab/matrix report? That is, a lower level of
granularity in a report column is too detailed for a certain job role,
and you don't want that person to be able to expand that column out,
but you don't want to write multiple reports to accomplish this task.
Thanks.On May 10, 12:25 pm, kmac2...@.gmail.com wrote:
> Is it possible to prevent certain users from expanding a column (or
> row) in a cross-tab/matrix report? That is, a lower level of
> granularity in a report column is too detailed for a certain job role,
> and you don't want that person to be able to expand that column out,
> but you don't want to write multiple reports to accomplish this task.
> Thanks.
I would suggest setting a hidden parameter for the current user (=User!
UserID.ToString) in the report, then pass this hidden parameter to the
stored procedure/query that is sourcing the report and based on the
user's job role, do not return certain drill-down (etc) data. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||The suggestion helped a bit...I was able to use the user role to set
certain information null, so when the group is expanded in the matrix,
it doesn't do anything but add another header row. However, the +/-
sign is still there, and I'd like to hide it in these situations. I
found the place to work with this information in the Edit Group
properties of the matrix column, but the ToggleItem can't be an
expression. While I could set it to another textbox than the default
and hide -that- textbox (which would hide the +/-) it would remove the
context of the +/- in situations where I need it. I might be missing
something, but I can't figure how to make this work out. Any ideas?
row) in a cross-tab/matrix report? That is, a lower level of
granularity in a report column is too detailed for a certain job role,
and you don't want that person to be able to expand that column out,
but you don't want to write multiple reports to accomplish this task.
Thanks.On May 10, 12:25 pm, kmac2...@.gmail.com wrote:
> Is it possible to prevent certain users from expanding a column (or
> row) in a cross-tab/matrix report? That is, a lower level of
> granularity in a report column is too detailed for a certain job role,
> and you don't want that person to be able to expand that column out,
> but you don't want to write multiple reports to accomplish this task.
> Thanks.
I would suggest setting a hidden parameter for the current user (=User!
UserID.ToString) in the report, then pass this hidden parameter to the
stored procedure/query that is sourcing the report and based on the
user's job role, do not return certain drill-down (etc) data. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||The suggestion helped a bit...I was able to use the user role to set
certain information null, so when the group is expanded in the matrix,
it doesn't do anything but add another header row. However, the +/-
sign is still there, and I'd like to hide it in these situations. I
found the place to work with this information in the Edit Group
properties of the matrix column, but the ToggleItem can't be an
expression. While I could set it to another textbox than the default
and hide -that- textbox (which would hide the +/-) it would remove the
context of the +/- in situations where I need it. I might be missing
something, but I can't figure how to make this work out. Any ideas?
Subscribe to:
Posts (Atom)