How to deny SQL administrator to read data.

As I sad.
How to deny SQL administrator to read data?
SanjaSanja,
Give him a job change.
Seriously, anyone who has sysadmin rights can read any data in the server.
You have to manage this by endeavoring to ensure the ethics and reliability
of your administrator. If you cannot do this, then you have problems. (See
the suggestion above.)
The only other option is to look into encrypting data, but that has its own
load of grief.
Russell Fields
"Sanja Krstic" <sanjakrstic@.hotmail.com> wrote in message
news:OXhsZ1YTEHA.1048@.tk2msftngp13.phx.gbl...
> As I sad.
> How to deny SQL administrator to read data?
> Sanja
>|||Hi,
Only solution which come to my mind is that you have to become the SYSADMIN
and do
all the DBA activities. Otherwise some how a person with sysadmin role can
access every thing.
FYI, Keep belief in your DBA, he never do any thing harm
Thanks
Hari
MCDBA
"Sanja Krstic" <sanjakrstic@.hotmail.com> wrote in message
news:OXhsZ1YTEHA.1048@.tk2msftngp13.phx.gbl...
> As I sad.
> How to deny SQL administrator to read data?
> Sanja
>|||To extend your idea, make wise use of roles defined in SQL server.
There are many predefined role so the origional poster need not always
assign sysadmin role to users.
Maybe create a new role with db_denydatareader is good. (not tested so not
sure if it works)
"Hari" <hari_prasad_k@.hotmail.com> bl
news:exXKk6dTEHA.1508@.TK2MSFTNGP11.phx.gbl g...
> Hi,
> Only solution which come to my mind is that you have to become the
SYSADMIN
> and do
> all the DBA activities. Otherwise some how a person with sysadmin role can
> access every thing.
> FYI, Keep belief in your DBA, he never do any thing harm
> --
> Thanks
> Hari
> MCDBA
> "Sanja Krstic" <sanjakrstic@.hotmail.com> wrote in message
> news:OXhsZ1YTEHA.1048@.tk2msftngp13.phx.gbl...
>|||Hi Lau Lei Cheong,
No, you cant restrict a user with SYSADMIN role.Even if you deny SYSADMIN
user, he will be able to select and update the objects. So no one can
restrict the user with SYSADMIN role.
--
Thanks
Hari
MCDBA
"Lau Lei Cheong" <lau_lc@.yahoo.com.hk> wrote in message
news:url9ECeTEHA.3924@.TK2MSFTNGP10.phx.gbl...
> To extend your idea, make wise use of roles defined in SQL server.
> There are many predefined role so the origional poster need not always
> assign sysadmin role to users.
> Maybe create a new role with db_denydatareader is good. (not tested so not
> sure if it works)
> "Hari" <hari_prasad_k@.hotmail.com> bl
> news:exXKk6dTEHA.1508@.TK2MSFTNGP11.phx.gbl g...
> SYSADMIN
can[vbcol=seagreen]
>|||Oh, what I really mean is to create a new role with simply all rights you
need the user to have but not datareader. Anyway, this is just some thoughts
that haven't been tested.
"Hari" <hari_prasad_k@.hotmail.com> bl
news:O404VbhTEHA.1172@.TK2MSFTNGP11.phx.gbl g...
> Hi Lau Lei Cheong,
> No, you cant restrict a user with SYSADMIN role.Even if you deny SYSADMIN
> user, he will be able to select and update the objects. So no one can
> restrict the user with SYSADMIN role.
> --
> Thanks
> Hari
> MCDBA
> "Lau Lei Cheong" <lau_lc@.yahoo.com.hk> wrote in message
> news:url9ECeTEHA.3924@.TK2MSFTNGP10.phx.gbl...
not[vbcol=seagreen]
> can
>

How to deny information schema views...

Hello, all. I created a login and granted the user access to my sql 2005
database. well when the user creates his odbc dsn to access the database, I
discovered he can also see the INFORMATION_SCHEMA views. What gives? How
can I deny him access to these objects. He should have access to the db I
granted him.
Help!!!!!!!!
RozThe Information Schema views in SQL Server 2005 should only return for the
user information about the objects the user actually has access to. While
this was a prominent information disclosure issue in SQL Server 2000, it's
not as wide open in SQL Server 2005. They are provided for SQL-92 compliance
so that users can query the metadata/schema of the database without having
to query the system tables. Is there a reason you want to block access to
them?
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/

> Hello, all. I created a login and granted the user access to my sql
> 2005 database. well when the user creates his odbc dsn to access the
> database, I discovered he can also see the INFORMATION_SCHEMA views.
> What gives? How can I deny him access to these objects. He should
> have access to the db I granted him.
> Help!!!!!!!!
> Roz|||Hello Roz,
You can't hide the fact the views exist as far as I can tell, but if you
look at what he see, it won't be much if anything. Basically he has to be
able to the see the metadata's metadata, but he shouldn't be able to see
the metadata itself unless you start granting him rights to do so (e.g.,
VIEW DEFINITION).
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for reply. I want to block access because as my users create their
ODBC DSNs, they can open these tables and **change** data. I've tried it an
d
it works. Very scary.
Roz
"K. Brian Kelley" wrote:

> The Information Schema views in SQL Server 2005 should only return for the
> user information about the objects the user actually has access to. While
> this was a prominent information disclosure issue in SQL Server 2000, it's
> not as wide open in SQL Server 2005. They are provided for SQL-92 complian
ce
> so that users can query the metadata/schema of the database without having
> to query the system tables. Is there a reason you want to block access to
> them?
>
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
>|||Kent,
Simply having the "public" role, gets him access to these tables. He (I)
was even able to open these tables say in Access thru ODBC, and potentially
change the data. Scary.
Roz
"Kent Tegels" wrote:

> Hello Roz,
> You can't hide the fact the views exist as far as I can tell, but if you
> look at what he see, it won't be much if anything. Basically he has to be
> able to the see the metadata's metadata, but he shouldn't be able to see
> the metadata itself unless you start granting him rights to do so (e.g.,
> VIEW DEFINITION).
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||I am wondering if you are seeing something else.
Could you please give us the steps you used to open
information schema views and change the underlying data on
SQL Server 2005? Which views, data in what columns?
As far as I know, what you are saying is not possible.
If it is actually other tables you are referring too, I
think you have a permissions issue with how you have
security set up. I think that's likely the issue anyway.
-Sue
On Tue, 20 Mar 2007 16:51:05 -0700, Roz
<Roz@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for reply. I want to block access because as my users create their
>ODBC DSNs, they can open these tables and **change** data. I've tried it a
nd
>it works. Very scary.
>Roz
>"K. Brian Kelley" wrote:
>