HOWTO setup user profiles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

HOWTO setup user profiles

Author

Topic

shahidns
Starting Member

10 Posts

Posted - 2005-01-25 : 04:29:21

Hi all,
I am new with SQL Server and my experience is mostly in Oracle. I am looking at setting up user profiles in SQL Server. In a particular instance we want to setup profiles where a particular user session needs to be automatically killed after 30 mins of login time.
I know of profiles in Oracle, but how does it work in SQL Server.

Thanks in advance.
Shahid.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-25 : 07:26:39

suppose I want to keep track of login 'qwe'... then smth like this:

declare @spid int, @login_time datetime, @sql varchar(30)

select @spid=spid, @login_time=login_time
from master..sysprocesses where loginame='qwe'

if datediff(mi, @login_time, getdate())>30
begin
set @sql=cast(@spid as varchar(4))
exec('kill '+@sql)
end

No problem to wrap the above (without its "declare" part) into an infinite loop:

while 1=1
begin
... ... ...
waitfor delay '00:10:00' ----- check for culprits every 10 minutes
end

then put the loop into a sproc;
and add the sproc into the list of startup procs --
those which executed right after starting of sql server service;

shahidns
Starting Member

10 Posts

Posted - 2005-01-25 : 11:38:54

Great reply. I will try out this code and hope it helps. But I am surprised SQL Server does not have anything like profiles in SQL Server which will automatically kill a session as per the described criteria.

Thanks a lot.
Shahid.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-25 : 12:23:31

I was suprised that 8i didn't have CASE

And SQLPlus...now there was one painful interface....and no way to export data, except with a cursor and utl_file.put_line

I haven't seen 9i...did the ever create an export utility?

Brett

8-)

shahidns
Starting Member

10 Posts

Posted - 2005-01-25 : 23:51:55

I see you are anti-Oracle person. I am not much of a fan of Oracle considering it takes me about 30 mins to setup a standby database in SQL Server compared to half a day in Oracle.
However, I have also worked on Ingres and I think that RDBMS is more stable and reliable than any.

Shahid.

PS: Oracle had a export utility all the way back in version 6.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-26 : 09:18:52

quote:
Originally posted by shahidns

I see you are anti-Oracle person.

Not true..it's just a different way of thinking...and hey with a DB2 OS/390 background...Oracle is a pleasure...

And what export utility (that ships with Oracle) are yu referring to?

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-03 : 08:14:33

quote:
I was suprised that 8i didn't have CASE

It did, I've used it. I think it was also in 7.3 or whatever the last 7 version was. Except it only supported:

SELECT CASE WHEN a=1 THEN 0 END

and not

SELECT CASE a WHEN 1 THEN 0 END

Of course, REAL Oracle people despise such ANSI-standard features and would insist on using DECODE() all the time.

They also have no need for ANSI JOIN syntax.

shahidns
Starting Member

10 Posts

Posted - 2005-02-06 : 10:57:18

Thanks Stoad the script worked beautifully. However, it only looked at the last record. Hence only one spid was being killed. I modified the script to my requirements and added it in a cursor and is working perfectly.

declare
@spid int,
@login_time datetime,
@sql varchar(30)
begin
declare sessioncursor cursor for select spid, login_time
from master..sysprocesses where loginame='Kisapp' and upper(hostname)='DXBMIWW21' and program_name like '%.Net%'
open sessioncursor
fetch next from sessioncursor into @spid,@login_time
while @@fetch_status=0
begin
print datediff(mi, @login_time, getdate())
if datediff(mi, @login_time, getdate())>10
begin
set @sql=cast(@spid as varchar(4))
exec('kill '+@sql)
end
fetch next from sessioncursor into @spid,@login_time
end
end

Thanks again.
Regards.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-07 : 08:31:12

Glad to hear, Shahid! Only one thing I'd like to mention.
Almost always we can replace Cursor with simple While Loop (and it's worthy to do).
In your case it can be done like this (easy technique to understand for an Oracle man):

declare
@spid int, @sql varchar(30)
set @spid=0

while 1=1
begin
select top 1 @spid=spid from master..sysprocesses where
loginame='Kisapp' and upper(hostname)='DXBMIWW21' and
program_name like '%.Net%' and
datediff(mi, login_time, getdate())>10 and spid>@spid
order by spid
if @@rowcount=0 break
set @sql=cast(@spid as varchar(4))
exec('kill '+@sql)
end

==================

Just for fun I tested "Cursor vs While Loop" against a huge table with real data
and with composite PK on these three columns: dp smalldatetime, nz int, np int:

While Loop:
declare @dp smalldatetime, @nz int, @np int
select @dp='19440101', @nz=0, @np=0
while 1=1
begin
select top 1 @dp=dp, @nz=nz, @np=np from t where
(dp>=@dp and nz>=@nz and np>=@np) and not(dp=@dp and nz=@nz and np=@np)
order by dp, nz, np
if @@rowcount=0 break
end

Cursor:
declare @dp smalldatetime
declare abc cursor FAST_FORWARD for select dp from t
open abc
fetch next from abc into @dp
while @@fetch_status=0
fetch next from abc into @dp
close abc
deallocate abc

Amazingly but the While Loop was faster ~15 times than the Cursor.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-07 : 13:21:10

quote:
Originally posted by Stoad

Just for fun I tested "Cursor vs While Loop" against a huge table with real data
and with composite PK on these three columns: dp smalldatetime, nz int, np int:

While Loop:
declare @dp smalldatetime, @nz int, @np int
select @dp='19440101', @nz=0, @np=0
while 1=1
begin
select top 1 @dp=dp, @nz=nz, @np=np from t where
(dp>=@dp and nz>=@nz and np>=@np) and not(dp=@dp and nz=@nz and np=@np)
order by dp, nz, np
if @@rowcount=0 break
end

lol, people, my greatest oops!
Of course, the WHERE condition in my quasi-cursor is WRONG.
Must be like this:

select top 1 @dp=dp, @nz=nz, @np=np from t where

~~(dp>=@dp and nz>=@nz and np>=@np) and not(dp=@dp and nz=@nz and np=@np)~~

(dp=@dp and nz=@nz and np>@np)
or
(dp=@dp and nz>@nz)
or
dp>@dp

order by dp, nz, np

No wonder it was so fast compared to the true cursor and means I have to re-test it.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-08 : 01:45:29

after re-testing: still faster than the cursor but "only" 1.5 times.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources