There is a nuance that is never really explained, but has bit me several times. When you create a user, you can do it at the database level or at the server level. If you create it at the database level you'll get errors. You need to create it at the server level (meaning the Security node for the server in SSMS), then add that person to the database. If the login already exists at the database level, you'll need to delete it first. I don't know why you can create a login at the database level because it's never worked for me.


>Hi,
>
>I am testing access to a new customer database (not the first time a very similar environment). What happens is when I use Windows authentication (specify in the connection string IntegratedSecurity True) connection works. But when I try SQL Server authentication I get connection error (basically connection denied). For SQL Server authentication I created login and then in the database I created user of the same name. The user has roles: db_datareader, db_datawriter, db_owner. But nothing works. I have double checked the password (I made it very simple 'test') but can't make the SQL Server authentication to work.
>What could be missing? TIA.