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