[dba-SQLServer] I'm getting nowhere

David McAfee davidmcafee at gmail.com
Sat Feb 19 18:24:16 CST 2011

Server logins have to already exit before you can add them as a user.

See CREATE USER in Bol / msdn / Google

Sent from my Droid phone.
On Feb 19, 2011 4:19 PM, "jwcolby" <jwcolby at colbyconsulting.com> wrote:
> > 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.
>> 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
>> 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
>> 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,
>> 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,
>> 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
>>> 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
>>> at all.
>>> OTOH I am not stupid. If I could find something that started at the
>>> is SQL Server security" basics I could learn this stuff. Before anyone
>>> "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
>>> 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.
>>> 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
>>> 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
>>> These are people that I do not necessarily know and I do not want to
>>> 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
>>> 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?
>>> specific database? Groups of databases?
>>> I see "roles" but these appear to be aimed at the server and none of
>>> 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
>>> 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
>>> 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
>>> documents that I have found (and I have extensive lists of bookmarked
>>> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com

More information about the dba-SQLServer mailing list