| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-09-09 : 09:34:25
|
| Andrew writes "SQLTeam,I need to prevent certain System Administrators from using an extended store procedure. The procedure was developed in house and is related to encryption.Is there anyway to accomplish this?Windows 2000 sp2SQL Server Enterprise & Standard Edition 2000 sp2Thanks,Andrew" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-09 : 09:35:56
|
| As long as they are members of the sysadmin role, they will be able to execute it. If you are concerned about certain people misusing this procedure they don't sound like reliable sysadmin candidates. |
 |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2002-09-09 : 09:37:55
|
| Can you create a new role with restricted access? |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-09-09 : 15:03:53
|
| You cannot prevent a member of the sysadmin fixed server role from doing anything in SQL - they are the system admins, end of story. As rob says - if you don't trust your sysadmins then they shouldn't be sysadmmins.HTHJasper Smith |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2002-09-09 : 15:22:11
|
| We should not presume that this question is an issue of trust (or a lack of trust motivated from within an organization). Many large client organizations require that certain sensitive information (if not all) be restricted (eg. Passwords, SSNs or encryption/decryption algorythms)Many clients will not do business with companies that cannot provide assurance that these security type mechanisms are in place and that policies such as these are adhered to.I agree with the previous member comments (You should be confident in your team)but I should not presume that the question is related to potential internal conflicts. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-09 : 15:41:58
|
You're right, in a way, but it still doesn't address the point. Realistically, if there are people who shouldn't execute this extended procedure, then they shouldn't (and physically can't) have system administrator privileges. I think it IS safe to presume that Andrew either has reservations about someone's integrity, or he is trying to establish the proper security for the group's members.In any case, the user(s) must be taken out of sysadmin and added to serveradmin, processadmin, sercurityadmin, etc. or whatever other roles are needed to do their job(s). That's one of the larger issues in this question: what do these admins do, and what should they NOT be allowed to do? That's not just a SQL Server issue but also an organizational one.As you said here:quote:
Many clients will not do business with companies that cannot provide assurance that these security type mechanisms are in place and that policies such as these are adhered to.
It really doesn't matter what the reason is, the answer is the same: they cannot belong to sysadmin, and they probably shouldn't. |
|
|
|
empire
Starting Member
40 Posts |
Posted - 2002-09-09 : 16:13:21
|
| Maybe Andrews question is more like this one (and mine)If you have NT System Administrators, how do you block them from being SQL System Administrators using Windows Authentication?Thats my problem I have, I want to force certain Admins into SQL using SQL Server Id's?How would one do this?Programmers are simple devices, put caffeine in, and get code out. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-09 : 16:25:03
|
| You can add their Windows logins to the SQL Server and then set those logins to deny access to the server. That would then require them to use a SQL Server login to connect. |
|
|
|
empire
Starting Member
40 Posts |
Posted - 2002-09-09 : 17:27:34
|
| Nice, thanks for the tip!Hope that helps Andrew as well and that I didn't steal the threadProgrammers are simple devices, put caffeine in, and get code out. |
|
|
|
empire
Starting Member
40 Posts |
Posted - 2002-09-09 : 17:28:28
|
| Nice, thanks for the tip!Hope that helps Andrew as well and that I didn't steal the threadProgrammers are simple devices, put caffeine in, and get code out. |
|
|
|
ksw
Starting Member
24 Posts |
Posted - 2002-09-09 : 17:35:59
|
quote:
If you have NT System Administrators, how do you block them from being SQL System Administrators using Windows Authentication?
First of all, make sure that you have removed the login Builtin\Administrators. This is a Windows group that is put into SQL Server by default. Any admins on your network will belong to this group. Since the service account for SQL Server has to be there, and if your company is like mine, the SQL Server programs were installed by a domain admin account, then you have to use Enterprise Manager to lock unwanted people out. Change the EM connection properties to use a SQL Server account (we use SA and the network admins don't know the password) and to always prompt for the login info.Also, it is recommended that you don't have any Windows groups set up as a SQL Server login. Since the DBA's aren't necessarily the network admins, the DBA looses control over who can be a member of the group and what permissions they have. If someone is added to a Windows group, SQL Server has no knowledge of that.Hope that helps some,--KSW |
|
|
|
gwhiz
Yak Posting Veteran
78 Posts |
Posted - 2002-09-09 : 18:19:20
|
| I agree with ksw. The builtin\administrators group needs to have sysadmin access revoked. But make sure the you have added yourself as a sysadmin first.Second I disagree with ksw I handle all of my security through groups I have no interest in spending 2-3 hours a day updating users. Leave this to the NT groups. I also would not connect using an SA account unless I had to have the security set to mixed. Why open another security hole when you don't have to. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-09 : 19:18:58
|
quote:
I have no interest in spending 2-3 hours a day updating users. Leave this to the NT groups.
I agree with this as well. NT Groups are an excellent way to manage security on a SQL Server. This should not be a question of an existing, general/public NT Group being added to SQL Server. If any users should NOT have access to SQL Server, then a new group should be created for SQL Server and that group's membership be maintained by the network admins, even if it is 99% identical to another group. Naturally, any NT/SQL Server group admininstration should be communicated between both sides regularly. |
|
|
|
|
Restricting Sys Admin from using xp_