New Login Question

Hello,
I have been programming with Sql Server 2000 for 2-3 years
now. But I am just now starting out with having to also
be the DBA of Sql Server 2000. My question is about
creating new Logins.
I noticed that at the bottom of the New login dialog there
are 2 dropdown boxes. One selects a database to log in to
and the other is the language. The database login
dropdown always has master listed. If I leave that alone
and then go to the Database access tab where I select what
database the new login can access I leave master in that
dialog unchecked but I check some other database. If a
user creates a connection with a tool like MS Access 2002
adp, the user can connect to master but can't access
anything because I did not check master in the Database
access tab of the New Login dialog. So my question is -
what is the significance of the database dropdown on the
first dialog of the Login dialog? If I change the
database dropdown from Master to just the desired
database, then the user cannot connect to master in
addition to not being able to access any objects in
master. But does master contain login information? If
the user is only going to be creating ODBC connections to
the desired Sql DB, does the user need to be able to
connect to master?
Thanks,
RonI see your dilemma. The answer to whether the user needs to be able to
connect to master, I don't know. But the syslogins table does exist there.
So any underlying tables that are affected by it will not show if you don't
choose it from the dropdown. I'm sure there are other system tables in maste
r
like sysobjects, sysdatabases etc that require a reference as well...
SELECT name, [password], dbname, language, sid,
'skip_encryption', loginname
FROM master..syslogins
"Ron" wrote:

> Hello,
> I have been programming with Sql Server 2000 for 2-3 years
> now. But I am just now starting out with having to also
> be the DBA of Sql Server 2000. My question is about
> creating new Logins.
> I noticed that at the bottom of the New login dialog there
> are 2 dropdown boxes. One selects a database to log in to
> and the other is the language. The database login
> dropdown always has master listed. If I leave that alone
> and then go to the Database access tab where I select what
> database the new login can access I leave master in that
> dialog unchecked but I check some other database. If a
> user creates a connection with a tool like MS Access 2002
> adp, the user can connect to master but can't access
> anything because I did not check master in the Database
> access tab of the New Login dialog. So my question is -
> what is the significance of the database dropdown on the
> first dialog of the Login dialog? If I change the
> database dropdown from Master to just the desired
> database, then the user cannot connect to master in
> addition to not being able to access any objects in
> master. But does master contain login information? If
> the user is only going to be creating ODBC connections to
> the desired Sql DB, does the user need to be able to
> connect to master?
> Thanks,
> Ron
>|||The default database option is just that, a default database to connect to
if one is not specified in the connection string/DSN. More often than not
when a user creates a DSN or a connection string they will specify a
database. All logins have access to the master database via the guest user
(a special database user account that cannot be removed from master) and do
not need to be explicitly granted access to that database. When setting up a
new login via Enterprise Manager, you simply need to select which databases
the login can access and also what permissions that user has access to with
the database.When you grant access to a database for a login, they are added
to the public role, however this role generally has no access to user
database objects (and not should it). Generally you would create a new role
in the database and grant the required permissions to that role. THen you
can simply add the user to that role. At the login level, you should be
using groups to access SQL Server so that when a new user needs access to an
existing database you can simply add them to a Windows group and not have to
touch the SQL Server at all.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:100301c53aba$a7a56690$a501280a@.phx.gbl...
> Hello,
> I have been programming with Sql Server 2000 for 2-3 years
> now. But I am just now starting out with having to also
> be the DBA of Sql Server 2000. My question is about
> creating new Logins.
> I noticed that at the bottom of the New login dialog there
> are 2 dropdown boxes. One selects a database to log in to
> and the other is the language. The database login
> dropdown always has master listed. If I leave that alone
> and then go to the Database access tab where I select what
> database the new login can access I leave master in that
> dialog unchecked but I check some other database. If a
> user creates a connection with a tool like MS Access 2002
> adp, the user can connect to master but can't access
> anything because I did not check master in the Database
> access tab of the New Login dialog. So my question is -
> what is the significance of the database dropdown on the
> first dialog of the Login dialog? If I change the
> database dropdown from Master to just the desired
> database, then the user cannot connect to master in
> addition to not being able to access any objects in
> master. But does master contain login information? If
> the user is only going to be creating ODBC connections to
> the desired Sql DB, does the user need to be able to
> connect to master?
> Thanks,
> Ron

New Log or append?

I'm backing into the DBA position from being an app / web designer. Using SQL 2005 and hating wizards, I'm trying to code a BACKUP LOG query to run every 15 minutes and create a separate date-time-stamped TRN. I have several questions starting with - should I be doing this? Is it better to create separate LOGs or append to one? Isn't it true that the only time I'll need the LOGs is when I have a crash and that then I'll need to RESTORE in date-time-order?

I'm doing a FULL backup every 6 hours and intend on automating deleting the TRNs on a successful completion of the BAK.

The following code runs fine and creates a date-time-stamped TRN but doesn't do what I want, as it overwrites the first TRN, so how do I create, say, four JOBSTEPs and get a JOBSTEP @.COMMAND to run the SETs?

USE msdb

IFEXISTS(SELECTNameFROM sysjobs WHEREName='jobDBBackupWedb_1Log')

EXECsp_delete_job @.job_name ='jobDBBackupWedb_1Log'

GO

DECLARE @.now char(14)-- current date in the form of yyyymmddhhmmss

DECLARE @.dbName sysname-- database name to include date-time-stamp

DECLARE @.Cmd nvarchar(260)-- variable used to build JobStep command

SET @.now =REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50),GETDATE(), 120),'-',''),' ',''),':','')

SET @.dbName ='Wedb_1_'+ @.now +'_.trn'

