jwcolby
jwcolby at colbyconsulting.com
Sat Feb 19 19:14:52 CST 2011
> Server logins have to already exit before you can add them as a user. Are you talking about Windows users? I specifically don't want to do that. Imagine a web app where the world might come in and read a table. It makes no sense to require a windows user before database access. In my case I will have perhaps 40-80 people coming in to 3 or 4 different databases. A handful of these will be able to do "database maintenance", things like adding to list tables. Most will only be able to run reports. Some will add time sheet records. I have no intention of adding 40-80 people that I do not know to my Windows users if I can avoid it. Or are you talking about SQL Server Logins? This is my problem, everyone starts discussing this in the middle. Start with either "Go to this web page to learn this stuff" or "A server login is... and you add one by..." If I don't know what you mean by "a server login" then knowing that one needs to exist first is... not useful. John W. Colby www.ColbyConsulting.com On 2/19/2011 7:24 PM, David McAfee wrote: > 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. > 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 >>> >>> >> _______________________________________________ >> 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 > >