SQL Security related

[microsoft.public.dotnet.languages.vb]

We had been running SQL Server without any control of security (since
the company is very small -100 employees). All of us know the admin
password and has been accessing the database as admin. Our database
server crashed due to hardware failure twice last month and we lost a
lot of important data. Now the management is taking the control of
server access seriously.

SQL Enterprise manager is installed on many PCs and any one can delete
any database with a right click.

My question is:

1. Can the enterprise manager be installed on client's PC with a
limited right (or as a user not as admin)?
We need to limit the user's access of using the Enterprise Manager.
In other words, how can we set this up for different users.


2. How can we keep running SQL Server if one server fails?
Clustering or Replication or Mirroring? O


I would highly appreciate if you could direct me to any website or
resources on how to set up security of SQL Server (2000 with the latest
service pack).


Thanks a million in advance.


Best regards,

Mamun

[David Portas]

Re: SQL Security related

Enterprise Manager runs with whatever permissions are assigned to the
login specified when you register the server in EM on each machine.

If everyone is logging in as SA then you should make everyone
re-register their servers in EM using different logins - either SQL
Server or Integrated (domain) logins. Then change the SA password. I
suggest you use Integrated security if you can. Assign users to domain
groups according to what level of access they need, then assign roles
to those groups.

For high availability options see:

http://www.microsoft.com/sql/techinfo/administration/2000/availability.mspx

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: SQL Security related

microsoft.publi c.dotnet.langua ges.vb (mamun_ah@hotma il.com) writes:[color=blue]
> We had been running SQL Server without any control of security (since
> the company is very small -100 employees). All of us know the admin
> password and has been accessing the database as admin. Our database
> server crashed due to hardware failure twice last month and we lost a
> lot of important data.[/color]

Sounds as if you need to review your backup strategy.
[color=blue]
> 2. How can we keep running SQL Server if one server fails?
> Clustering or Replication or Mirroring? O[/color]

Clustering, log-shipping and replication are all possible to use for
high-availability solutions. Clustering is the most heavy-duty, but
also requires more money for hardware. And you need Enterprise Edition
of SQL Server.

Log shipping is leaner, but still requires Enterprise, if you want to do
it simple. (It's possible to do log shipping with Standard, but you need
to roll your own.)

Replication is definitely the low-budget solution. The main purpose of
replication is not to provide high availability, but it can be used for
this aim.

Some of the articles on

http://www.microsoft.com/technet/prodtechnol/sql/2000/technologies/dbengine.mspx

may be useful to you.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp