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 "TESTDBLOGIN" as the Name.
Select "SQL Server Authentication", and put in a password.
In the drop-down list of databases select "TESTDBLOGIN"
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 "TESTDBLOGIN".
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 "TESTDBLOGIN". If I right-click the "TESTDBLOGIN" 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 "Permissions" 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