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