That is DBA having all DB administration rights but cannot view user data.

...That is DBA having all DB administration rights but cannot view user data....

Hello Rishi,

as others have pointed out, if "all administration rights" means they indeed are members of the sysadmin server role, there is no chance.

But lets assume you are open and able to change that fact (which is why you are here I hope :). Then things become different.

You could work out a custom server role and respective SQL Agent role, to allow just the necessary tasks, and deny all others.

Especially when you are under SQL Server 2014, you can use the "DENY SELECT ALL USER SECURABLES".

Whilst this is not 100% secure - there are some ways to escape from such a "sandboxed" server role - it is not so obvious and easy to trick it without leaving traces. - So make sure you also have auditing set up in place and preventing access to the audit log files for the very same administrators.

I would also recommend you this article of mine as a starting point, to get an idea about the complexity of limiting permissions for administrators: New Permissions in SQL Server 2014: IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER

Yes, you can restrict the DBA with sysadmin permissions from viewing data. See this:  http://www.mssqltips.com/sqlservertip/2840/sql-server-encryption-to-block-dbas-data-a

Thanks Andreas for your advice but unfortunately the sql server version are 2005-2008 and I believe custom server roles are possible only in sql 2012 and above. 

column level encryption is a very complex and not feasible option as it all depends  destination colums which is varbinary and table in different databases and on multiple instances and very much sure this is going to effect application as well.

Thanks Andreas for your advice but unfortunately the sql server version are 2005-2008 and I believe custom server roles are possible only in sql 2012 and above. 

That is correct. Unfortunately that information was missing.

SQL Server 2005-2008 are out of mainstream support for a reason. Too bad. Then you will have a hard time creating customized Logins. Not impossible but even more tricky and in the end most often not practical. Starting with SQL Server 2008 you at least have auditing. in 2005 you only have SQL Trace...

I can only advise to get an upgrade to the current version rolling. Good Luck.

Hi Specialists,

                   My Client does not want DBA to view user tables/Data in Sql server database on every instances  but can do all sql administration task like Backup/Restore,Database maintenance ,SqlAgent Jobs,Index maintenance,sql Cluster administartion.That is DBA having all DB administration rights but cannot view user data.

Let us know how this can be reached.

Regards

Rishi

My Client does not want DBA to view user tables/Data

Hello Rishi,

This is absolutely not possible, you can not restrict members of the SysAdmin role in any way. You would have to remove the user from SysAdmin role and grant otherwise permissions; but at lease one user must have SysAdmin permission.

Sorry this is not completely possible.

You will have access to all tables so long as you're the db owner. AFter all you should trust somebody. If DBAs want to their normal activties they would definitely require db owner level access which will also include access to these tables too.

What you can do alternatively is to have audit mechanism to audit who is accessing the sensitive tables if you want.