Re: Disable Sysadmin to view metadata in SQL2005

Thank you so much! So what do you recommend/best practices in
deploying a system to another clients so that they won't see database
schemas, given the fact that they have sysadmin rights to that box? In
the data layer, we're implementing encryption in a couple of key fields
so that even sysadmin won't be able to see those info?

Thanks again for your help!
Dex

Laurentiu Cristofor [MSFT] wrote:
You cannot deny permissions to a sysadmin, so you cannot prevent him to
access information that, by definition, he is supposed to access.

You can, however, restrict who is a sysadmin and if you need other users to
perform administrative tasks, look at granting only the minimal permissions
required to perform those tasks. If the permissions are not granular enough,
look at granting access via signed code - this way you can avoid granting
the permissions required by the operation and instead you can grant EXECUTE
permission on code that "packs" the access to the operation.

Thanks

--
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/

This posting is provided "AS IS" with no warranties, and confers no rights.

"Dex" <dplaras@xxxxxxxxxxx> wrote in message
news:1153258018.919768.201370@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Tim,

There's a SQL statement in 2005 that disables users view access of the
metadata. I think it's View Any Database / View Server State/ View
Definition. My question is that if we can disable SA account to view
the metadata (tables, columns, etc)?

Thanks,
Dex

Tim Stahlhut wrote:
There better not be an option to do that; it would be insane to do it.

Tim S

"Dex" <dplaras@xxxxxxxxxxx> wrote in message
news:1153253585.004826.199140@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Everyone,

Is there any way we can disable sysadmin/sa to view metadata on a
particular database in SQL2005?

Thanks,
Dex



.

Relevant Pages

  • Re: db_denydatawriter
    ... perhaps this also gives read write access on the database to this user? ... Resrictive permissions overrides in its own level. ... However, if she has sysadmin right, then she'll be able to modify that data. ... Is it possible she has some admin rights which override DenyWriter (though ...
    (microsoft.public.sqlserver.security)
  • Re: Disable Sysadmin to view metadata in SQL2005
    ... You cannot deny permissions to a sysadmin, so you cannot prevent him to ... You can, however, restrict who is a sysadmin and if you need other users to ... There's a SQL statement in 2005 that disables users view access of the ... the metadata? ...
    (microsoft.public.sqlserver.security)
  • Re: Default permissions on new database
    ... All sysadmin role members are automatically "dbo" in all databases with no explicit mapping. ... that creates the database is the one-and-only database owner and is actually mapped to the "dbo" user. ... as sysadmin) with dbo permissions to this database. ...
    (microsoft.public.sqlserver.security)
  • Re: DTS in ASP
    ... Giving IIS_WPG permissions to the database (db_owner and sysadmin) ... (IWAM_MACHINE is member of this group) ...
    (microsoft.public.sqlserver.dts)
  • Re: List Users Permissions down to table.column action
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- GRANT USER ACCESS TO SERVER ROLES ...
    (microsoft.public.sqlserver.security)