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 |
|
bogey
Posting Yak Master
166 Posts |
 Posted - 2004-05-18 : 14:11:30
|
| Is it possible to deny NT Administrators access to sql server and its data? I've got a junior admin who is creating tables and programs and I'm afraid of him possibly screwing things up e.g. Database locking and private information that should not be seenThansk. |
|
|
jharwood
Starting Member
41 Posts |
Posted - 2004-05-18 : 16:08:50
|
| In Enterprise Manager remove sa access from the builtin\administrators login. |
 |
|
|
jharwood
Starting Member
41 Posts |
Posted - 2004-05-18 : 16:12:18
|
| Keep in mind though, if you have the sqlserver services running under local system, it may cause problems. If you have the services running under a domain account with it's own perissions, then removing sa access to the builtin\admins should work. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-18 : 16:16:03
|
| Do you want to make sure that nt admins don't get sysadmin rights when they log in?You need to remove the BUILTINAdministrators group.Make sure you don't lose all sysadmin access though. Think about creating another group with sysadmin access into which you put dba's.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-18 : 18:49:36
|
| I prefer the setup Nigel just said. Setup an NT Group (DB-Admin for example) and grant them the sysadmin access. Make them the dbo on each database. Delete the BUILTIN\Administrators group from SQL Server. SQL Server shouldn't be running under LOCAL SYSTEM anyway so that should definitely be fixed. Now, to address another point:How is your admin going to create tables and programs without access? I'm assuming you have a development server? Is he an admin or a developer? If he's a developer, he shouldn't be an Administrator anyway.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-05-19 : 09:02:05
|
quote:
Originally posted by derrickleggett
I prefer the setup Nigel just said. Setup an NT Group (DB-Admin for example) and grant them the sysadmin access. Make them the dbo on each database. Delete the BUILTIN\Administrators group from SQL Server. SQL Server shouldn't be running under LOCAL SYSTEM anyway so that should definitely be fixed. Now, to address another point:How is your admin going to create tables and programs without access? I'm assuming you have a development server? Is he an admin or a developer? If he's a developer, he shouldn't be an Administrator anyway.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
I'm not sure that setting up another group is going to do the trick...if the guy is an NT admin (and a local admin) he could always just add himself as part of the group, and you're back to where you started.Restrict the access to particular logins. |
|
|
|
bogey
Posting Yak Master
166 Posts |
Posted - 2004-05-19 : 09:05:08
|
| So whats the verdict then or what does Microsoft say? I'm gonna be moving my server to a dedicated machine in the comming weeks and would like to know how to keep those prying eyes away from sensitive dataThanks. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-19 : 09:16:03
|
| If you have people set up as domain admins, then you will need to create a new domain just for your SQL Servers. Only the DBA would the administrators of this domain. You then need to restrict your full administrator group and only have people as domain administrators.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Keeping NT Admins out of Server