hello,
We wrote an erp,and provide a platform to participator to extend my erp system,so I will give my participator database dictionary,but I only want to give partial database dictionary,I will hide some table and some field, I want they cann't open the database thouth sql server management studio or other tools,only can using our interface to access database,how can I do
-- I use sql server 2005
Sadly this is not possible. If a user or login has access to the database through your application it will have access through any other tool as well because SQL Server does not distinguish between one application or another.
You could try encrypting the contents of tables and fields. Check BOL to see if this might work for a solution.
HTH,
-Steven Gott
S/DET
SQL Server
hello,
Thank you for your response. Canging a way,if I can using one instance for my database,and set sa password only we know,give an account only for programing, furthermore, if sql server can support a type of private special database,like microsoft office ACCESS, this database connection is private,I will give orm dll,and connection hide in dll, can I do this
Thank you again.
Steven Gott - MS wrote:
Sadly this is not possible. If a user or login has access to the database through your application it will have access through any other tool as well because SQL Server does not distinguish between one application or another.
You could try encrypting the contents of tables and fields. Check BOL to see if this might work for a solution.
I want they cann't open the database thouth sql server management studio or other tools,only can using our interface to access database,how can I do
I believe that using the application roles it is possible to restrict users only to, well, using an application accessing to db without giving any other access to database or server
Here is excerpt from SQL Server 2005 Books Online (Application Roles, various topics)
"An application role is a database principal that enables an application to run with its own, user-like privileges. You can use application roles to allow access to specific data to only those users that connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles work with both authentication modes, and are activated by using sp_setapprole and require a password.
Connecting with an Application RoleThe following steps make up the process by which an application role switches security contexts:
- A user executes a client application.
- The client application connects to an instance of SQL Server as the user.
- The application then executes the sp_setapprole stored procedure with a password known only to the application.
- If the appliction role name and password are valid, the application role is activated.
- At this point the connection loses the permissions of the user and assumes the permissions of the application role.
The permissions acquired through the application role remain in effect for the duration of the connection.
In earlier versions of SQL Server, the only way for a user to reacquire its original security context after activating an application role is to disconnect and reconnect to SQL Server. In SQL Server 2005, sp_setapprole has a new option that creates a cookie that contains context information before the application role is activated. The cookie can be used by sp_unsetapprole to revert the session to its original context. For information about this new option and an example, see sp_setapprole (Transact-SQL)."