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 the replication environment

Is it possible to restoring the master db ? This is to
restore the merge agents settings, without creating the
merge agents again? If any one got an answer for this
please reply asap.
Binoy,
replication agents and the master database are two separate issues. In
master there is sysservers and sysxlogins, both of which are relevant to the
replication setup. However, you are concerned about the merge agent jobs and
parameters which are in msdb (sysjobs, MSagent_profiles, MSagent_parameters
etc). For a good set of details on the replication backup strategy, you can
have a look in BOL for 'replication, backup and restore
operations,Strategies for Backing Up and Restoring Merge Replication'.
HTH,
Paul Ibison

restoring sql 2000 backup on sql 7

Hi,

I am working in a sql server 2000 environment but my customer uses sql server 7. Is it possible to use backup and restore to transfer the database or should I set up a sql server 7 for development?

The database only contains tables and views...

Thanks
Koen

You may have to move the tables and Views and you cannot enable relationship there is none in 7.0 and in Belgium if the 7.0 is not using unicode you cannot use Unicode because to Unicode enable you must reinstall. There are many differences with those versions it is hard to tell you in one post. The best thing to do ask the customer for the personal edition which is free to them so you can develop directly to 7.0 Hope this helps.

Restoring Database using Scripts

I have and MSDE environment and I use "OSQL" to administer the database. I've written a backup script and restore script. My problem/question as to do with the restore. Both scripts run just fine.
However, I want my restore to use the lastest and greatest backup set within the backup device. I never really know what the lastest backup set is most of the time. But when ever my restore process runs I want it to use the lastest backup within the back
up device. How do I specify in my script to use the lastest backup set when a restore is done?
Thanks
Hi,
If you are going to restore in the same server see the MSDB..BACKUPSET table
for position. See the below query.
select database_name,backup_finish_date,position from msdb..backupset where
database_name='msdb'
If you are doing it in a different machne use the below command.
restore headeronly from disk='c:\msdb.bak'
You can restore the latest backup based on position
Thanks
Hari
MCDBA
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:C6B6DEC9-1CA4-4624-A6F9-2A7004037BC1@.microsoft.com...
> I have and MSDE environment and I use "OSQL" to administer the database.
I've written a backup script and restore script. My problem/question as to
do with the restore. Both scripts run just fine.
> However, I want my restore to use the lastest and greatest backup set
within the backup device. I never really know what the lastest backup set
is most of the time. But when ever my restore process runs I want it to use
the lastest backup within the backup device. How do I specify in my script
to use the lastest backup set when a restore is done?
> Thanks
|||hi Larry,
"Larry Bird" <LarryBird@.discussions.microsoft.com> ha scritto nel messaggio
news:C6B6DEC9-1CA4-4624-A6F9-2A7004037BC1@.microsoft.com...
> I have and MSDE environment and I use "OSQL" to administer the database.
>I've written a backup script and restore script. My problem/question as to
do with
>the restore. Both scripts run just fine.
> However, I want my restore to use the lastest and greatest backup set
within the
> backup device. I never really know what the lastest backup set is most of
the time.
>But when ever my restore process runs I want it to use the lastest backup
within the
>backup device. How do I specify in my script to use the lastest backup set
when a
>restore is done?
Im'm late, and Hari already pointed out what needed, but, anyway =;-D
I woul'd not automatically scritp this... restore shoul'd be better a manual
operation as it's not a dayly house keeping activity...
anyway... you can start from getting the max file position...
SET NOCOUNT ON
DECLARE @.db VARCHAR(128)
DECLARE @.file VARCHAR(128)
DECLARE @.cmd VARCHAR(1024)
SELECT @.file = 'D:\ADOvb6\VBH_FILE\vbhotel.bak' , @.db = 'vbhotel'
SET @.cmd = 'RESTORE HEADERONLY FROM DISK = '''+ @.file + ''''
CREATE TABLE #restore (
BackupName VARCHAR(128) ,
BackupDescription VARCHAR(128) ,
BackupType INT ,
ExpirationDate DATETIME ,
Compressed INT ,
Position INT ,
DeviceType INT ,
UserName VARCHAR(128) ,
ServerName VARCHAR(128) ,
DatabaseName VARCHAR(128) ,
DatabaseVersion INT ,
DatabaseCreationDate DATETIME ,
BackupSize INT ,
FirstLsn VARCHAR(128) ,
LastLsn VARCHAR(128) ,
CheckPointLsn VARCHAR(128) ,
DifferentialBaseLsn VARCHAR(128) ,
BackupStartDate DATETIME ,
BackupFinishDate DATETIME ,
SortOrder SMALLINT ,
CodePage SMALLINT ,
UnicodeLocaleId INT ,
UnicodeComparisonStyle INT ,
CompatibilityLevel TINYINT ,
SoftwareVendorId INT ,
SoftwareVersionMajor INT ,
SoftwareVersionMinor INT ,
SoftwareVersionBuild INT ,
MachineName NVARCHAR(128) ,
Flags INT ,
BindingId uniqueidentifier ,
RecoveryForkId uniqueidentifier ,
Collation NVARCHAR(128)
)
INSERT INTO #restore EXEC(@.cmd)
SELECT MAX(r.Position) AS [MaxFile]
FROM #restore r
WHERE r.BackupType = 1
AND r.DatabaseName = @.db
AND r.MachineName = @.@.SERVERNAME
GO
DROP TABLE #restore
--<--
MaxFile
1
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Restoring Database in a clustered environment

Can someone tell me how to make a backup in a sql clustered environment (MSSQL 2000) and how to restore that backup in clustered environment (MSSQL2005)? A step by step backup and restore procedure is appreciated.

regardless of if the server the db is on is clustered or not, the same principles apply

backup database dbname to file = 'c:\backup.bak'

same goes with the restore

here is an article that will help:

http://www.sqlteam.com/item.asp?ItemID=26689

|||

Hi Tim,

Yes the db is on clustered.. do i need to backup each db on each node? or just backup the active node's db?

|||

If the cluster is active/active (two instances, one on each node), and there are databases on the 2nd node that you want to backup, then you'll likely need to back that db up as well. Otherwise, you can backup the database on the active node.

The thing about a clustered environment is that it can host multiple instances. These instances are mutually exclusive from each other, so you can treat them like they are different server instances, even though at the time they may be on one node. So, just backup the databases as you normally would as if it was a nonclustered environment.

HTH,

Tim

|||

To take this to the simplest case:

Take a 2 node cluster with 1 instance, and 1 user database:

There is only one physical copy of that database. At any point in time, it may be mounted on node "A" or node "B".

The instance name is always the same, regardless of which physical machine hosts it.

So, to back up the database, you run a backup job on whichever node is currently hosting the instance.

Ideally, your backups go to media which is accessable from all cluster nodes, so that restores are clean as well. (another shared disk, or a network share if perf isn't as critical)

Restore is just the same: Go to the node which has your instance currently hosted (or move the instance to the node that you're currently on), and run restore.

For more complex configurations, just take the above and iterate for each instance.

There are 3rd party backup products which operate over the network, which will automatically go to the node currently hosting a database (via the instance's virtual hostname) and do the backup/restore on the appropriate machine.