[AccessD] [dba-SQLServer] I'm getting nowhere

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
>
>



More information about the AccessD mailing list