We have a SQL Server database that will be deployed to client servers. The database contains a number of schemas, some of which we want only the application to access. Is it possible to deny access to all users (even sa) except a user we create to those schemas?
我们有一个将部署到客户端服务器的SQL Server数据库。该数据库包含许多模式,其中一些我们只希望应用程序访问。除了我们为这些模式创建的用户之外,是否可以拒绝所有用户(甚至是sa)的访问权限?
1 个解决方案
#1
0
You cannot remove the SA out of the permissions. Every person who is SA can see and work with your data. It doesn't count which schema it is in. Another option for an SA is the usage of DBCC, which will help him avoid some principles (like the one that a temporary table is only visible to the session which generated it).
您无法从权限中删除SA。 SA的每个人都可以查看和处理您的数据。它不计算它所在的模式.SA的另一个选择是使用DBCC,这将帮助他避免一些原则(比如临时表仅对生成它的会话可见)。
If you trust a person to be an SA, you really should trust him to access all of your data.
如果你相信一个人是一个SA,你真的应该相信他可以访问你的所有数据。
The only way to avoid this, is to wait for SQL Server 2016. It will allow you to store every data in your database encrypted. Even the SQL Server itself can't access all information of it. The decryption/encryption is done by the application. SQL Server self don't know the key. The new features will be called always encrypted.
避免这种情况的唯一方法是等待SQL Server 2016.它将允许您加密数据库中的每个数据。即使SQL Server本身也无法访问它的所有信息。解密/加密由应用程序完成。 SQL Server自己不知道密钥。新功能将始终加密。
Anyway, you can do such a scenario on your own with nearly all SQL Server variants, but it would be a bit more work as in SQL Server 2016.
无论如何,您可以使用几乎所有SQL Server变体自行完成这样的场景,但是在SQL Server 2016中会有更多的工作。
#1
0
You cannot remove the SA out of the permissions. Every person who is SA can see and work with your data. It doesn't count which schema it is in. Another option for an SA is the usage of DBCC, which will help him avoid some principles (like the one that a temporary table is only visible to the session which generated it).
您无法从权限中删除SA。 SA的每个人都可以查看和处理您的数据。它不计算它所在的模式.SA的另一个选择是使用DBCC,这将帮助他避免一些原则(比如临时表仅对生成它的会话可见)。
If you trust a person to be an SA, you really should trust him to access all of your data.
如果你相信一个人是一个SA,你真的应该相信他可以访问你的所有数据。
The only way to avoid this, is to wait for SQL Server 2016. It will allow you to store every data in your database encrypted. Even the SQL Server itself can't access all information of it. The decryption/encryption is done by the application. SQL Server self don't know the key. The new features will be called always encrypted.
避免这种情况的唯一方法是等待SQL Server 2016.它将允许您加密数据库中的每个数据。即使SQL Server本身也无法访问它的所有信息。解密/加密由应用程序完成。 SQL Server自己不知道密钥。新功能将始终加密。
Anyway, you can do such a scenario on your own with nearly all SQL Server variants, but it would be a bit more work as in SQL Server 2016.
无论如何,您可以使用几乎所有SQL Server变体自行完成这样的场景,但是在SQL Server 2016中会有更多的工作。