stored proc to add a user account?

[smsinger3]

I am writing a stored proc to
1) Create a new user
2) Give that user access to several databases and
3) Setup a user role in all the databases.

It seems to work fine, however, I get the following error when I click the Database Access tab for the user in Enterprise Manager:

The name was not found in the users collection. If the name is a qualified name, then use []to separate various parts of the name, and try again

Here is part of my code in the stored procedure:

--Setup a new login for the user if it does not exist
if not exists (select * from master..syslogins where name = @login)
BEGIN
exec sp_addlogin @login, @pwrd
END

--Change the default database for the user
exec sp_defaultdb @login, 'members'

--Grant DB access to the user
exec sp_grantdbaccess @login, @pwd OUT

--Add the role
exec sp_addrolemember @role, @login


Does anyone have any ideas why I get that error in EM? Your help is very much appreciated!

Thanks,

Steve S
sms@hmbnet.com

 

[tlbroadbent]

EM is using cached information. You need to refresh the information. Right-click the database, select Refresh. Sometimes, I have to disconnect from the server and reconnect because Refresh doesn't work. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[smsinger3]

Thanks, tlbroadbent! I was refreshing it all along, however I did not actually CLOSE the Enterprise Manager! That was extremely frustrating.

Thanks for your help,

Steve

 

[tlbroadbent]

It isn'r necessary to close EM. Just right click the server in EM and select Disconnect. Then open the server again. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.