SET @.Cmd ='BACKUP LOG Wedb_1 TO DISK = '+CHAR(39)+'\\SERVER1\D$\SQLDatabases\Backup\WebEoc\'+ @.dbName +CHAR(39)

EXECsp_add_job

@.Job_Name ='jobDBBackupWedb_1Log',

@.Description ='Run Wedb_1 DB Backup Transaction Log Every 15 Minutes',

@.Category_Name ='Database Maintenance'

EXECsp_add_jobstep

@.Job_Name ='jobDBBackupWedb_1Log',

@.step_name ='DB_Backup_Log',

@.step_id = 1,

@.Database_Name ='Master',

@.subsystem ='TSQL',

@.command = @.Cmd

/*

Start time for the Transaction Log backups is seven and one half minutes after midnight

to not conflict with the Full database backups run every 6 hours starting at midnight.

The Transaction Log backups are to be run every fifteen minutes.

*/

EXECsp_Add_JobSchedule

@.Job_Name ='jobDBBackupWedb_1Log',

@.Name ='schedDB_BackupLog',

@.Freq_Type = 4,-- Daily

@.Freq_Interval = 1,-- Each day

@.Freq_Subday_Type = 0x4,-- Interval in minutes

@.Freq_Subday_Interval = 15,-- Every fifteen minutes

@.Active_Start_Time = 000730 -- Start Time is seven and one half minutes after midnight

--@.Active_End_Time = 235959 -- End Time is next midnight

EXECsp_Add_JobServer

@.Job_Name ='jobDBBackupWedb_1Log'

GO

My opinion, I'd create DB Maint Plans to do database and trans log backups, and not write my own, unless there is some real good reason to. Main plans aren't perfect, but still better then trying to reproduce the same end result. One of the points of backing up the trans log is for disaster recovery. You could backup your trans log every 30 minutes, then copy/move those backups off to a different server/tape, etc... So, I wouldn't stack them on one file, becuase you need to move them continually to a different box, or yea, backup cross server, ehhhh.... I prefer local backups and then copying them somewhere else, like with RoboCopy. The times you nee the trans logs are for recovery, or to do log shipping to a different server (similar concept to database mirroring, but still more in use then mirroring is I think). If doing a full backup every 6 hours, same thing, back it up and move it off somewhere safe, not leave that on teh same drives as your database files! I'd still leave the old TRN files on teh other location, there COULD be a case where you need to go back 9 hours, then you'd have the old trans and backups to get there, I wouldnt delete the TRN files every 6 hours as you said.

Bruce

|||

Thanks ... but ...

I'm backing up to a separate / different network drive on a different / separate server that my network admin has established as a SANs - Storage Area Network - drive. That SANs drive is data specific and is being snap-shot-backed-up every 5 minutes. Then it goes to tape every night at 11:00 PM.

The nature of the DB is that the WebEoc is for running the SEOC - State Emergency Operations Center - during hurricanes, earthquakes, etc.

|||

I think that the problem with your original script is that you are creating the backup command when you create the job rather than dynamically creating and executing the command when executing the job.

Try the re-worked version below.

Chris

Code Snippet

USE msdb

IF EXISTS ( SELECT Name
FROM sysjobs
WHERE Name = 'jobDBBackupWedb_1Log' )
EXEC sp_delete_job @.job_name = 'jobDBBackupWedb_1Log'

GO

DECLARE @.cmd NVARCHAR(3201)

SET @.cmd = N'DECLARE @.sql VARCHAR(8000)

DECLARE @.now char(14)
-- current date in the form of yyyymmddhhmmss

DECLARE @.dbName sysname
-- database name to include date-time-stamp

SET @.now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), ''-'',
''''), '' '', ''''), '':'', '''')

SET @.dbName = ''Wedb_1_'' + @.now + ''_.trn''

SET @.sql = ''BACKUP LOG Wedb_1 TO DISK = '' + CHAR(39)
+ ''\\SERVER1\D$\SQLDatabases\Backup\WebEoc\'' + @.dbName + CHAR(39)

EXEC(@.sql)'
EXEC sp_add_job @.Job_Name = 'jobDBBackupWedb_1Log',
@.Description = 'Run Wedb_1 DB Backup Transaction Log Every 15 Minutes',
@.Category_Name = 'Database Maintenance'

EXEC sp_add_jobstep @.Job_Name = 'jobDBBackupWedb_1Log',
@.step_name = 'DB_Backup_Log', @.step_id = 1, @.Database_Name = 'Master',
@.subsystem = 'TSQL', @.command = @.Cmd

/*

Start time for the Transaction Log backups is seven and one half minutes after midnight

to not conflict with the Full database backups run every 6 hours starting at midnight.

The Transaction Log backups are to be run every fifteen minutes.

*/

EXEC sp_Add_JobSchedule @.Job_Name = 'jobDBBackupWedb_1Log',
@.Name = 'schedDB_BackupLog', @.Freq_Type = 4, -- Daily
@.Freq_Interval = 1, -- Each day
@.Freq_Subday_Type = 0x4, -- Interval in minutes
@.Freq_Subday_Interval = 15, -- Every fifteen minutes
@.Active_Start_Time = 000730
-- Start Time is seven and one half minutes after midnight

--@.Active_End_Time = 235959 -- End Time is next midnight

EXEC sp_Add_JobServer @.Job_Name = 'jobDBBackupWedb_1Log'

GO

|||

Chris,

Thank you very kindly! I take your comment about run-time, create/execute; that was spot on. Thanks for the code change - works like a charm ...

... but, I've looked and not found an answer, what is the "N" doing for me in SET @.cmd = N'DECLARE ... ?

Is it declaring that what follows is all an NVARCHAR string?