Re: SQL Server 2005 login with Windows Authentication

Hi Erland,

I just want to thank you very much for your precious information.
BULTIN\Administrators being synonym of D620\Administrators was the key point
i did not know. Everything makes much more sense now!

Also your helper SP and functions are really of great value!
Thanks again!

Cheers,
Francois Malgreve.


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns994FF1DCE7D8CYazorman@xxxxxxxxxxxx
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: Slow booting xp home.
    ... Changing the boot order to boot first from your hard disk might save you a half second, but you won't be able to boot from a CD until you change it back - and the time spent to do that will erase any previous time saved. ... 2- Consider what software you really want to start with Windows and also how you've configured your applications at startup ... Installing and Registering Visual Studio Express Editions Smart Device ...
    (microsoft.public.windowsxp.perform_maintain)
  • 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: Windows NT user or group not found.
    ... out what SQL Server thinks the login name is vs. what Windows thinks the ... When the SQL Server collation and the Windows collation are different, ... The following example uses the Param2 parameter to get the SID from Windows ...
    (microsoft.public.sqlserver.connect)
  • 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)