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 |
|
d.docouto
Starting Member
5 Posts |
 Posted - 2005-07-07 : 15:26:03
|
I am currently looking to deploy an application using MSDE. For the users that have SQL 2000, I am going to have them follow the basic install to create a new SQL instance. Then changing the SQL "SA" password manually. Basically, my main concern is that I don't want users being able to access the SQL databases. Once I change the "SA" password, I am left with 1 more problem. The user who installs SQL has a login into the SQL instance. I know you can remove this user manually. However, is there a way to remove all users automatically other then the "SA" user?Does anyone else have experience deploying applications using MSDE or SQL 2000? Any help is much appreciated.  |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-07-07 : 15:52:53
|
| So you don't want anyone to have access to the SQL Server after the install is done? If so, then you'll need to remove the BUILTIN\Administrators group from the sysadmin fixed server role. Otherwise, local admins will have sysadmin access. If you change the sa password and remove the group mentioned above, then who is going to be responsible for backups, integrity checks, normal SQL health, etc...? Someone needs to manage it at the customer site unless the contract that you have with your customer is that your company will do daily maintenance plus any troubleshooting/assistance needed.Tara |
 |
|
|
d.docouto
Starting Member
5 Posts |
Posted - 2005-07-07 : 15:57:35
|
| Tara,That's exactly what I need to do. Our application is going to use SQL as a backend database. However, we don't want users have the ability to go into the SQL and attach other databases to our instance and make there own changes. Could be very bad news. Bascially, I need to know now to remove all users except for SA from the SQL instance. This way, they can't create there own connection to the database. All other mainteance will be done directly through the program.Hopefully that made sense. Thanks for the help! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-07-07 : 16:02:59
|
Check out sp_revokelogin and sp_droplogin in SQL Server Books Online. You'll also want to check out sp_droprolemember to do the BUILTIN\Administrators group thing mentioned in my previous post.quote:
All other mainteance will be done directly through the program.
As a DBA, I would never use someone else's program to do DBA maintenance. For large corporations such as the one that I work for, we would not be able to remove our access. We would need to setup backup jobs and normal DBA routines such as DBCC CHECKDB. The DBA would check the overall health of the SQL Server each day. All of this would be done via Enterprise Manager, Query Analyzer, or osql.exe and not through a third party product.So if your program can handle DBCC CHECKDB, which is a command that needs to be run daily to check the integrity of the databases, who is going to check the output of the command each day to verify that no corruption exists?Tara |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-07-07 : 21:25:13
|
| I don't think you can really lock your customers out if they know what they are doing. I don't think you could keep me out if I was a local admin on the server where SQL Server was running.I don't know who your customers are, but I would never allow a database server in our company that we did not have access to. What happens if your company goes out of business? How would I know that you are really performing backups? How would I know that you are performing other basic maintenance operations, monitoring space usage, etc? How do you intend to perform tape backups for them?CODO ERGO SUM |
|
|
|
|
|
|
|
|
HELP ! SQL 2000/MSDE Database Distribution