Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bambola
Posting Yak Master
103 Posts |
 Posted - 2003-01-29 : 06:40:37
|
Here is the scenario:we create my_user on our server.added him to my_database.grant execute on my_sproc to my_userdeny select on sysobjects to my_user now when I connect with QA to my_server using my_user, I can execute my_sproc, but I can also see all the objects undermaster msdb and tempdb. see = see sprocs code, table content etc.is there a way to avoid that without having to add my_user to any of those databases? (What I need to do is to allow our client to call a sproc on our server from a sproc on his server. both SQL Server 2000. )Thanks!  Bambola. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-29 : 08:37:09
|
Well, as was pointed out in the earlier thread:quote:
I don't think you can prevent it ... enterprise manager (em) shouldn't be your main route of accessing the database for that user
If the users are using EM then you're out of luck. If all they have to do is access data, you can set up another means to browse data (MS Access linked tables, data browser app, web browser app) and take Enterprise Manager away. If they need to administer the database as well, there are some web-based apps available that can do that too, and you might be able to customize them to limit access as you wish. But as long as EM is involved, you can't hide objects you don't want the user to see.And before someone else chimes in with it, you can hide the sproc code by using WITH ENCRYPTION, but DO NOT ENCRYPT A PROCEDURE UNLESS you are using source control to maintain the source code. Otherwise you WILL accidentally encrypt a sproc and not be able to retrieve the code later. I cannot emphasize this enough: DO NOT ENCRYPT IF YOU ARE NOT USING SOURCE CONTROL. |
 |
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-01-29 : 08:59:31
|
| WITH ENCRYPTION doesn't sound like a good idea to me. There are ways to decrypt those sprocs. As for our client, he has to be able to execute a call to a sproc on our server, from within his sproc. He has a login with permission to do only this. Aparrently he can see all sprocs tables on master etc. I can DENY select on master..sysobjects. He would not see a thing and everything will work ok. But to do that I would need to add the user to the master database and I don't want to do that. I was hoping to find a better solution. Bambola. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-29 : 09:07:35
|
| Why not create a linked server on the client's server that points to yours? The client can then call:EXECUTE LinkedServer.MyDatabase.dbo.MyStoredProcedureHe would not have to connect to your server directly, and the linked server can include a login mapping that provides only the permissions he needs. He might be able to browse tables, but he would not be able to access procedure code. You may also be able to use a remote server link to your server and configure RPC on it, but linked servers are usually more flexible.By the way, is the client USING Enterprise Manager? You haven't said either way, and I just want to know for certain. And also, why do they even HAVE permission to master, msdb, and so on? If they need to execute a non-system procedure, put it in a database other than master. |
|
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-01-29 : 09:23:27
|
| They are using EM. And I did not add this user to the master database, yet it still allows me to view the objects.Could you please try it to see what am I talking about? - create your_user - add your_user to a your_database - DENY your_user SELECT on sysobjects.- connect to your server with that user from QA. and open the object browser.You will not be able to see sprocs/tables/etc under your_database. and this is what I wanted. But under master/msdb/tempdb you would, and I certainly wouldn't want that.Is it more clear now?Bambola. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-29 : 10:01:10
|
| Add the user to master, then run:EXEC sp_addrolemember 'db_denydatareader', 'user'EXEC sp_addrolemember 'db_denydatawriter', 'user'This will prevent them from reading and writing data, and they will not be able to select anything from the object browser (they may still see the database, but cannot drill down through the folders) You would repeat this process for msdb and any other databases you want to restrict for that user.Also look in Books Online under "roles" for more info, there are other roles available that can help you secure the database further. |
|
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-01-29 : 11:28:47
|
| This is what I was trying to avoid, adding user to the master database. Was hoping to find another way.So far I learned that the problem is with "guest", and that I can remove it from all databases but master (or no one but sa would be able to login). Thanks anyway!Bambola.Bambola. |
|
|
|
|
|
|
|
|
Pemission problem - Take 2.