Restrict Logins

Hi All:
I'm running SQL Server 7 in an NT environment using SQL Server
Authentication for the logins.
I'm a developer who has recently accepted the responsibilities for SQL
Server maintenance. I'm not really qualified to do this, but I'm
learning. Lord knows I need all the help and suggestions I can get;
I'm readily maintaining the status quo and make any modifications only
after what I consider exhaustive testing on our "test server". I've
just been handed the task of restricting certain logins to a specific
machine.
I do know that when I look at the running processes in EM I can see
the machines the users are connecting from.
Is there some way I can trap this information, issue an alert that
will start a job to kill the offending process? Or, is there some way
I can use DMO to do this'
I had originally thought that maybe I could intercept the login and
make the appropriate checks and deny the login... But, I haven't
found anything that indicates this approach is "do-able".
Then I found the active processes information and I began to wonder if
there wasn't some way to trap this information and issue a process
kill all in unattended mode.
I'm a developer and am not "married" to the EM ( it does make life and
learning easier).
Any and all help and suggestions are definitely appreciated.
TIA,
cathyHi,
If it is SQL Server Login or Windows user connecting to SQL server then you
can DENY the user to access the tables, If it is Host name
then you cant deny or revoke directly , But you can follow the below steps
to kill the user.
1. Create a script which looks like:-
use master
go
declare @.x varchar(1000)
set @.x=''
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where hostname = 'host_name' -- replace this with the actual host name
exec (@.x)
go
2. Schedule this script every 1 minute daily in SQL Agent -- Jobs. This will
check and the process if it logs in from the host name you mentioned.
Thanks
Hari
MCDBA
"Catherine Finnegan" <remove_for_nospam_caeriel1@.cablespeed.com> wrote in
message news:erfa90tjrncj89pugk4qr1bp7trkegui5n@.
4ax.com...
> Hi All:
> I'm running SQL Server 7 in an NT environment using SQL Server
> Authentication for the logins.
> I'm a developer who has recently accepted the responsibilities for SQL
> Server maintenance. I'm not really qualified to do this, but I'm
> learning. Lord knows I need all the help and suggestions I can get;
> I'm readily maintaining the status quo and make any modifications only
> after what I consider exhaustive testing on our "test server". I've
> just been handed the task of restricting certain logins to a specific
> machine.
> I do know that when I look at the running processes in EM I can see
> the machines the users are connecting from.
> Is there some way I can trap this information, issue an alert that
> will start a job to kill the offending process? Or, is there some way
> I can use DMO to do this'
> I had originally thought that maybe I could intercept the login and
> make the appropriate checks and deny the login... But, I haven't
> found anything that indicates this approach is "do-able".
> Then I found the active processes information and I began to wonder if
> there wasn't some way to trap this information and issue a process
> kill all in unattended mode.
> I'm a developer and am not "married" to the EM ( it does make life and
> learning easier).
>
> Any and all help and suggestions are definitely appreciated.
> TIA,
> cathy

Restoring Master Database errors on SQL 2000 SP3a

Hi All
I work in a envoriment where i restore lots of master
database for specfic reason.
The problem i am currently encountering is the following
When i attempt to restore the Master database is SQL
Enterprise Manager (in single user mode) i get a error
box with a title of sql-dmo odbc sqlstate: HY000 and
nothing else.
It then kills the SQL service which i can then restart as
normal but the master database is not restored.
If i attempt to restore via SQL Query Anylzer i just get
connect broken and the SQL service is stopped.
The only way i have found to restore my master database is
to reinstall SQL from scratch which is very time consuming.
Has anyone else seen this and now how to workaround this
issue with out reinstalling SQL
Many Thanks
Martin Cheetham
Hi,
I have never ended up in this situation. I recommend you to try rebuilding
the MASTER database using REBUILDM.EXE rather than reinstalling SQL Server.
After rebulding the master you can restore the database
using the normal procedure
Thanks
Hari
MCDBA
"Martin Cheetham" <martin_cheetham@.yahoo.co.uk> wrote in message
news:237a101c45e86$4c7f5b60$a401280a@.phx.gbl...
> Hi All
> I work in a envoriment where i restore lots of master
> database for specfic reason.
> The problem i am currently encountering is the following
> When i attempt to restore the Master database is SQL
> Enterprise Manager (in single user mode) i get a error
> box with a title of sql-dmo odbc sqlstate: HY000 and
> nothing else.
> It then kills the SQL service which i can then restart as
> normal but the master database is not restored.
> If i attempt to restore via SQL Query Anylzer i just get
> connect broken and the SQL service is stopped.
> The only way i have found to restore my master database is
> to reinstall SQL from scratch which is very time consuming.
> Has anyone else seen this and now how to workaround this
> issue with out reinstalling SQL
> Many Thanks
> Martin Cheetham

Restoring Master Database errors on SQL 2000 SP3a

Hi All
I work in a envoriment where i restore lots of master
database for specfic reason.
The problem i am currently encountering is the following
When i attempt to restore the Master database is SQL
Enterprise Manager (in single user mode) i get a error
box with a title of sql-dmo odbc sqlstate: HY000 and
nothing else.
It then kills the SQL service which i can then restart as
normal but the master database is not restored.
If i attempt to restore via SQL Query Anylzer i just get
connect broken and the SQL service is stopped.
The only way i have found to restore my master database is
to reinstall SQL from scratch which is very time consuming.
Has anyone else seen this and now how to workaround this
issue with out reinstalling SQL
Many Thanks
Martin CheethamHi,
I have never ended up in this situation. I recommend you to try rebuilding
the MASTER database using REBUILDM.EXE rather than reinstalling SQL Server.
After rebulding the master you can restore the database
using the normal procedure
Thanks
Hari
MCDBA
"Martin Cheetham" <martin_cheetham@.yahoo.co.uk> wrote in message
news:237a101c45e86$4c7f5b60$a401280a@.phx
.gbl...
> Hi All
> I work in a envoriment where i restore lots of master
> database for specfic reason.
> The problem i am currently encountering is the following
> When i attempt to restore the Master database is SQL
> Enterprise Manager (in single user mode) i get a error
> box with a title of sql-dmo odbc sqlstate: HY000 and
> nothing else.
> It then kills the SQL service which i can then restart as
> normal but the master database is not restored.
> If i attempt to restore via SQL Query Anylzer i just get
> connect broken and the SQL service is stopped.
> The only way i have found to restore my master database is
> to reinstall SQL from scratch which is very time consuming.
> Has anyone else seen this and now how to workaround this
> issue with out reinstalling SQL
> Many Thanks
> Martin Cheetham