Re: SQL Server 2005 login with Windows Authentication

Francois Malgreve (francois.malgreve@xxxxxxxxxxxxxxxxxxx) writes:
My Computer name is D620 and my windows user name is Francois (who has
admin rights, beign part of the Administrator group). As it's a stand
alone computer at home, I don't have any domain so it's a local user
(not a domain user).

I obviously installed SQL Server using my Francois (admin) local user
account. So having stated that I chose windows authentication as
authentication mode i was expecting to see something like D620\Francois in
the available logins of my SQL Server instance.

You need to do

CREATE LOGIN D620\Francois FROM Windows

to do this. But you can still access SQL Server as D620\Francois without
this command - run "SELECT SYSTEM_USER" in a query window to see who you
are. And the reason for this is that a Windows login also can have access
through a Windows group. And indeed:

Instead, I see:
- BULTIN\Administrators (What is it? And why is it not
D620\Administrators,

This is how you get in. I don't know why it's called BUILTIN\Administrators,
rather than <machine>\Administrators. But maybe this so they easier can
write documentation around it. Or that the name is hardwired somewhere.

Anyway, BUILTIN\Administrator gives sysadmin access to SQL Server to
all Windows uses with admin access to Windows. There are situations where
you prefer to drop this entry. For instance you are the DBA, but you don't
want the network admin to fiddle inside SQL Server. (Before you do this,
be sure that have created another login with sysadmin rights!)

- NT AUTHORITY\SYSTEM (who is that fellow?)

Mumble, also some built-in Windows entity. LocalSystem, I think.


How can I know if D620\Francois got admin rights

select is_srvrolemember('sysadmin')

When you are logged in as D620\Francois

and if he is dbo of all db?

sp_helpdb




--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: login 101..
    ... On Windows 2003, SQL Server 2005 can enforce the Windows password complexity ... Windows authentication - SQL Server uses a special protocol to ask ... user is in the list of allowed logins, ...
    (microsoft.public.sqlserver.security)
  • RE: How to create a trusted connection
    ... You need to grant access for the Windows login by referring to the books ... is set to use Windows authentication to be able to do trusted connection. ... There are two modes of authentication in SQL Server: ...
    (microsoft.public.sqlserver.security)
  • RE: How to create a trusted connection
    ... What do you mean by "creating a Windows login"? ... To login SQL Server 2000 using trusted connection, grant the Windows ... Group" in SQL Server Books Online to get the concept, ...
    (microsoft.public.sqlserver.security)
  • Re: Connecting to SQL from a Windows Service
    ... There is probably some way to debug this, but that's deeper Windows knowledge than I can dredge up right now. ... I looked into the Event Log and found that when my service ... 1st Login succeeds. ... You can try turning on login auditing in SQL Server and then check the ...
    (microsoft.public.sqlserver.connect)
  • Re: Domain Admin login failure.
    ... specific login in SQL Server. ... Then it won't need it's admin privileges. ... Rick Byham, SQL Server Books Online ... administrator, it will let my domain admin account login. ...
    (microsoft.public.sqlserver.security)