Does somebody understand what they mean here. Some of it is about Integrated security

[Tony Johansson]

Hello!

I received this from a person in a news group. I have replied to the news
group to get some information about what this actually means but haven't
received any kind of respons so I hope I might get some from this group
because it's about Sql server.

When you use integrated security, the app essentially logs you in. If
you want to test this, make another account (after you have the website
in IIS) and make sure that account does not have access to SQL Server.
You will see that it fails. Most likely the browser will pop up so you
can "log in", but I am not sure of that (have to think about it). If it
does, you can log in as Tony (you state this is your account) and gain
access.

What I know is that every windows account have automatically access to Sql
Server when using Integrated security.
So I can't understand what they mean with If
you want to test this, make another account (after you have the website and
make sure that account does not have access to SQL Server. You will see that
it fails.

//Tony

[Bob Barrows]

Tony Johansson wrote:
> Hello!
>
> I received this from a person in a news group. I have replied to the
> news group to get some information about what this actually means but
> haven't received any kind of respons so I hope I might get some from
> this group because it's about Sql server.
>
> When you use integrated security, the app essentially logs you in. If
> you want to test this, make another account (after you have the
> website
> in IIS) and make sure that account does not have access to SQL Server.
> You will see that it fails. Most likely the browser will pop up so you
> can "log in", but I am not sure of that (have to think about it). If
> it does, you can log in as Tony (you state this is your account) and
> gain access.
>
> What I know is that every windows account have automatically access
> to Sql Server when using Integrated security.

That's not true. Logins have to be created in SQL Server even for
Windows accounts.

--
HTH,
Bob Barrows

[Tony Johansson]

Assume I use Integrated Security. I have two windows accounts one account
Tony that I use when logging in to the machine and the other is ASPNET that
IIS is using.

How can I see in Sql Server what access rights these two accounts have.
None of these two account exist in Sql Server.

//Tony

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> skrev i meddelandet
news:OXh8FAxg...@TK2MSFTNGP02.phx.gbl...

[Bob Barrows]

Just to further clarify, in addition to granting rights to individual
Windows accounts, you can also grant rights to AD groups. So, if that
has been done, and a Windows account has been added to that AD group, it
might appear that no SQL authorization had to be granted to that Windows
account, when in actuality it had been.
--
HTH,
Bob Barrows

[Bob Barrows]

Using SSMS, it is simply a matter of expanding the Security node for
your server.
Without SSMS, you can use the system stored procedure sp_helplogins.

This information is all in SQL Books Online (BOL) which, if you do not
already have it installed on your machine, you can read online at
msdn.microsoft.com. It's also available as a download from MS (a simple
search should find it for you).

PS. Your application may or may not be using the ASPNET account to log
into SQL Server, depending on how you have your website set up. If you
have Anonymous unchecked, it will use the Tony account (unless you are
using Impersonation), otherwise, it will use the ASPNET account, which
of course will need to be a domain account if the SQL Server is on a
different machine.

--
HTH,
Bob Barrows

[Bob Barrows]

Using SSMS, it is simply a matter of expanding the Security node for
your server.
Without SSMS, you can use the system stored procedure sp_helplogins.

This information is all in SQL Books Online (BOL) which, if you do not
already have it installed on your machine, you can read online at
msdn.microsoft.com. It's also available as a download from MS (a simple
search should find it for you).

PS. Your application may or may not be using the ASPNET account to log
into SQL Server, depending on how you have your website set up. If you
have Anonymous unchecked, it will use the Tony account (unless you are
using Impersonation), otherwise, it will use the ASPNET account, which

of course will need to have domain rights if the SQL Server is on a
different machine.

--
HTH,
Bob Barrows

[Tony Johansson]

Assume I use Integrated Security. I have two windows accounts one account
Tony that I use when logging in to the machine and the other is ASPNET that
IIS is using.

How can I see in Sql Server what access rights these two accounts have ?

None of these two account exist in Sql Server.

In Sql Server under the Security->Logins I have these accounts defined
Builtin\Administrator
Builtin\users
HOMEPC\SQLServer2005MSSQLUser$HEMPC$SQLEXPRESS
NT INSTANS\SYSTEM
sa

So one more question does anyone know if these account has anything with
windows account to do ?

//Tony

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> skrev i meddelandet

news:eiVHCNxg...@TK2MSFTNGP02.phx.gbl...

[Erland Sommarskog]

Tony Johansson (johansson...@telia.com) writes:
> In Sql Server under the Security->Logins I have these accounts defined
> Builtin\Administrator
> Builtin\users
> HOMEPC\SQLServer2005MSSQLUser$HEMPC$SQLEXPRESS
> NT INSTANS\SYSTEM
> sa
>
> So one more question does anyone know if these account has anything with
> windows account to do ?

If you at some point granted access to BUILTIN\Users, this means that you
granted access to all users on the machine. Thus, if you create a new
account on the machine, this account have access to SQL Server thanks to
BUILTIN\Users.

If you say

DROP LOGIN [BUILTIN\Users]

this new account will not have access any more. In fact, no account that
is not an adminstrator will not have access, so you should first grant
access to ASPNET and other users that you want to be table to access
SQL Server.

--
Erland Sommarskog, SQL Server MVP, esq...@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

[Dan Guzman]

> Assume I use Integrated Security. I have two windows accounts one account
> Tony that I use when logging in to the machine and the other is ASPNET
> that
> IIS is using.
>
> How can I see in Sql Server what access rights these two accounts have.
> None of these two account exist in Sql Server.

Be aware that there are 2 levels of authentication and authorization that
occur with an IIS database application: IIS and SQL Server. IIS first
confirms the end user identity based on the IIS site configuration. The
user can use the application only if they are authorized or anonymous access
is enabled. Once authorized, an application using Integrated Security to
connect to SQL Server typically connects to SQL Server using the Windows
domain account you specify as the IIS anonymous or application pool security
context. The credentials of the end user are not used to connect to the
database server in this scenario. You'll need to add a SQL Server login for
the IIS Windows account (or group) as well as the corresponding database
user and object permissions.

There are ways to configure IIS to use the end user credentials, but this is
usually done only for intranet applications. See
http://technet.microsoft.com/en-us/library/cc733010(WS.10).aspx for more
information.

--
Hope this helps.

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