Default database for logins

Question

Post reply

Default database for logins

sqlfriend

SSC Guru

Points: 52781
More actions
December 16, 2010 at 12:47 pm

#228494

I move sql server 2005 to another new server. I know there is a way to generate login scripts and run it to on new servers to recreate the logins.
But I also read the script will not include the default databases to those logins.
So is there a way or sql to generate the default db of the logins on the old server, so that I can do either manually or script to set the default db on the new server?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

The sp_help_revlogin script for SQL2005 does include the default database for the logins.

but anyway this would script them out for you as well (SQL2000 syntax but will work)

set quoted_identifier off

select 'exec sp_defaultdb ',+"'" +loginname +"'," +dbname from syslogins

---------------------------------------------------------------------

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks, what does set quoted_identifier off mean?

Is this just a setting for the sql statement or is it turn off for all future sql statements.

Do I need to turn it back on?

Thanks

The Grateful DBA SSC Veteran Points: 263 More actions · Answer 3

'SET QUOTED_IDENTIFIER OFF' means identifiers can not be designated with double-quotes and must follow all appropriate identifier rules. You'll see this statement most of the time when an INSERT/UPDATE is run on a text-type column and the field has the potential to contain a single-quote (a contraction for instance).

Hope this helps...Troy