Grant, Revoke, Deny

Context: SQLServer 2000
I have a single table in the database that I want role "Apps" to have SELECT
access only, so using the UI, I find the table and check the SELECT column
for the table and choose the X (to deny) for INSERT, UPDATE, DELETE.
Next, I want to ensure that no one that has public rights to the database
can do anything with this table. First time through I put an X (to deny) for
all four permissions, which prevented everyone (including sa) from
selecting, etc. I found this to be 'by design' behavior so I using the Query
Analyzer to issue the REVOKE method on the table for all 4 items. sa was
then able to still do everything.
My problem is that anyone in the db_datawriter role (or db_datareader) can
still write (read) from the table. I am unable to reassign permissions to
special roles.
I guess I could not use the special role for my datareaders/writers but the
advantage of these roles is that I don't have to continuously go and grant
permissions. If this is my only option in this scenario, then I guess I will
have to go with it - I was just hoping there was something more elegant.
Thanks in advancePermissions are cumulative and DENY takes precedence. REVOKE removes GRANT
and DENY permissions previously assigned. Because all users are members of
public, you should DENY permissions to public only when you want to prohibit
access by all database users.
'sa' is a member of the sysadmin fixed server role and permissions are not
checked for sysadmin role members. I believe you are mistaken when you say
that you prevented 'sa' from accessing the table.
One method to allow Apps users to SELECT from the table and prevent other
'db_datareader' and 'db_datawriter' access:
1) GRANT SELECT ON YourTable TO Apps
2) DENY INSERT, UPDATE, DELETE ON YourTable TO Apps
3) create a new role to prevent access (e.g. 'DenyCertainTables')
4) DENY ALL ON YourTable TO DenyCertainTables
5) add all 'db_datareader' and 'db_datawriter' role members, except Apps
role members, to the 'DenyCertainTables' role.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:uqHH2W8tFHA.3528@.TK2MSFTNGP15.phx.gbl...
> Context: SQLServer 2000
> I have a single table in the database that I want role "Apps" to have
> SELECT
> access only, so using the UI, I find the table and check the SELECT column
> for the table and choose the X (to deny) for INSERT, UPDATE, DELETE.
> Next, I want to ensure that no one that has public rights to the database
> can do anything with this table. First time through I put an X (to deny)
> for
> all four permissions, which prevented everyone (including sa) from
> selecting, etc. I found this to be 'by design' behavior so I using the
> Query
> Analyzer to issue the REVOKE method on the table for all 4 items. sa was
> then able to still do everything.
> My problem is that anyone in the db_datawriter role (or db_datareader) can
> still write (read) from the table. I am unable to reassign permissions to
> special roles.
> I guess I could not use the special role for my datareaders/writers but
> the
> advantage of these roles is that I don't have to continuously go and grant
> permissions. If this is my only option in this scenario, then I guess I
> will
> have to go with it - I was just hoping there was something more elegant.
> Thanks in advance
>|||Thanks Dan,
I made a freshman mistake and when I thought the 'sa' couldn't access I was
in the wrong window.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ez9IfD9tFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Permissions are cumulative and DENY takes precedence. REVOKE removes
GRANT
> and DENY permissions previously assigned. Because all users are members
of
> public, you should DENY permissions to public only when you want to
prohibit
> access by all database users.
> 'sa' is a member of the sysadmin fixed server role and permissions are not
> checked for sysadmin role members. I believe you are mistaken when you
say
> that you prevented 'sa' from accessing the table.
> One method to allow Apps users to SELECT from the table and prevent other
> 'db_datareader' and 'db_datawriter' access:
> 1) GRANT SELECT ON YourTable TO Apps
> 2) DENY INSERT, UPDATE, DELETE ON YourTable TO Apps
> 3) create a new role to prevent access (e.g. 'DenyCertainTables')
> 4) DENY ALL ON YourTable TO DenyCertainTables
> 5) add all 'db_datareader' and 'db_datawriter' role members, except Apps
> role members, to the 'DenyCertainTables' role.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:uqHH2W8tFHA.3528@.TK2MSFTNGP15.phx.gbl...
column[vbcol=seagreen]
database[vbcol=seagreen]
can[vbcol=seagreen]
to[vbcol=seagreen]
grant[vbcol=seagreen]
>sql

Global Property in SQL?

Here's one for you all...
[SQL Server 2000]
I need to somehow create a place (in memory?) that will store a value.
This value needs to be visible to all sessions
BUT - it needs to die if and when the session that created it dies.
Because a session can die unexpectedly, I cannot rely on removing this "glob
al property" programmatically.
I cannot use a standard table, because if the session dies the value will re
main...
The closest I can come to is to create a temp object in tempdb and name that
object the value of the "global property".
This way others could query tempdb..sysobjects and get the value -- and the
object would die when the session does.
But I would hate to implement a hack like that.
There has got to be a better way!
BTW I have to do this in TSQL but if there an extended sp out ther that can
help me...?
Haven't found anything in BOLSound just like what a global ##temptable does...?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:O%23CrMPziGHA.412@.TK2MSFTNGP05.phx.gbl...
Here's one for you all...
[SQL Server 2000]
I need to somehow create a place (in memory?) that will store a value.
This value needs to be visible to all sessions
BUT - it needs to die if and when the session that created it dies.
Because a session can die unexpectedly, I cannot rely on removing this "glob
al property"
programmatically.
I cannot use a standard table, because if the session dies the value will re
main...
The closest I can come to is to create a temp object in tempdb and name that
object the value of the
"global property".
This way others could query tempdb..sysobjects and get the value -- and the
object would die when
the session does.
But I would hate to implement a hack like that.
There has got to be a better way!
BTW I have to do this in TSQL but if there an extended sp out ther that can
help me...?
Haven't found anything in BOL|||See if this helps:
http://groups.google.com/group/micr...br />
b19b10bd
Anith|||Thanks Anith,
Exactly what I was looking for.
Robert
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message news:uUVZSlziGHA.1204@.TK2MSFTNGP0
2.phx.gbl...
> See if this helps:
> http://groups.google.com/group/micr... />
08b19b10bd
> --
> Anith
>