About access to Sql Server

[Tony Johansson]

Hello!

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.

Now to my question will these two windows account(Tony and ASPNET) have the
same access rights to Sql Server ?

I mean that every windows account that you use with Integrated Secirity have
full right to Sql Server.

If not how can you control these accounts(Tony and ASPNET) in Sql Server
when none of these two account exist in sql server.

//Tony

[Mary Chipman [MSFT]]

Security is complicated topic, so there isn't a one-size-fits-all
answer to your question. Here's a couple of resources to help get you
started:

Patterns & Practices ASP.NET security guidance
http://msdn.microsoft.com/en-us/library/ms954801.aspx

The ADO.NET Security documentation
http://msdn.microsoft.com/en-us/library/bb669074.aspx has links to
related topics in SQL BOL which will help with the details on the
server side. Sometimes it's hard ferreting out what you need, this
ties it together for you.

--Mary

[Erland Sommarskog]

There are lots of details missing about your setup. But if both Tony and
ASPNET are members of the same Windows group, and this group have been
granted access to SQL Server, they will have the same rights. Not
necessarily full rights though!

--
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

[Tony Johansson]

Background information.
I use windows XP pro and sql server express 2005 everything is located on
the same machine.
For me it seems that when I use Windows authentication every user account on
XP have access to Sql Server and can start
Sql Server management Studio express. I can also list data from the Customer
table in the Northwind database using a SqlDataSource and a DataGrid.
I just tested to create a new user with name Test and added this user to the
Guest group and this worked perfect.

I want to test this.
Create a user account that use Windows authentication that doesn't have
access to Sql Server.
Can somebody help me to guide me how I can do this ?

//Tony

"Erland Sommarskog" <esq...@sommarskog.se> skrev i meddelandet
news:Xns9CE9F38BE...@127.0.0.1...

[Erland Sommarskog]

Tony Johansson (johansson...@telia.com) writes:
> Background information.
> I use windows XP pro and sql server express 2005 everything is located
> on the same machine. For me it seems that when I use Windows
> authentication every user account on XP have access to Sql Server and
> can start Sql Server management Studio express.

Well, starting SSMSE is not strange - that's just another applicaiton.

> I can also list data from the Customer table in the Northwind database
> using a SqlDataSource and a DataGrid. I just tested to create a new user
> with name Test and added this user to the Guest group and this worked
> perfect.
>
> I want to test this.
> Create a user account that use Windows authentication that doesn't have
> access to Sql Server.
> Can somebody help me to guide me how I can do this ?

Could you run this query and post the results:

SELECT name FROM sys.server_principals WHERE type IN ('U', 'G')

[Tony Johansson]

The result I get from this query SELECT name FROM sys.server_principals

WHERE type IN ('U', 'G')

is the following.
BUILTIN\Administartors
BUILTIN\Users

//Tony

"Erland Sommarskog" <esq...@sommarskog.se> skrev i meddelandet

news:Xns9CEAE693D...@127.0.0.1...

[SQLSQUIRREL]

Tony,

To test "denying access" of Windows Account you will need to execute the
following in SQL Server 2005 express:
DENY CONNECT SQL TO <Windows Account Name>

Try that and let us know how that worked for you.

Brett
http://www.lockergnome.com/sqlsquirrel/

"Tony Johansson" wrote:

> .
>