11 February 2007 15:41
tonyrogerson
Prevent users from reconnecting in SQL Server
Here you go Dominic, for SQL Server 2005 this will work.
The big problem we have with SQL Server of old is that its been difficult to kick people out and keep them kicked out; even using the "Single User Mode" stuff its difficult because often somebody gets before you can take control of the database.
There is another way; there is a TSQL statement we can use ->
DENY CONNECT SQL TO [someuser] -- Prevents somebody logging on
GRANT CONNECT SQL TO [someuser] -- Allows them to log on
The DENY CONNECT only prevents somebody from connecting, it does not affect current connections - so if your user is already logged on then you need to do something else (ring them first and ask nicely and then KILL the connection, or if you need to get to the pub then KILL first and take the ear ache later).
The procedure below automates this process for you; its pretty basic and I could do a lot more to jazz it up but its a start...
CREATE PROC control_user_access
@op varchar(5),
@db sysname
AS
BEGIN
/******
Author: Tony Rogerson, 2007
http://sqlblogcasts.com/blogs/tonyrogerson
Feel free to use at will but don't rip it off as your own!
******/
IF @op NOT IN ( 'GRANT', 'DENY' )
BEGIN
RAISERROR( '@op takes values GRANT or DENY', 16, 1 )
END
ELSE
BEGIN
-- Prevent Logins from getting into SQL Server.
DECLARE @sql varchar(max)
SET @sql = '
declare logins_cur cursor for
select sp.name
from [' + @db + '].sys.database_principals du
inner join master.sys.server_principals sp on sp.sid = du.sid
where du.principal_id > 1'
EXEC( @sql )
DECLARE @login_name sysname
OPEN logins_cur
FETCH NEXT FROM logins_cur INTO @login_name
WHILE @@fetch_status = 0
BEGIN
SET @sql = @op + ' CONNECT SQL TO [' + @login_name + ']'
PRINT @sql
EXEC( @sql )
FETCH NEXT FROM logins_cur INTO @login_name
END
DEALLOCATE logins_cur
-- Kill the logins in the database
DECLARE sessions_cur CURSOR FOR
SELECT spid
FROM master..sysprocesses -- Have to use this because DMV's dont give me the dbid
WHERE dbid = DB_ID( @db )
OPEN sessions_cur
DECLARE @session_id int
FETCH NEXT FROM sessions_cur INTO @session_id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'KILL ' + CAST( @session_id AS varchar(10) )
PRINT @sql
EXEC( @sql )
FETCH NEXT FROM sessions_cur INTO @session_id
END
DEALLOCATE sessions_cur
END
END
To use it, just pass @op as DENY or GRANT depending on what you are doing and then specify the database you are going to work on.
There are other ways of doing this but I wanted to throw this one into the mill.
Tony.
Filed under: SQL Server