Database security

[bbpitpit]

hi thr,

May I know how to create a secure database? I heard some of my seniors said about something that outsider unable to open and view our database table unless the user is the admin itself...instea d of setting password to our database, is there any other way to avoid our database to be viewed from other people?

p/s: Any databases but I prefer SQL Server 2000 for this question....thx !!

[iburyak]

Nobody can access database unless you grant them rights.
If you are sysadmin revoke all rights and privileges like security administrator or system administrator from all users and create, for example, read_only group (role) in each database. You can grant some rights to this group like select from some tables of views, execute from some reporting stored procedures. Any new user should be a member of this group. And you will not have to worry granting rights separately to each user.
If you have user_id and password that everyone knows. You just change a password and nobody will be able to login without your knowledge or permission.

To test my words:
1. create some login_id make it a member of read_only group.
2. login with this new UID and see what you’ll see or be able to do.


Hope it helps.
There is much more to it. But people write books on Server Security and it is not a place for it….. :)