hi,
Dimitrios said:
I have a adp that I want to protect. Because of my recent upsizing to
MSDE, I am kind of lost!
I got Andrea Montanari's Dbamgr2k to handle security issues but
still... In the logins node of my MSDE instance I have 2 users: sa and
BUILTIN\Administrators both pointing to the "master" database of MSDE.
In my db node my windows account is mentioned as owner.
In users node in the db node, only dbo with no login name exists.
When I choose to see the currently connected users, I see two
instances of my windows account.
What do I have to change to:
1. Make the security machine independent,
what do you mean by that?
2. Prevent all users but me to see and alter the BE
this is a little hard to accomplish... you can not prevent "sa" login to
access and manage the instance and all objects in it (dbs, tables, views,
....) ... you can disable mixed security in order to prevent SQL Server
stantard logins to log in (and this include "sa" login) and only allowing
trusted WinNT connections (see the
http://support.microsoft.com/default.aspx?scid=kb;en-us;285097 KB article in
the Windows registry "hacking" part, using the 1 value for the LoginMode
key, or, by using DbaMgr2k, accessing the server properties and, in the
security tab, set the Windows security option for the security mode)...
then you have all members of BUILTIN\Administrators (including your self)
that are granted login as member of the server sysadmins role... you can
deny login for the BUILTIN\Administrators (and grant yourself login via a
personal WinNT login) but you'll enter in problems regarding the account
running the SQL Server Agent (especially if you are running MSDE sp4 as
http://support.microsoft.com/default.aspx?scid=kb;en-us;283811&sd=tech KB
article has still not been updated for sp4)..
that's to say all members of sysadmin server role cna do whatever thy will
with the MSDE instance...
on the other hand, all other kinds of logins have to be mapped as database
users to be elegible for database uses, and you can define with the
granularity you want the actual permissions they have...
3. Where do I setup common users?
?
what do you mean by "common users"?
again, you have to grant all non admins logins acces to each user database
you want them to be reachable...
PS. I want to distribute the database and I want to make the BE as
secure as possible both from theft of data and DB design.
as regard the db design, in SQL Server 2000 you will not be able to protect
it as every database user will be able to "see" your data model metaschema
(and eventually script it out too) with the exception of procedures, views
and user defined functions you create with the WITH ENCRYPTION option...
(BTW, be aware that the encryption algorithm has been cracked)
for the remaining, please start having a look at
http://www.sql-server-performance.com/vk_sql_security.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply
