Complete Neophyte Question(s)

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. I
digress...
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. One of our users can modify a particular
view, and another can't. The only difference I can see is that one is a
member of this role and the other isn't. If I remove the role from the user
who CAN modify the view, he no longer can. Alas, I have no idea where this
is defined.
Schemas are also slightly confusing, but I imagine that's just a way of
logically grouping sets of objects. 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.
I'm not even certain what this effectively did, but it solved the problem
while I try to figure out how this whole thing works.
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.I'd recommend that you go for a training course ;-)
"James" <minorkeys@.gmail.com> wrote in message
news:eVtdKXmuHHA.1168@.TK2MSFTNGP02.phx.gbl...
>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. I
>digress...
> 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. One of our users can modify a
> particular view, and another can't. The only difference I can see is that
> one is a member of this role and the other isn't. If I remove the role
> from the user who CAN modify the view, he no longer can. Alas, I have no
> idea where this is defined.
> Schemas are also slightly confusing, but I imagine that's just a way of
> logically grouping sets of objects. 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.
> I'm not even certain what this effectively did, but it solved the problem
> while I try to figure out how this whole thing works.
> 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.
>|||James (minorkeys@.gmail.com) 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 sophisticate
d.

> 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 dam
ned 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-7808
659449c5.htm
in Books Online, but admittedly Books Online is surprisingly thin on
some of the permission topics.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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@.sommarskog.se> wrote in message
news:Xns995FC3C3DACYazorman@.127.0.0.1...
> James (minorkeys@.gmail.com) writes:
> From having been very simple-minded in SQL 4.x, it is now quite
> sophisticated.
>
> 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.
>
> 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.
>
> 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.
>
> The normal starting point would be
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-78
08659449c5.htm
> in Books Online, but admittedly Books Online is surprisingly thin on
> some of the permission topics.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||James (minorkeys@.gmail.com) writes:
> 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?
I did some research, and I think I have the answer. If you do Help->About
what version do you get for Managment Studio? My guess is that you will
see something 9.00.1399 or 9.00.2047, that is either RTM or SP1. To wit,
when I try this on SP1 of SSMS, I don't see the securables, but SP2 gives
me the list of objects.
You can find the latest service pack for SQL Server on
http://support.microsoft.com/kb/913089/.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I don't use the word hero very often, but you sir, are the greatest hero of
all time. Thank you for confirming I'm not insane. SP2 has caused the
Securables tab to populate properly and now it actually makes sense.
The only other issue that I'm trying to wrap my head around and can't seem
to google well is the difference between "GRANT" and "WITH GRANT". Can you
enlighten me?
Thank you a million times over!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996577CE42B7BYazorman@.127.0.0.1...
> James (minorkeys@.gmail.com) writes:
> I did some research, and I think I have the answer. If you do Help->About
> what version do you get for Managment Studio? My guess is that you will
> see something 9.00.1399 or 9.00.2047, that is either RTM or SP1. To wit,
> when I try this on SP1 of SSMS, I don't see the securables, but SP2 gives
> me the list of objects.
> You can find the latest service pack for SQL Server on
> http://support.microsoft.com/kb/913089/.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Disregard, seems my google-ing skills were lacking.
More generic question as I'm messing with the Northwind database to get a
thorough understanding of this. If a user isn't a member of any roles and
has no permissions explicitly defined, does it err on the side of deny or
grant? Or does that depend on the permission? Right now I have a user who
has Connect as the only database level permission, no roles, no secureables
but can still view definition, it seems. I'm able to connect and view all
of the tables, although everything else seems locked down. I have refreshed
and can still see them.
Either way, thanks again. I'm much much further than I was yesterday at
this time.
"James" <minorkeys@.gmail.com> wrote in message
news:uFdBmN%23vHHA.2304@.TK2MSFTNGP06.phx.gbl...
>I don't use the word hero very often, but you sir, are the greatest hero of
>all time. Thank you for confirming I'm not insane. SP2 has caused the
>Securables tab to populate properly and now it actually makes sense.
> The only other issue that I'm trying to wrap my head around and can't seem
> to google well is the difference between "GRANT" and "WITH GRANT". Can
> you enlighten me?
> Thank you a million times over!
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns996577CE42B7BYazorman@.127.0.0.1...
>|||James (minorkeys@.gmail.com) writes:
> Disregard, seems my google-ing skills were lacking.
You should not have to go Google to find out what WITH GRANT means. SQL
Server comes with online documentation on you hard disk.
WITH GRANT is one of the more esotheric features in SQL Server in my
opinion, but maybe that says more about the simplistic security of the
system I work with.

> More generic question as I'm messing with the Northwind database to get
> a thorough understanding of this. If a user isn't a member of any roles
> and has no permissions explicitly defined, does it err on the side of
> deny or grant? Or does that depend on the permission? Right now I have
> a user who has Connect as the only database level permission, no roles,
> no secureables but can still view definition, it seems. I'm able to
> connect and view all of the tables, although everything else seems
> locked down. I have refreshed and can still see them.
If no permissions have been granted, then you have no permissions. That is,
if run the below in a database, the SELECT should not return anything:
CREATE LOGIN erik WITH PASSWORD='rtsoppa'
go
CREATE USER erik
go
EXECUTE AS LOGIN = 'erik'
go
SELECT name FROM sys.objects
go
REVERT
go
DROP USER erik
go
DROP LOGIN erik
...unless rights have been granted to the public role.
In SQL 2005 a user only has permission to see the definition of objects
to which he been granted some access. More exactly he needs VIEW DEFINITION,
but this permission is implied if he already has SELECT permission.
Where DENY comes in is that it overrides GRANT. Say that a user is a member
of a role that has SELECT permission to a table X, but that himself he
has been denied access to the table. Then he cannot access that table.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That makes complete sense to me, but somehow I'm overlooking something.
I have a user named james on a database. If I go into properties for that
user they have no owned schemas. They have no role membership (including
public, which isn't listed here for some reason). If I right-click the
database and go to properties -> Permissions, the only permission they have
is Connect, not view definition.
The only thing I can see is that there's a login of the same name at the
server level which is a member of the Server Role public, but my
understanding is that it's unrelated.
So the long and the short of it, is that this user can view definition on
this database, and I can't figure out why.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996668C8847CBYazorman@.127.0.0.1...
> James (minorkeys@.gmail.com) writes:
> You should not have to go Google to find out what WITH GRANT means. SQL
> Server comes with online documentation on you hard disk.
> WITH GRANT is one of the more esotheric features in SQL Server in my
> opinion, but maybe that says more about the simplistic security of the
> system I work with.
>
> If no permissions have been granted, then you have no permissions. That
> is,
> if run the below in a database, the SELECT should not return anything:
> CREATE LOGIN erik WITH PASSWORD='rtsoppa'
> go
> CREATE USER erik
> go
> EXECUTE AS LOGIN = 'erik'
> go
> SELECT name FROM sys.objects
> go
> REVERT
> go
> DROP USER erik
> go
> DROP LOGIN erik
> ...unless rights have been granted to the public role.
> In SQL 2005 a user only has permission to see the definition of objects
> to which he been granted some access. More exactly he needs VIEW
> DEFINITION,
> but this permission is implied if he already has SELECT permission.
> Where DENY comes in is that it overrides GRANT. Say that a user is a
> member
> of a role that has SELECT permission to a table X, but that himself he
> has been denied access to the table. Then he cannot access that table.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||James (minorkeys@.gmail.com) writes:
> That makes complete sense to me, but somehow I'm overlooking something.
> I have a user named james on a database. If I go into properties for
> that user they have no owned schemas. They have no role membership
> (including public, which isn't listed here for some reason). If I
> right-click the database and go to properties -> Permissions, the only
> permission they have is Connect, not view definition.
> The only thing I can see is that there's a login of the same name at the
> server level which is a member of the Server Role public, but my
> understanding is that it's unrelated.
> So the long and the short of it, is that this user can view definition on
> this database, and I can't figure out why.
If you under database roles look at the public role, does it have any
permissions on anything?
If you run:
execute as login = 'james'
go
select * from sys.fn_my_permissions('dbo.Orders', 'object')
go
revert
What do you see?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx