SQL 2005 - SQLDMO - Adding Logins/ Users

[Will]

I do not understand the sequence in which to add logins/users. A couple
of things to note. I can not use vbscript - it has to be done using the
sqldmo objects (or in a sql script).

The sequence I use now is:

1- create login, set default db
2- add login to sql server
3- set db owner
4- create user
5- add user to master db
6- add user to tempdb - blows up here
Error 'Microsoft SQL-DMO: [SQL-DMO]This object is already in a
collection.'

I need the user in 3 dbs. Any help greatly appreciated. Thinking about
it...I bet by adding it to the master db that it is automatically added
to tempdb so I will try to add it to the 2rd db and see if that works.

Thanks!
-Will

[Erland Sommarskog]

Will (wm****@msn.com) writes:

I do not understand the sequence in which to add logins/users. A couple
of things to note. I can not use vbscript - it has to be done using the
sqldmo objects (or in a sql script).

The sequence I use now is:

1- create login, set default db
2- add login to sql server
3- set db owner
4- create user
5- add user to master db
6- add user to tempdb - blows up here
Error 'Microsoft SQL-DMO: [SQL-DMO]This object is already in a
collection.'

I need the user in 3 dbs. Any help greatly appreciated. Thinking about
it...I bet by adding it to the master db that it is automatically added
to tempdb so I will try to add it to the 2rd db and see if that works.

Permit me to be curious why you need to add the user to master and tempdb?
There is rarely any reason to do this, least of all in master.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

I don't know about the tempdb thing, but anytime I want to know how to
do something with script I'll just load up profiler and do it through
the GUI, then pick through the script and clean it up a bit.

Erland Sommarskog wrote:

Will (wm****@msn.com) writes:

I do not understand the sequence in which to add logins/users. A couple
of things to note. I can not use vbscript - it has to be done using the
sqldmo objects (or in a sql script).

The sequence I use now is:

1- create login, set default db
2- add login to sql server
3- set db owner
4- create user
5- add user to master db
6- add user to tempdb - blows up here
Error 'Microsoft SQL-DMO: [SQL-DMO]This object is already in a
collection.'

I need the user in 3 dbs. Any help greatly appreciated. Thinking about
it...I bet by adding it to the master db that it is automatically added
to tempdb so I will try to add it to the 2rd db and see if that works.

Permit me to be curious why you need to add the user to master and tempdb?
There is rarely any reason to do this, least of all in master.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx