Re: Error 18456: Login failed for user 'DOMAIN\user'

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 09/13/04 

Date: Sun, 12 Sep 2004 22:01:44 -0600

Via Group Membership for a login has to do with how that
login was added. One scenario is that a windows account can
be initially added directly to a role (vs. using
sp_grantlogin then sp_addsrvrolemember).
To duplicate the via group membership:
1. Create a windows account
2. In Query Analyzer, add that account to one of the server
roles such as processadmin using sp_addsrvrolemember - e.g.
sp_addsrvrolemember 'Domain\WindowsAccount', 'processadmin'

-Sue

On Fri, 10 Sep 2004 15:52:08 -0700, "===steve pdx==="
<lins@nospam_portptld.com> wrote:

>I'm a new DBA for my company. after I took over the existing servers and
>started auditing all accouts. i noticed that some nt accouts has server
>access "Via group membership". i don't know how they were created that way
>because i have never been able to duplicate the result using EM to add a NT
>account. (it's always 'permit'). Does anyone know how to duplicate that "Via
>group membership".
>thru sql2k EM? We use sql2k on nt5 box.
>
>thank you.
>
>
>
>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>news:9vv3k0hfuj5r7uapph2l33mfl3aq1hov1r@4ax.com...
>> I'm not sure Chris. I've run 2005 on a stand alone box and I never
>> noticed anything like that. You could try the beta newsgroup - you can
>> get more information on it at:
>> http://www.aspfaq.com/sql2005/show.asp?id=1
>>
>> -Sue
>>
>> On Thu, 9 Sep 2004 11:49:20 +0200, "Christoffer"
>> <christoffer@nospam.com> wrote:
>>
>> >Hi,
>> >
>> >When I use the Microsoft SQL Server Management Studio, which is in SQL
>> >Server 2005 and is equivalent to Enterprise Manager for SQL Server 2000,
>to
>> >add a Windows login, the login's Server access property is set to "Via
>group
>> >membership".
>> >
>> >If I use the stored procedures sp_grantlogin on SQL Server 2005, the
>login's
>> >Server access property is set to "Permit".
>> >If I use the old Enterprise Manager for SQL Server 2000, the login's
>Server
>> >access property is set to "Permit".
>> >
>> >Why does the SQL Server Management Studio (2005) set the Server access
>> >property to "Via group membership" instead of "Permit". I know this is a
>> >beta and an answer might not be possible to give...
>> >
>> >Thanks for helping me out, I no longer experience the 18456 error, this
>is
>> >just out of curiousity :)
>> >/Chris
>> >
>> >
>> >"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>> >news:iq7uj0tv8g78vlco9b6dr4m36uq1b3n1tc@4ax.com...
>> >> Chris,
>> >> I'm not real clear on when you are receiving this error. It doesn't
>> >> look like something you would get when you actually try to add the
>> >> login. Try add the account using Query Analyzer instead and executing
>> >> sp_grantlogin. The help file has more information on sp_grantlogin.
>> >> Whether you set up another group or not depends upon how you are going
>> >> to design your security model. Using Windows groups certainly can be
>> >> easier but whether that works for you or not depends on how your
>> >> windows groups are set up, how well they reflect the access, security
>> >> needs of the applications that will use the databases on the server,
>> >> etc.
>> >>
>> >> -Sue
>> >>
>> >> On Wed, 8 Sep 2004 14:21:49 +0200, "Christoffer"
>> >> <christoffer@nospam.com> wrote:
>> >>
>> >>>I see, the thing is, there is no added Windows groups login in the SQL
>> >>>Server. I tried adding DOMAIN\Administrators but I got the errore
>message:
>> >>>
>> >>>"The login 'DOMAIN\Administrators' does not exist. (Microsoft SQL
>Server,
>> >>>Error: 15007)"
>> >>>
>> >>>But I'm sure it exists since I browsed to it using the Active
>Directory.
>> >>>Is
>> >>>there some limitation to adding the DOMAIN\Administrators group? Should
>I
>> >>>try to set up yet another group for database access?
>> >>>
>> >>>Thanks for your help!
>> >>>/Chris
>> >>>
>> >>>
>> >>>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>> >>>news:8pttj05a5jihmdoqe1ekg4iufo7er08j5f@4ax.com...
>> >>>> Via group membership means the login was has access to SQL
>> >>>> Server through their membership in a windows group.
>> >>>> Managing logins through windows group can definitely make
>> >>>> the management of logins much easier.
>> >>>>
>> >>>> -Sue
>> >>>>
>> >>>> On Wed, 8 Sep 2004 10:31:06 +0200, "Christoffer"
>> >>>> <christoffer@nospam.com> wrote:
>> >>>>
>> >>>>>Hello,
>> >>>>>
>> >>>>>I think I have found the problem. When I add my login (DOMAIN\USER)
>> >>>>>using
>> >>>>>the Microsoft SQL Server Management Studio (2005 Beta 2), the
>property
>> >>>>>"Server Access" is set to "Via Group Membership". If I change this to
>> >>>>>"Permit" I can login!
>> >>>>>
>> >>>>>My question is: What is "Via Group Membership" and is this the best
>> >>>>>practice
>> >>>>>when adding Windows logins?
>> >>>>>
>> >>>>>Thanks,
>> >>>>>Chris
>> >>>>>
>> >>>>>
>> >>>>>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>> >>>>>news:90psj0p1dethn063mh6sd61u9fi93c1ec9@4ax.com...
>> >>>>>> Are you a member of a group that has been denied access to
>> >>>>>> the server? Did you try another Windows account and test
>> >>>>>> with that?
>> >>>>>>
>> >>>>>> -Sue
>> >>>>>>
>> >>>>>> On Tue, 7 Sep 2004 14:53:32 +0200, "Christoffer"
>> >>>>>> <christoffer@nospam.com> wrote:
>> >>>>>>
>> >>>>>>>Hello
>> >>>>>>>
>> >>>>>>>I deleted and re-added the account and I also gave it access to
>every
>> >>>>>>>database with db_owner on every database. I also made the account a
>> >>>>>>>member
>> >>>>>>>of the sysadmin role. Still no luck :(
>> >>>>>>>
>> >>>>>>>The account is in a Active Directory, could this be the problem?
>> >>>>>>>
>> >>>>>>>Cheers,
>> >>>>>>>Chris
>> >>>>>>>
>> >>>>>>>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>> >>>>>>>news:9rlpj0982j03evgpta2vla52c2jrtlg9md@4ax.com...
>> >>>>>>>> Error 18456 is "login failed for user xxx"...the error you
>> >>>>>>>> are getting. You have something incorrect with adding the
>> >>>>>>>> windows account to the logins or the permissions you have
>> >>>>>>>> set for the login. Try deleting the login and then try
>> >>>>>>>> adding it again. Make sure the login has access to whatever
>> >>>>>>>> database you have set up as the default database.
>> >>>>>>>>
>> >>>>>>>> -Sue
>> >>>>>>>>
>> >>>>>>>> On Tue, 31 Aug 2004 11:34:56 +0200, "Christoffer"
>> >>>>>>>> <christoffer@nospam.com> wrote:
>> >>>>>>>>
>> >>>>>>>>>Hi,
>> >>>>>>>>>
>> >>>>>>>>>I've just installed SQL Server 2005 Beta 2 and added Active
>> >>>>>>>>>Directory
>> >>>>>>>>>account to our SQL Server (under Database/Security/Logins).
>However,
>> >>>>>>>>>when
>> >>>>>>>>>I
>> >>>>>>>>>try to login using the Windows Authentication, I get this error
>> >>>>>>>>>message:
>> >>>>>>>>>
>> >>>>>>>>>Login failed for user 'DOMAIN\user'. (Microsoft SQL Server,
>Error:
>> >>>>>>>>>18456)
>> >>>>>>>>>
>> >>>>>>>>>where DOMAIN\user is really my domain-account, which I rather not
>> >>>>>>>>>publish.
>> >>>>>>>>>When I try to login using 'sa' and SQL Server Authentication
>there
>> >>>>>>>>>is
>> >>>>>>>>>no
>> >>>>>>>>>problem.
>> >>>>>>>>>
>> >>>>>>>>>I've tried to find out what error 18456 is but no luck. Could
>anyone
>> >>>>>>>>>help
>> >>>>>>>>>me
>> >>>>>>>>>please?
>> >>>>>>>>>
>> >>>>>>>>>Cheers,
>> >>>>>>>>>Chris
>> >>>>>>>>>
>> >>>>>>>>
>> >>>>>>>
>> >>>>>>
>> >>>>>
>> >>>>
>> >>>
>> >>
>> >
>>
>

Relevant Pages

  • Re: Server Registration fails - figure this out !
    ... looking at the registry setting doesn't mean it is actually ... Query Analyzer or osql - with sa or login as the domain user ... >> Make sure the windows account being used has a login, ... >> any other group membership. ...
    (microsoft.public.sqlserver.security)
  • Re: What is "via group membership"
    ... fixed server role, the login will be listed with Server ... I did some testing just and it did not show up Via group membership ... having explicitly added them as a login first. ...
    (microsoft.public.sqlserver.security)
  • Re: VPN users have full access
    ... use a prog that will show the group membership for ... Some use the SID and others use login name... ... use an ACL dump prog to check the ACL's on the ... > network by VPN. ...
    (microsoft.public.win2000.security)
  • RE: Login Failed Reason: Token-based access failed
    ... I've noticed in SQL Server 2008 when a Windows account doesn't have a Login ... validation failed with an infrastructure error. ...
    (microsoft.public.sqlserver.server)
  • Re: Update group membership without re-login
    ... > of the login shell. ... be nonportable and introduce race conditions. ... But usually it is not any major problem to me. ... to take advantage of my new group membership without having ...
    (comp.os.linux.security)