question

Giuseppe avatar image
Giuseppe asked

Restrict access to sql server database

Hi to all, I am new in this group and use sql server 2008 r2. I work on a server where many databases are located, and a team uses windows authentication with admin rights on the whole server. I would like to know if I have chance to limit access to a new database on the same server and let access to it only by sql login, denying access to it by windows login. Thanks in advance
sql-server-2008-r2loginaccess
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The site runs on voting. Please indicate all helpful answers by clicking on the thumbs up symbols next to those answers. Please indicate any one answer that lead to the best solution by clicking on the check mark next to that answer.
0 Likes 0 ·
Dave_Green avatar image
Dave_Green answered
Generally speaking, there isn't a way to keep someone with admin rights on a server from specific databases. Especially if they have access to the files on the file system. You could look at [this post][1] which details the use of encryption to keep administrators from seeing the data - but I would suggest that you need to think a little about the problem. Administrators are in a position of trust. If you can't trust them with the access which they need to do their job, then they shouldn't have that access (or job). In this case, that may mean that you need to establish whether these are the correct people to administer the database, or whether it actually needs to be on a different / dedicated server, with different administrators. [1]: http://www.mssqltips.com/sqlservertip/2840/sql-server-encryption-to-block-dbas-data-access/
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you very much
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
The only way to disallow access to administrators of particular SQL Server Instance to some databases is, setup additional instance on the machine and move the database to that instance. Of course, if the SQL Server Admins are also Administrators of the Machine, then there is always chance, they can gain access also to the other instance.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.