Restrict user from default tables (access given by public role)

I am newbie.. probably this is a silly question..
I added few users with only SELECT permissions to 2 User Tables. But
when these users login,they could also see multiple system tables
(syscontraints, syssegments , dtproperties) . From reading this forum
and other articles, I understand these are visible due to the user
being part of the default "public" role.
My Question is, is there any way to remove/hide these system tables
from the user?.. The team manager doesnt like these users seeing
anything other than the 2 User Tables they supposed to see..
Thanks in advance for all tips/advices/suggestionsTell your team manager he's a turd. No...actually don't say that, it'll
probably get you fired.
I wouldn't go messing around with the permissions on the system tables,
you're likely to stuff up some functionality of SQL Server in that DB
for those users. The system tables are a very necessary part of
day-to-day data & schema manipulation...for every user of the DB.
Things like optimising query plans, for example, rely on data stored in
the system tables. I'm not positive if things will start breaking if
you start revoking or denying permissions on those system tables but I
wouldn't risk it.
There's an option in SSMS (Tools | Options | Environment | General |
Hide system objects in Object Explorer), not sure about SQLEM or QA,
that will hide the system tables in the GUI but not change their
underlying permissions. That may placate the pointy-haired manager
<http://www.dilbert.com/> asking for this change, perhaps.
What tool are the users using to "see" these system tables?
*mike hodgson*
http://sqlnerd.blogspot.com
sreejith.ram@.gmail.com wrote:

>I am newbie.. probably this is a silly question..
>I added few users with only SELECT permissions to 2 User Tables. But
>when these users login,they could also see multiple system tables
>(syscontraints, syssegments , dtproperties) . From reading this forum
>and other articles, I understand these are visible due to the user
>being part of the default "public" role.
>My Question is, is there any way to remove/hide these system tables
>from the user?.. The team manager doesnt like these users seeing
>anything other than the 2 User Tables they supposed to see..
>Thanks in advance for all tips/advices/suggestions
>
>|||Thanks Mike... This convinced me I should spend my time convincing the
manager to leave it alone than trying to hide the tables
The users are using SQL Server Enterprise Manager.|||> The users are using SQL Server Enterprise Manager.
EM is more of a DBA/Developer tool than an end-user tool. In any case, you
can hide most of the system object noise by unchecking the 'show system
databases and system objects' option under the EM server registration
properties.
Hope this helps.
Dan Guzman
SQL Server MVP
<sreejith.ram@.gmail.com> wrote in message
news:1138983030.862156.116660@.f14g2000cwb.googlegroups.com...
> Thanks Mike... This convinced me I should spend my time convincing the
> manager to leave it alone than trying to hide the tables
> The users are using SQL Server Enterprise Manager.
>|||I thought there was that "hide" option in SQLEM. I just couldn't
remember where it was (been using SSMS too long <g> ).
*mike hodgson*
http://sqlnerd.blogspot.com
Dan Guzman wrote:

>EM is more of a DBA/Developer tool than an end-user tool. In any case, you
>can hide most of the system object noise by unchecking the 'show system
>databases and system objects' option under the EM server registration
>properties.
>
>

Restoring state of database after running a Unit test

Sorry if this is a newbie question.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.

Restoring state of database after running a Unit test

Sorry if this is a newbie question.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.
Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.