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

google your database

I hope you'll like a tool I've made called Nautilus.

Nautilus (sqlserver) v1.0.1
Google your MS SQLServer database
http://sourceforge.net/projects/nautilus/
Author: Marcos Luis Casamayor (marcos.casamayor@.gmail.com)

WHAT
--
Nautilus is a tool for developers, advanced users and auditors.
The main objetive is to find information in a SQLServer database without knowing too much about the database. User can search tables and records with no SQL knowledge required.
You can also browse related data following the foreign key links.Since this appears to be a freeware application, I am going to let it stay in this forum. If I find out otherwise I will kick the thread.|||Product won't handle integrated security. Requires SQL Server login.
Unable to use the product because "Page cannot be displayed" errors in application.

Uninstalling...|||...I have a script that will do this anyway, if anybody needs this functionality.|||Product won't handle integrated security. Requires SQL Server login.
Unable to use the product because "Page cannot be displayed" errors in application.

Uninstalling...

Its true, the tool requires knowing database connection info.
But once loged in you can find tables (sometimes you dont remember exact name), then click over it and see its data and structure, then locate a record filtering data, then pivot a record, see its complete info and follow the foreign keys "paths" to the records it refers to and see which records refer it. A diferent feature is finding a value in any record of any table which only sometimes is needed (this is the "google data" option).

I know that if you give it a try you won't be disappointed.
Marcos.|||I did give it a try. All I got was "page cannot be displayed" errors.|||I did give it a try. All I got was "page cannot be displayed" errors.

blindman, I'm very sorry about your problems. I would like some clues about your environment (sqlserver version, xp version, and more).

I can only guess that you have c:\nautilus\nautilus\ in your disk and you created the virutal dir to c:\nautilus instead of c:\nautilus\nautilus. There is always a confusion making zip files. I'll explain it better in next readme file.

sorry again, marcos.|||If anybody else on the forum tried this software, I encourage them to report their experiences.
By the way, marcoscasa, are you sure you can legally use the verb "google" to advertise your application? Has the term passed into the public domain? It implies that there is some sort of business relationship between you and the search engine company, or that your application uses the same search algorithms as google.

Good SQL Server Site?

I've been working with FirebirdSQL and Delphi for some years now. Most
of our new projects will be VS2005 and SQLServer 2000/2005. Can anyone
recommend a good site for learning SQL Server SP language?
For instance, how do I refer to a new row/field inserted or updated in
an after insert/update trigger? In FirebirdSQL it would be
"New.FieldName".
Thanks,
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."
this is pretty good
http://www.informit.com/guides/conte...rver&seqNum=46
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:uFmuO5dEGHA.3200@.tk2msftngp13.phx.gbl...
>
> I've been working with FirebirdSQL and Delphi for some years now. Most
> of our new projects will be VS2005 and SQLServer 2000/2005. Can anyone
> recommend a good site for learning SQL Server SP language?
> For instance, how do I refer to a new row/field inserted or updated in
> an after insert/update trigger? In FirebirdSQL it would be
> "New.FieldName".
> Thanks,
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."

Good SQL Server Site?

I've been working with FirebirdSQL and Delphi for some years now. Most
of our new projects will be VS2005 and SQLServer 2000/2005. Can anyone
recommend a good site for learning SQL Server SP language?
For instance, how do I refer to a new row/field inserted or updated in
an after insert/update trigger? In FirebirdSQL it would be
"New.FieldName".
Thanks,
--
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."this is pretty good
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=46
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:uFmuO5dEGHA.3200@.tk2msftngp13.phx.gbl...
>
> I've been working with FirebirdSQL and Delphi for some years now. Most
> of our new projects will be VS2005 and SQLServer 2000/2005. Can anyone
> recommend a good site for learning SQL Server SP language?
> For instance, how do I refer to a new row/field inserted or updated in
> an after insert/update trigger? In FirebirdSQL it would be
> "New.FieldName".
> Thanks,
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."

Good SQL Server Site?

I've been working with FirebirdSQL and Delphi for some years now. Most
of our new projects will be VS2005 and SQLServer 2000/2005. Can anyone
recommend a good site for learning SQL Server SP language?
For instance, how do I refer to a new row/field inserted or updated in
an after insert/update trigger? In FirebirdSQL it would be
"New.FieldName".
Thanks,
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."this is pretty good
http://www.informit.com/guides/cont...erver&seqNum=46
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:uFmuO5dEGHA.3200@.tk2msftngp13.phx.gbl...
>
> I've been working with FirebirdSQL and Delphi for some years now. Most
> of our new projects will be VS2005 and SQLServer 2000/2005. Can anyone
> recommend a good site for learning SQL Server SP language?
> For instance, how do I refer to a new row/field inserted or updated in
> an after insert/update trigger? In FirebirdSQL it would be
> "New.FieldName".
> Thanks,
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."

Good sql server programming tool

Hi,
Are there any commercial tools available that allow easy management of sql
server objects? Intellisense, object grouping, hiding system objects etc.?
Thanks
Nick
In article <cfdaep$jpb$1@.phys-news-1.nl.colt.net>,=20
Nick.DELETETHISStansbury@.Sage-Partners.com says...
> Hi,
> Are there any commercial tools available that allow easy management of =
sql
> server objects? Intellisense, object grouping, hiding system objects etc.=
?
>=20
> Thanks
>=20
> Nick
>=20
>=20
>=20
Nick,
We have an IDE designed specifically for the development of SQL code=20
objects that is currently in closed beta. Features include source=20
control, logical grouping, intellisense and much more. =20
We are still accepting select sites to participate in our beta testing,=20
and offer free licenses to participating sites that aggressively utilize=20
the product during the beta phase and provide us quality feedback. =A0If=20
you are interested, please send details regarding your testing=20
environment to info@.perfectionedge.com.
Best regards,
David Barber
Perfection Edge
http://www.perfectionedge.com
|||Nick Stansbury wrote:
> Hi,
> Are there any commercial tools available that allow easy management
> of sql server objects? Intellisense, object grouping, hiding system
> objects etc.?
> Thanks
> Nick
Try Speed IDE from Imceda over at http://www.imceda.com.
David G.

Good practice when working with objects using SQLServer as a secure store.

I'm sure this has been asked plenty of times before, so I'm after a link to a good answer.

I have tens of thousands of milk crates, holding dozens of different types of milk in hundreds of locations. I am used to working with objects but not databases. For this situation however I want the security of SQLServer transactions to track, for example, when a robot moves a crate from one location to another.

I am thinking of using SQLServer as a store. On startup I want to get my ecosystem of objects out of the store. While I am running, I'll just use objects. When I change an object property I want it to securely persist. I don't want to snapshot the whole menagerie of object states, just update the values that changed. Which will sometimes include the addition or deletion of objects. How do I do this? Is there an example somewhere that does this (or approximately this)?

I use VB and have Visual Studio 2005. (Which, by the way, is stunning. I thought all that "you will use less time and code more and better" talk was just hype. But its for real. Amazing product.)

tia

John

I think what you're asking is more on the client (VB programming) side than strictly in the database layer. I'd recommend checking out some of the "best practices" books and sites - you can check this one out to start:

http://msdn2.microsoft.com/en-us/vbasic/ms789183.aspx

Sorry if that's too basic - you may have already seen that site.

Buck Woody

http://www.buckwoody.com

good index?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:417616
Hi,
I have a table with
smallint,
smallint,
smalldatetime,
smallint
I made a clustered primary key containg the first 3 colomns.
Inserts are something like:
1,1,12:00,2
1,2,12:00,2
2,1,12:00,2
2,2,12:00,2
1,1,12:10,2
1,2,12:10,2
2,1,12:10,2
2,2,12:10,2
But a lot more rows of course. No updating or deleting.
I assume reindexing is not necessary because I select in the inserted order.
Am I right assuming this?
Thanks
Frank
Keep these points in mind when considering using a clustered index;
* The physical ordering supports the range retrievals of important queries
* The cluster index key is used in the ORDER BY or GROUP BY clause
* The cluster index key is used in important joins relating multiple tables
* The cluster index columns are not changed regularly.
"Frank" wrote:

> Hi,
> I have a table with
> smallint,
> smallint,
> smalldatetime,
> smallint
> I made a clustered primary key containg the first 3 colomns.
> Inserts are something like:
> 1,1,12:00,2
> 1,2,12:00,2
> 2,1,12:00,2
> 2,2,12:00,2
> 1,1,12:10,2
> 1,2,12:10,2
> 2,1,12:10,2
> 2,2,12:10,2
> But a lot more rows of course. No updating or deleting.
> I assume reindexing is not necessary because I select in the inserted order.
> Am I right assuming this?
> Thanks
> Frank
>
>
|||Check Scan Density of the index time to time using DBCC Showcontig.
If its less then 75-80% then either reindex it or defrag it using DBCC
Indexdefrag.
Thanks

good index?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:417616
Hi,
I have a table with
smallint,
smallint,
smalldatetime,
smallint
I made a clustered primary key containg the first 3 colomns.
Inserts are something like:
1,1,12:00,2
1,2,12:00,2
2,1,12:00,2
2,2,12:00,2
1,1,12:10,2
1,2,12:10,2
2,1,12:10,2
2,2,12:10,2
But a lot more rows of course. No updating or deleting.
I assume reindexing is not necessary because I select in the inserted order.
Am I right assuming this?
Thanks
FrankKeep these points in mind when considering using a clustered index;
* The physical ordering supports the range retrievals of important queries
* The cluster index key is used in the ORDER BY or GROUP BY clause
* The cluster index key is used in important joins relating multiple tables
* The cluster index columns are not changed regularly.
"Frank" wrote:

> Hi,
> I have a table with
> smallint,
> smallint,
> smalldatetime,
> smallint
> I made a clustered primary key containg the first 3 colomns.
> Inserts are something like:
> 1,1,12:00,2
> 1,2,12:00,2
> 2,1,12:00,2
> 2,2,12:00,2
> 1,1,12:10,2
> 1,2,12:10,2
> 2,1,12:10,2
> 2,2,12:10,2
> But a lot more rows of course. No updating or deleting.
> I assume reindexing is not necessary because I select in the inserted orde
r.
> Am I right assuming this?
> Thanks
> Frank
>
>|||Check Scan Density of the index time to time using DBCC Showcontig.
If its less then 75-80% then either reindex it or defrag it using DBCC
Indexdefrag.
Thanks

Good books on SQL Server Reporting Services

Can you please share with me if you are aware of any good books on SQL
Server reporting services.
Many Thanks
SimonReporting services is so new that I don't think there are any books
published on it yet.
This is your starting point:
http://www.microsoft.com/sql/reporting/default.asp
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Simon Lenn" <simonlenn@.yahoo.com> wrote in message
news:3641e2c2.0312191558.270cef9b@.posting.google.com...
Can you please share with me if you are aware of any good books on SQL
Server reporting services.
Many Thanks
Simon

Good Book on Datawarehousing with SqlServer?

Can anyone recommend a good book on designing and implementing a data
warehouse using sql server specifically? Not just one in your library, but
one that stands-out?
Thanks,
Michael
The Good Books that can be refered are
1)OLAP Analysis services 2000 By WROX Press
2)MDX solutions with Microsoft SQL server Analysis Services By George
spofford -Wiley press
3)Microsoft OLAP solutions by George spofford ,Erik Thomsen & Dick chase
-Wiley Press
Regards,
SriniPeela
"Snake" wrote:

> Can anyone recommend a good book on designing and implementing a data
> warehouse using sql server specifically? Not just one in your library, but
> one that stands-out?
> Thanks,
> Michael

Going nuts here!

Alright- I plugged this code in to tell my form to write to SQLServer when I click the Submit button. However I error out when I load the form:

dim Conn as new OleDbConnection("DSN=MyDB")

Sub On_Click(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "'," & _
"'" & params(1) & "'," & _
"'" & params(2) & "'")

ExecuteStatement(strSQL)
end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand(strSQL, Conn)

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as Exception
end try
objCmd.Connection.Close()
end function

I get this error:
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(14) : error BC30471: Expression is not an array or a method, and cannot have an argument list.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(14) : error BC30205: End of statement expected.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(15) : error BC30035: Syntax error.

"'" & params(0) & "'," & _

Any ideas??try this :

strSQL="INSERT INTO tblSCRequest (strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "','" & params(1) & "','" & params(2) & " ')"

better yet, use parameterized queries. (1) you dont have to worry about these quotes (2) you are safe from sql injection attacks.

hth|||So what if for simplicity sake I try this-

strSQL=""INSERT INTO tblSCRequest (strRequestor) VALUES (" & _
";" & params (0) & "')"

Would this work? Also, I saw your link to the param site. If I get this working I'm going to use that going forward. Thanks.|||no a ; indicates end of sql statement. use parameterized queries. thats the best way to go about it.

hth|||Okay. I have this problem now-

Sub Submit(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO Comm (strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "','" & params(1) & "','" & params(2) & " ')"

ExecuteStatement(strSQL)

end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand()

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as exception
end try
objCmd.Connection.Close()
end function

I get an error saying "System.NullReferenceException: Object reference not set to an instance of an object." And it is pointing to line 31 as the culprit-

Line 29: catch ex as exception
Line 30: end try
Line 31: objCmd.Connection.Close()<---Error line
Line 32: end function|||(1) use parameterized queries. ( i suggested this earlier too)
(2) make sure you have values in all the parameters.
(3) put the connection.close in the finally block

hth

global variables in dtspump task

I have two connections, an oracle and sqlserver. the dtspump task is pulling data from oracle to sqlserver. I am trying to automate this package by substituting the where clause values to variables since they are based on year and month. My question is - how does one use global variables in a dtspump task? How can I use variables to do this? Can it be done or do I need to use an Active X script for it?

thanks for any help.
novice userMay check with SQL DTS (http://www.sqldts.com) website.