Re: Complete Neophyte Question(s)

Thank you very much. I believe I have my head wrapped around this better.

When I go to the Securables tab for this role, there are no objects in the
listbox. Simply an Add button. Yet, if I view the properties of certain
stored procedures it will have that role listed with execute permissions.

This seems like a simple request, but all I want is to see the objects that
a certain role has permissions on, and what those permissions are?
Rhetorical: Why is this so difficult/counter-intuitive?

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns995FC3C3DACYazorman@xxxxxxxxxxxx
James (minorkeys@xxxxxxxxx) writes:
I have inherited a SQL Server (2005) from an outgoing DBA and while I'm
familiar with databases from a data structure/manipulation standpoint,
the
permissions/security model in SQL Server 2005 has baffled me thus far.

From having been very simple-minded in SQL 4.x, it is now quite
sophisticated.

As far as I can tell, our previous DBA create a role which is called
SP_Exec. I know that this role has permissions defined somehow, but I'll
> be damned if I can figure it out.

In Object Explorer, Databases->yourdb->Security->Roles->Database Roles.
Find the role of interest, and click Properties in the context menu.
Go to the Seucrables tab.

Normally, you grant permissions to roles, and then add users to the
roles. If you were to grant rights to users directly, it would be more
difficult to managed.

Schemas are also slightly confusing, but I imagine that's just a way of
logically grouping sets of objects.

Right. There are some security features related to schemas. If you add
an object to a schema owned by someone else, the schema owner becomes
the object that you created. It is also possible to grant permissions
on a schmea, which implies that you get permissions to all existing
and future objects in the schema to which the permissions apply.

At first glance it seems everything is utilizing the a schema called
dbo. One of our users was having trouble until I went into the schema
and added "View Definition" as a permission.

In SQL 2005, users are only permitted to see objects they have permission
to. This is a change from SQL 2000 where the metadata was visible to
all users.

Can anyone point me in the direction of a document that explains this in
English? Perhaps the majority of my hang-ups are with Management Studio
and not the actual structure of the overall permissions model. ANY help
would be appreciated.

The normal starting point would be
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808659449c5.htm
in Books Online, but admittedly Books Online is surprisingly thin on
some of the permission topics.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.