jwcolby
jwcolby at colbyconsulting.com
Sat Feb 19 18:18:04 CST 2011
> The hierarchy goes like this: > > Roles > -- Users But why does the user have no (apparent) password but the role does? I found a vague (to me) reference to schemas and assigning schemas to users... Now that makes sense. I assume in all this that if a user goes away I just delete the user? I don't see any way to enable / disable the user. This whole thing just seems real hokey. John W. Colby www.ColbyConsulting.com On 2/19/2011 4:38 PM, Arthur Fuller wrote: > 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 >> >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >