August 9, 2005 at 8:25 am
Is the default database a login option or a server option. I know how to change it for a user, but can you change it for the server?
August 9, 2005 at 9:27 am
its per user, When you create a user without specifiying it gives it master
sp_addlogin [ @loginame = ] 'login'
[ , [ @passwd = ] 'password' ]
[ , [ @defdb = ] 'database' ]
[ , [ @deflanguage = ] 'language' ]
[ , [ @sid = ] sid ]
[ , [ @encryptopt = ] 'encryption_option' ]
[@loginame =] 'login'
Is the name of the login. login is sysname, with no default.
[@passwd =] 'password'
Is the login password. password is sysname, with a default of NULL. After sp_addlogin has been executed, the password is encrypted and stored in the system tables.
[@defdb =] 'database'
Is the default database of the login (the database the login is connected to after logging in). database is sysname, with a default of master.
[@deflanguage =] 'language'
Is the default language assigned when a user logs on to SQL Server. language is sysname, with a default of NULL. If language is not specified, language is set to the server's current default language (defined by the sp_configure configuration variable default language). Changing the server's default language does not change the default language for existing logins. language remains the same as the default language used when the login was added.
[@sid =] sid
Is the security identification number (SID). sid is varbinary(16), with a default of NULL. If sid is NULL, the system generates a SID for the new login. Despite the use of a varbinary data type, values other than NULL must be exactly 16 bytes in length, and must not already exist. SID is useful, for example, when you are scripting or moving SQL Server logins from one server to another and you want the logins to have the same SID between servers.
[@encryptopt =] 'encryption_option'
Specifies whether the password is encrypted when stored in the system tables. encryption_option is varchar(20), and can be one of these values.
August 10, 2005 at 10:11 am
I had the same problem before,
What you can do is create a new database with the new collation, script out the old DB and apply it to the new database making sure that all the cols have the correct collation setting on them. When this is done you can use copy over the data to the new database.
Regards,
m
August 10, 2005 at 1:56 pm
You can use sp_defaultdb to set the default database for a user.
Syntax: sp_defaultdb 'login_name','database'
You should be able to create a cursor with
SELECT * FROM master..sysxlogins
and then loop through the names and set the default database to whatever you wish using sp_defaultdb.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply