Arthur Fuller
fuller.artful at gmail.com
Sat Feb 19 15:38:21 CST 2011
The hierarchy goes like this: Roles -- Users What is not obvious from the docs is that you can add a role to a role. The reason you would want to do this is to "include" lower-level capabilities within a higher-level group (without bothering to have to re-define these). You can grant select, update, delete and inserts on any combination of tables, views and sprocs. The approach I typically use is to deny table access to everyone but me, and then to grant various levels of access to views and sprocs to various roles. That way, no one but you can directly hit a table. So, your bottom level might define Select capability and nothing else (to one or more views and sprocs). The next level up might permit Updates, and the next Inserts and Deletes. Actually I mean granting this privileges on the sprocs/views created for those purposes. As you move up the hierarchy, you can "stack" the abilities (i.e. add the lowest level role to the next up, and so on, until you reach the top, where the only member of that role is you. HTH, and if not feel free to ask. Arthur On Sat, Feb 19, 2011 at 2:53 PM, jwcolby <jwcolby at colbyconsulting.com>wrote: > I am getting nowhere on understanding SQL Server security. Microsoft > provides us with SQL Server Express which implies that joe blow (me) is > going to install / maintain it. > > I am not a SQL Server Admin and I cannot afford to spend the time to be > one. > > Google is my friend. BOL is not. > > Except that Google is taking me to these places where I am expected to > already know how this stuff works, and then wants to make me a *better* > administrator. Which of course is useless because I am not an administrator > at all. > > OTOH I am not stupid. If I could find something that started at the "This > is SQL Server security" basics I could learn this stuff. Before anyone says > "RTFM (BOL)" let me simply say, "not happening". I have tried BOL and it > simply sucks for my level of expertise (my opinion of course). If that is > your advice, simply stay out of this thread. Thanks! > > So... my needs: > > I need to set up several SQL Server databases for use by different, very > small groups (5-20 people) of entirely unrelated people. What I mean by > that is that each DB is for a different "company" if you will. I need to > access these databases from C#. I understand the group / user paradigm. I > would like to create groups and users. Specific groups can do specific > things in the database, some can see data but not modify it. Some can add > records in specific tables but not others. Some can run reports (view). > > I do *NOT* want to create windows level groups and users if I can avoid it. > These are people that I do not necessarily know and I do not want to give > them any rights at the machine level, and I prefer to not maintain such > lists at the machine level. > > Unfortunately SQL Server does not seem to model Groups / users. I go into > SQL Server and see a security tab. It has "logins". Is that a user? A > specific ability to log in with a password? To what? The server itself? A > specific database? Groups of databases? > > I see "roles" but these appear to be aimed at the server and none of these > people are going to be doing anything at the server level. > > Can I safely ignore everything under the server security tab? > > I go to a database and I see a security tab. It has users and roles. > Hmm... better (I would think). I would like to add users "under" the > specific database that the user will access. > > So I try to add a new user but I do not see anywhere to require a password. > Hmmm... > > I go into roles and I do not see any predefined role that looks like it > would be useful to me in meeting my needs described above. If I look at > "add new role" it asks for a password. The User / group model does nto > assign passwords at the group level which implies that a role is not a group > at the user / group paradigm. > > Is it just me, or is SQL Server security just... different? Am I correct > in assuming that it doesn't implement a user / group paradigm? > > And more importantly, where can I go to get a plain, simple, English > description of how this mess works? > > And please excuse the tone that results from my frustration. The only help > documents that I have found (and I have extensive lists of bookmarked web > pages) so far assume that I am an administrator. I am not, and cannot > afford to become one. And yet MS pushes SQL Express as if I (non-admin) > should be able to use this as a data store pool. > > Help! > > -- > John W. Colby > www.ColbyConsulting.com <http://www.colbyconsulting.com/> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >