code to add a user

[cpitc]

Hi

I am running SQL Express (SSMS) under the server name Colin\SQLExpress

If I use this script I cannot log into the SSMS using the SQL login created.

CREATE LOGIN [colinp] WITH PASSWORD=N'leeds'
, CHECK_EXPIRATION=ON, CHECK_POLICY=ON,
DEFAULT_DATABASE=[Adventureworks], DEFAULT_LANGUAGE=[English];

If I change the code to Default_Database=[Master] it lets me log in but then I have to allow permission to the Adventureworks database manually when I log in.

What code can I use that will give me login and also gove me rights to the Adventureworks database also.

Thanks

 

[kss444]

After you create your user you have to grant them permissions to the database.

Use Adventurworks
EXEC sp_grantdbaccess 'RHOME\Bob'

Ordinary Programmer

 

[cpitc]

Thanks for the reply

I have entered this code

CREATE LOGIN [CP] WITH PASSWORD=N'leeds',
CHECK_EXPIRATION=ON, CHECK_POLICY=ON,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[English];


Use Adventurworks
EXEC sp_grantdbaccess 'CP'

When I run the EXEC I ma getting this error message

Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'Adventurworks'. No entry found with that name. Make sure that the name is entered correctly.

I have refreshed the logins and the name is there, any ideas? Thanks

 

[gmmastros]

Adventur[!]e[/!]Works is not spelled correctly.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cpitc]

Of course, thanks it works well now.