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
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.
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/
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.
To Limit User Access to a Specific Database
- Step 1: Create a Matter.
- Step 2: Associate the Database with the Matter.
- Step 3: Create a User Group.
- Step 4: Assign Users to the User Group.
- Step 5: Associate the User Group with the Matter.
- Step 6: Remove the Database from the "All user Groups" Matter.
Regards,
Will
18 People are following this question.
