How to create login with default database

Hi,

I can't performe this:

use databaseX

CREATE LOGIN [abc]

WITH PASSWORD ='xxxxxxxxxxxxx',

DEFAULT_DATABASE = db_name()

or

DEFAULT_DATABASE = [ db_name() ] as well

I know that

DEFAULT_DATABASE = databaseX

works, but how can I create login with default database with db_name() ?

thanks,

You have to use dynamic SQL to form the CREATE LOGIN statement and execute it. Most DDL parameters only support literals or variables as values. Ex:

declare @.db nvarchar(130)

set @.db = quotename(db_name())

exec ('create login [abc] with password = ''xxxxx'', default_database = ' + @.db)

|||

Or, you could use sp_defaultdb (note that this sp is deprecated and may be removed from future versions according to MS):

USE MyDb
go

CREATE LOGIN [abc]
WITH PASSWORD ='xxxxxxxxxxxxx';

declare @.db sysname;

set @.db = db_name();

exec sp_defaultdb [abc], @.db

|||

thank you guys,

Can I execute this command from V.B, PowerBuilder or Delphi as well " by ODBC " ?

Or can I just do from Sql environment?

thanks,

|||

Hi Alessandro,

You can execute this from VB/.NET as well. For example, you can execute it against a valid ado connection object, via smo etc.

Cheers

Rob

How to create a Maintenance Plan and backup to multiple locations

Hi all,
Currently, I have a maintenance plan and it creates a complete backup to the
default backup directory on the local machine.
What should I do if I also want to back up the database to another
machine/share drive on the same domain? Do I need to create a new
maintenance plan just for that? Could this create unnecessary performance
hit on the SQL Server?
Or could I go directly to the SQL job and modify the script so that it can
back up to multiple locations? I donâ't know how to do this or whether it
will work at all.
Sure I can create a NT backup to copy the database to another location. But
I really like the "Remove file older than..." feature on the maintenance
plan. Our database is pretty big and the currently backup mechanism faces
space issue without this "Remove file older than..." feature.
Any advice? And thanks in advance.
Abel ChanYou can send the backup file anywhere you want as long as the account that
SQL Server runs under has permissions to that share or folder. But with
SQL2000 you can not backup to two locations at once. SQL2005 gives you more
options in that area. It is rarely a good idea to backup to the default
directory since if that machine dies you loose botht he db and the most
current backup. You might want to think about backing up to a remote share.
Just use a UNC for the path in the MP wizard (or edit the job) and you
should be all set.
--
Andrew J. Kelly SQL MVP
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:341680AD-9EF2-4574-9BF6-CE4E391DF2D8@.microsoft.com...
> Hi all,
> Currently, I have a maintenance plan and it creates a complete backup to
> the
> default backup directory on the local machine.
> What should I do if I also want to back up the database to another
> machine/share drive on the same domain? Do I need to create a new
> maintenance plan just for that? Could this create unnecessary performance
> hit on the SQL Server?
> Or could I go directly to the SQL job and modify the script so that it can
> back up to multiple locations? I don't know how to do this or whether it
> will work at all.
> Sure I can create a NT backup to copy the database to another location.
> But
> I really like the "Remove file older than..." feature on the maintenance
> plan. Our database is pretty big and the currently backup mechanism faces
> space issue without this "Remove file older than..." feature.
> Any advice? And thanks in advance.
> Abel Chan
>|||Hi Andrew,
Thanks to your response. I took your suggestion and back up the database to
another media. :> I also found that I need to run the SQL server under a
domain account instead of Local System in order to use UNC path. Thanks
again.
Abel Chan
"Andrew J. Kelly" wrote:
> You can send the backup file anywhere you want as long as the account that
> SQL Server runs under has permissions to that share or folder. But with
> SQL2000 you can not backup to two locations at once. SQL2005 gives you more
> options in that area. It is rarely a good idea to backup to the default
> directory since if that machine dies you loose botht he db and the most
> current backup. You might want to think about backing up to a remote share.
> Just use a UNC for the path in the MP wizard (or edit the job) and you
> should be all set.
> --
> Andrew J. Kelly SQL MVP
>
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:341680AD-9EF2-4574-9BF6-CE4E391DF2D8@.microsoft.com...
> > Hi all,
> >
> > Currently, I have a maintenance plan and it creates a complete backup to
> > the
> > default backup directory on the local machine.
> >
> > What should I do if I also want to back up the database to another
> > machine/share drive on the same domain? Do I need to create a new
> > maintenance plan just for that? Could this create unnecessary performance
> > hit on the SQL Server?
> >
> > Or could I go directly to the SQL job and modify the script so that it can
> > back up to multiple locations? I don't know how to do this or whether it
> > will work at all.
> >
> > Sure I can create a NT backup to copy the database to another location.
> > But
> > I really like the "Remove file older than..." feature on the maintenance
> > plan. Our database is pretty big and the currently backup mechanism faces
> > space issue without this "Remove file older than..." feature.
> >
> > Any advice? And thanks in advance.
> >
> > Abel Chan
> >
>
>