September 10, 2008 at 7:34 pm
(SQL2005 w sp2)
It looks like I have a fundamental misunderstanding (or under-understanding) of the "right" way to set up security on my sql2005 box.
I have a domain group-- let's call it "domain_developers". It contains one or more users in my environment. I add domain_developers to the server as a login-- and also add it to a database "mydb". I create a db role in MYDB, "role_developers" and make "domain_developers" the only member. I grant permissions to "role_developers".
This has been my general strategy and it *has* been working. However-- I am now running into an issue. This role has permission to the dbo/schema (added on the securables screen for the role) and I've given it all permissions (in the gui) except CONTROL, REFERENCES and TAKE OWNERSHIP (it has ALTER, DELETE, EXECUTE, INSERT, SELECT, UPDATE, VIEW DEFINITION).
User (in the domain group) goes into the db. Cannot create a new table. I begin troubleshooting by creating a sql authenticated login on the server and adding it to the db and the role so I can simulate his permissions. I cannot create a table either. I try fully qualified and without qualifying it:
create table foo (bar int)-- CREATE TABLE permission denied
create table mydb.dbo.foo (bar int)-- CREATE TABLE permission denied
For giggles I try
create view v_miketest as (select @@servername sname) -- WORKS
So I start poking around, and add another securable (not saved yet, just investigating it): I add mydb/database. Without doing anything-- I see these permissions already granted:
CREATE FUNCTION
CREATE PROCEDURE
CREATE VIEW
Well, ok... at least that explains why I could create the view. But I'm not clear on why those perms already exist in mydb/database; and if I have perm to CREATE a PROC, VIEW or FUNC then why not also TABLE? (And why, since these perms exist, wasn't mydb/database showing as one of the securables?)
BEGIN side_note
I created a brand new role and set it's schema perms to those shown for the other. I removed my test acct from the original one and added it here. I open mydb/database and it shows no perms... so it LOOKS like the existing perms for this under role_developers must be leftovers from something else... In this environ, my test account cannot create a view-- as expected.
END side_note
*A possible/partial solution* is to grant "CREATE TABLE" to mydb/database-- but since I've already found myself in unexpected waters I wanted to confirm from the brain trust THAT is the way to go. Do I get more granularity if I grant permissions under mydb/database instead of dbo/schema?
If I've commited some obvious strategic blunder I'd be glad to know so I can eliminate this problem. If you know a good reference (online if possible) that might help me sort this all out, please pass it along.
Any other recommendations?
September 11, 2008 at 12:23 pm
Additional Notes: I've found/noticed/realized that when you grant securable/database/dbname and set various perms in it, the next time you look at that role, it will not show securable/database/dbname. I've decided I can drop securable/schema/dbo/ALTER... I just found the BOL comment that appears to contradict what I see:
ALTER (from "Permissions", SQL Server 2005 Books Online (September 2007))
Confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope. For example, ALTER permission on a schema includes the ability to create, alter, and drop objects from the schema
Instead, under securable/database/dbname I grant CREATE FUNCTION,CREATE PROCEDURE,CREATE TABLE,CREATE VIEW,SHOWPLAN.
Again-- If I am off in the weeds here, feel free to slap me around.
(Addendum: I added the perms under securables/database/dbname and removed alter under the schema but then could not create the objects I'd just granted... so I had to re-add "alter"-- and now it works. But I have to do some more homework to make sure I didn't just allow more than I wanted)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply