SQL Express 2008: Select User Name to Login

On Thu, 02 Sep 2010 00:21:18 +0200, Erland Sommarskog
wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>> I am working through the tutorials. I have created user Mary.
>> The next bit is about how she can not access anything yet.
>>
>> Oh, really?
>>
>> I want to see what happens if she does.
>>
>> I try to login under Mary and can not. The User name field is
>> disabled. The docs says that it is only available when logging in
>> using Window Authentication, but that is exactly when it is not
>> available.
>>
>> The Connect to Server form comes up with:
>> Server type: Database Engine
>> Server name: Loop\SQLEXPRESS
>> Authentication: Windows Authentication
>> User name: LOOP\Gene
>> Password: <blank>
>> Both User name and Password are disabled. LOOP\Gene is the admin
>> account.
>>
>> What do I need to correct, please?
>
>You need to select SQL Server authentication. When you use Windows
>authentication, you always log with the user with which you logged
>into Windows.
>
>SQL Server authentication is not enabled by default, but you can enable
>it by right-clicking the server in Object Explorer, and it should be
^
Insert
selecting Properties,
(There is a Security leaf? as well which caused me a bit of
confusion.)

>on the Security tab. You need to restart SQL Server for the setting
>to take effect.

OK, did that, but...

I do not know what to use for a user name. I have tried a number
of things (with and without computer name) and nothing works under SQL
Server Authentication. I get
Login failed for user '<what-I-tried>'. (Microsoft SQL
Server, Error: 18456)

Windows Authentication still works fine. There is the
complication that I changed the name of my computer at one point.
There is at least one entry with the old name. I have had no problem
running under Loop\Gene.

I do not see user Mary in the list of logins. Should I? Where
is it stored?

I am tempted to just huck it and reinstall, but I was hoping to
be a bit less ignorant before doing that.

Sincerely,

Gene Wirchenko
 >> Stay informed about: SQL Express 2008: Select User Name to Login 

Gene Wirchenko (genew@ocis.net) writes:
> I do not see user Mary in the list of logins. Should I? Where
> is it stored?

If you never created a login with name, you can of course not log in
as it.

You create logins with the CREATE LOGIN command.


--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: SQL Express 2008: Select User Name to Login 

On Thu, 02 Sep 2010 23:59:54 +0200, Erland Sommarskog
wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>> I do not see user Mary in the list of logins. Should I? Where
>> is it stored?
>
>If you never created a login with name, you can of course not log in
>as it.
>
>You create logins with the CREATE LOGIN command.

But I did create a login. I found the login elsewhere. It was a
matter of where the needle goes in the haystack.

I ended up uninstalling, and then SQL Express 2008 would not
reinstall. I have a mess here now, but maybe Monday.

Sincerely,

Gene Wirchenko
 >> Stay informed about: SQL Express 2008: Select User Name to Login 

Gene Wirchenko (genew@ocis.net) writes:
> But I did create a login. I found the login elsewhere. It was a
> matter of where the needle goes in the haystack.

SELECT * FROM sys.server_principals lists all your login, groups etc.



--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: SQL Express 2008: Select User Name to Login 

On Fri, 03 Sep 2010 22:31:36 +0200, Erland Sommarskog
wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>> But I did create a login. I found the login elsewhere. It was a
>> matter of where the needle goes in the haystack.
>
>SELECT * FROM sys.server_principals lists all your login, groups etc.

I am using SSMS and do not know the system tables yet, so I went
by the SSMS displays.

Sincerely,

Gene Wirchenko
 >> Stay informed about: SQL Express 2008: Select User Name to Login