Link to home
Start Free TrialLog in
Avatar of bejhan
bejhan

asked on

Creating an MS SQL Server user for MS Access connectivity

I created a user in MS SQL Server 2008 Express via:
sp_addlogin 'bejhan', 'password', 'nsf'
sp_addrolemember N'db_datareader', N'bejhan'
p_addrolemember N'db_datawriter', N'bejhan'

Open in new window


I then set the connection string of my table definitions to:
ODBC;DRIVER=SQL Server;SERVER=BEJHAN-LAPTOP\SQLEXPRESS;DATABASE=nsf;UID=bejhan;PWD=password

Open in new window


However, when I try to refresh the table links I get the following error:
Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'bejhan'.

Open in new window


What am I missing?
ASKER CERTIFIED SOLUTION
Avatar of sameer_goyal
sameer_goyal
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bejhan

ASKER

I forgot include that after I executed sp_addlogin, I used sp_adduser:

sp_addlogin 'bejhan', 'password', 'nsf'
sp_adduser 'bejhan, 'bejhan'
sp_addrolemember N'db_datareader', N'bejhan'
p_addrolemember N'db_datawriter', N'bejhan'

Open in new window

But based on Sameer's comment it seems I missed setting the sysadmin role.

I'll try that later today.

Jamie, I am not actually using "password" as the password, I've just replaced the real password for posting.
I think you need to use Sameer's code - create Login, not sp_addlogin as:

"This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CREATE LOGIN instead."

And specify the check_policy as off (defaults to on) with create login

CHECK_POLICY = { ON | OFF }

    Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

    If the Windows policy requires strong passwords, passwords must contain at least three of the following four characteristics:

        An uppercase character (A-Z).

        A lowercase character (a-z).

        A digit (0-9).

        One of the non-alphanumeric characters, such as a space, _, @, *, ^, %, !, $, #, or &.
Avatar of bejhan

ASKER

Sorry guys, lost all of my time to another project. I'll give your suggestions a try in a few days.