Help with the Security/Login/User area of operations

[teddysnips@hotmail.com]

Currently studying for 70-229.

I'm trying to understand how security for users is managed in SQL
Server. I've been using SQL Server for a few years now, but without
investigating the bits that "just work".

So, here's the scenario. This is more or less how I create all my
applications (which these days are all ASP.NET).

I have a database called "TESTDB" (original, huh?)

Now, I want to create a method for users to access this database, so I
open EM, locate and expand the node for the server containing the
database, and open the "Security" node. Click on the "Logins" leaf,
and in the pane right-click and select "New Login.."

On the "General" tab I enter "TESTDBLOGI N" as the Name.

Select "SQL Server Authentication" , and put in a password.

In the drop-down list of databases select "TESTDBLOGI N"

Ignoring the "Server Roles" tab, I go to the "Database Access" tab and
scroll down until I can see the "TESTDB" database. Check the "Permit"
checkbox, and lo!, the "Database Roles for TESTDB" list is populated,
with "public" already ticked. I check the "db_owner" box, and press
"OK". It asks me to confirm the password, and once that's done there's
a new login called TESTDBLOGIN. What's more in "Users" leaf in the
"TESTDB" database node there's a new user called "TESTDBLOGI N".

All well and good. I can now create a connection using this login and
do more or less what I want in the TESTDB database.

But.

If I click the "Users" leaf in the "TESTDB" node, there are two users -
"dbo" and "TESTDBLOGI N". If I right-click the "TESTDBLOGI N" user, I
can see that it has Database Role Memberships for the "public" and the
"db_owner" roles. But if I examine the permissions on these two roles
(by selecting the row, and then pressing the "Permission s" button) I
find that there are permissions set for EITHER role - all the check
boxes are blank!

So, how is it that I can do SELECT, UPDATE, INSERT and DELETE
operations via this login/user?

Sorry it all took so long - I just wanted to get it right.

Thanks

Edward
--
The reading group's reading group:

http://www.bookgroup.org.uk

[Jeffrey Sheldon via SQLMonster.com]

Re: Help with the Security/Login/User area of operations

Edward,

Our Financial system has that configuration.

Using your example: TESTDB is a user that is granted the db_owner role but
TESTDB is not the dbo of the database/table.

Under User Properties you see TESTDB as granted to public/db_owner checked.

But if you choose Permissions you see nothing checked.

If you look above the window grid you see 'List only objects with
permissions for this user'. If you click this I will bet you have nothing
in your grid.

TESTDB has inherited the permissions based on the role you have given them
(in this case db_owner).

IF you provided a permission (like exclude DELETE on a specific table) then
this would show as checked in permissions.

The permissions is the exception to what the role has given.

I tried to communicate this the best I could...sorry if it is a bit choppy.

Jeff

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Re: Help with the Security/Login/User area of operations

(teddysnips@hot mail.com) writes:[color=blue]
> If I click the "Users" leaf in the "TESTDB" node, there are two users -
> "dbo" and "TESTDBLOGI N". If I right-click the "TESTDBLOGI N" user, I
> can see that it has Database Role Memberships for the "public" and the
> "db_owner" roles. But if I examine the permissions on these two roles
> (by selecting the row, and then pressing the "Permission s" button) I
> find that there are permissions set for EITHER role - all the check
> boxes are blank!
>
> So, how is it that I can do SELECT, UPDATE, INSERT and DELETE
> operations via this login/user?
>
> Sorry it all took so long - I just wanted to get it right.[/color]

If I understood this correctly, this user belongs to the db_owner role.
In this case he has permissions to do anything in the database.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[teddysnips@hotmail.com]

Re: Help with the Security/Login/User area of operations



Erland Sommarskog wrote:[color=blue]
> (teddysnips@hot mail.com) writes:[color=green]
> > If I click the "Users" leaf in the "TESTDB" node, there are two users -
> > "dbo" and "TESTDBLOGI N". If I right-click the "TESTDBLOGI N" user, I
> > can see that it has Database Role Memberships for the "public" and the
> > "db_owner" roles. But if I examine the permissions on these two roles
> > (by selecting the row, and then pressing the "Permission s" button) I
> > find that there are permissions set for EITHER role - all the check
> > boxes are blank!
> >
> > So, how is it that I can do SELECT, UPDATE, INSERT and DELETE
> > operations via this login/user?
> >
> > Sorry it all took so long - I just wanted to get it right.[/color]
>
> If I understood this correctly, this user belongs to the db_owner role.
> In this case he has permissions to do anything in the database.[/color]

Thanks for this. I suppose it's pretty obvious when you think about
it, but the permissions for db_owner are not explicity spelled out in
the book I am following. Also, I don't really understand why the
Permissions properties for the user aren't filled in (when you press
the "Permission s" button), but that's a side issue.

Thanks again.

Edward