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
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] 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. :
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.