Users cannot logon to applcation using Windows Authentication unless their account is inside the SQL Server local admin group

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Spin - 09 Mar 2008 21:40 GMT

Gurus,

Running SQL server 2005 SP2 configured for Windows Authentication on a
two-node cluster running Windows Server 2003 SP2 inside a single Active
Directory domain. All users logon to the domain from their Windows XP Pro
workstations. On the SQL Server is an application to which some users must
access. When they bring up the logon screen for this application, there is
a radio button for SQL Authentication and another Windows Authentication.
The users cannot logon using Windows Authentication unless their domain
account is a member of the local administrators group on the SQL Server.
Note: Even if I give their domain accounts 'sysadmin' role on the DB
server, they still cannot login into the application using Windows
Authentication until they given local administrator privileges on the DB
server.

Signature

Spin

Reply to this Message

Dan Guzman - 09 Mar 2008 21:59 GMT

> The users cannot logon using Windows Authentication unless their domain
> account is a member of the local administrators group on the SQL Server.
> Note: Even if I give their domain accounts 'sysadmin' role on the DB
> server, they still cannot login into the application using Windows
> Authentication until they given local administrator privileges on the DB
> server.

It seems the user accounts either do not have individual logins or are not
members of a Windows group that has been granted SQL Server access. Try
listing the account and the database(s) the users have been granted access
to:

EXEC sp_helplogins 'MyDomain\MyAccount'

If the account is not listed as expected, try adding the login again and
creating the database user:

USE MyDatabase
CREATE LOGIN [MyDomain\MyAccount] FROM WINDOWS;
CREATE USER [MyDomain\MyAccount];
--and add to database role(s) or grant permissions

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Gurus,
>
[quoted text clipped - 10 lines]
> Authentication until they given local administrator privileges on the DB
> server.

Reply to this Message