Author |
Topic |
doristan
Starting Member
12 Posts |
Posted - 2006-07-06 : 03:11:19
|
Hi; May i know is there a way to prevent user to connect to my DB using SQL Enterprise Manager when this user login to my server using his profile? EG: i m administrator on my SQLSERVER, i can connect to my DB using SQL Enterprise Manager. But when User1 login to my SQLSERVER using his profile, he shouldn't able to connect to my DB using Enterprise Manager. Is there a way to set this? thanks :)Doris |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-06 : 03:14:18
|
as long as you did not grant the access of your DB to User1, User1 will not be able to access to your DB. KH |
|
|
doristan
Starting Member
12 Posts |
Posted - 2006-07-06 : 03:23:45
|
i have set the password to my EM, because i have many SQLSERVERs to maintain, so i m looking for a way that: if my server is login by other profile, he is not allowed to access to my DB, is it possible? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-06 : 03:31:26
|
You can't un saved the password in the EM. Edit the Server registration and change to always prompt for login name & password.Are you using Windows Authentication or Mixed ? KH |
|
|
doristan
Starting Member
12 Posts |
Posted - 2006-07-06 : 03:50:36
|
I m using Mixed.i know there is a setting to prompt for login name and password, but my intend is not to do that, my intend is to control by the window profile and not by using the account in the EM. Is there a way to do that? please? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-06 : 03:54:13
|
registered servers in one profile are not available in other profiles. I just did some testing where i registered a server using one profile and it definitely does not show up when I login as a different user.Are you seeing something different?-e |
|
|
doristan
Starting Member
12 Posts |
Posted - 2006-07-06 : 03:58:52
|
Yes, when my other user login using his profile, he manages to open EM and connect to my DB. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-06 : 04:02:17
|
Try changing your EM server registration to Windows Authentication and deny access to User1 KH |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-06 : 04:17:27
|
quote: Originally posted by doristan Yes, when my other user login using his profile, he manages to open EM and connect to my DB.
interesting. It looks like enterprise manager can keep registration information in both HKLM and HKCU. The stuff in HKLM is shared between all profiles.I found a script here http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=270935 that allows you to backup/restore EM registrations. It takes the registrations from HKLM and puts them in HKCU instead, eliminating the security risk.looking around in EM in the tools menu there is a setting for storing EM registrations as 'user independent'. Do you have that checked?EM --> Tools --> Options --> General Tab-ec |
|
|
doristan
Starting Member
12 Posts |
Posted - 2006-07-06 : 05:00:21
|
hi eyechart: May i know what actually this script for? must i run this script before i uncheck the 'user independent'? Does this means if i unchecked the 'user independent', my other user will not able to connect to my DB when he login using his profile?Thanks alot. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-06 : 05:25:45
|
is the builtin/administrator still existing on the server?if yes, then whatever profile he uses, as long as he is local admin to the server, he can access the entire sql server with SAis he member of sysadmin? if yes, then you cannot do anything, else you can remove his account from your databaseand make sure that the guest account doesn't exist either in your dbHTH--------------------keeping it simple... |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-07-06 : 11:26:00
|
quote: Originally posted by doristan hi eyechart: May i know what actually this script for? must i run this script before i uncheck the 'user independent'? Does this means if i unchecked the 'user independent', my other user will not able to connect to my DB when he login using his profile?Thanks alot.
I didn't write the script. The script seems to be able to cleanup the registry the way you want so your SQL registrations (including logins) are not exposed to other profiles who login to your server.If you have questions about the use of that script, read the thread I linked to and ask a question there.bottom line on this one is that in order to achieve what you want, you need to clean the registered servers out of the HKLM area so they are not shared between profiles. You then must enable the "user independent" feature of EM so future SQL Registrations do not wind up in HKLM.-ec |
|
|
|