user permissions setting times 1

[djj55]

Hello, in SQL 2000 is there a way to lock users out based on time of day. I still want admin/job access as several jobs will be run during this time.

Thank you,
djj

 

[ptheriault]

do you need to restrict access to a db so you can perform maint on it?

You could kill all the users in the db then restrict access to the db to members of dbo or sa.

Here is a script to kill all db users.

After you kill your spids just issue an alter db command to set the db to restict access to members of the db_owner or sa

DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'

CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30),
requestid int)

INSERT INTO #tmpUsers EXEC SP_WHO


DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DB HERE'

DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
        PRINT 'Killing ' + @spid
        SET @strSQL = 'KILL ' + @spid
        EXEC (@strSQL)
        END
        FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers


GO

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[djj55]

Exactly what I was looking for, have a star.
Thank you,
djj