sql server new users: What's the difference between a role and a schema? Both appear to be bundles
of permissions owned by a user. But a schema can be owned by only one user,
while a role can be assigned to multiple users.

If that's the case, then when creating a new database user in SQLSMS, why do
I have lists of Schemas to be owned by the user and role membership for the
user? They look like the same thing.

Thanks,

David Veeneman
Foresight Systems

hi David,
[quoted text, click to view]
actually a Role is a convenient way to "package" and manage an omogeneus set
of database users..
a schema, on the contrary, is a "container" where objects are created in...
it's not directly related to database users but to database objects.. by
this you can consider grouping omogeneus set of objects in separated
containers (aka schemas), so that all tables, views, stored procedures
related to accounting information will be contained in the Payroll schema,
where all objects related to employees and the like could be contained in
the HumanResources schema... try having a look at the new database sample,
AdventureWorks, you can download for free at
http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en
...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
--------- remove DMO to reply

Thanks--but I'm still puzzled. When I create a new database user in SQL
Management Studio Express, The Database User - New dialog contains
pick-lists for "Schemas owned by this user" and "Database role membership".
If I specify a role membership, do I also need to specify a schema
ownership, or do I specify a schema only if I don't specify a role? Thanks
again.

David Veeneman
Foresight Systems

Very helpful--thanks much.

hi David,
[quoted text, click to view]

the 2 "things" are separated....
a user can, but not mandatory, own 1 or more schemas..
a user must be member of a database role (at least the "public" one).. and
this is mandatory...

if you specify a role membership, say db_datareader, implies that the user,
as long as all other role members, can read each user table without
requiring explicit permission sets... but this has nothing to do with
ownership and schemas.. loosely speaking, role membership is related to
permissions sets against database objects (or "securable")..

do you need to specify a schema ownership?...
if you do not specify an existing schema as default schema, a specific
(quiet reserved :D) schema, named as the user name will be created as well,
owned by the user him self...
so if you generate a new database user named "Andrea" (only member of the
public role, for this scenario)and do not specify his default schema as the
existing "dbo" schema, a new schema named "Andrea" as well will be
generated, owned by the "Andrea" database user....
this does not imply, BTW, that user Andrea, owning a personal schema (named
Andrea as well) has particular permission on that schema, such as creating a
new table like
CREATE TABLE Andrea.MyPrivateTable ( id int );
actually resulting in
Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database 'test'.

as "public" member (obviously) do not have ddl permission ( :D)
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
--------- remove DMO to reply