how to secure sql server database 2

[MarQ]

I am new to SQL 2000, i dont know how to protect my database, is it the database or the enterprise manager? I have seen that there are permissions to be set in each table, however what i want is that the database or the enterprise manager should not be opened directly unless its me..can anyone help me secure my database? thanks

 

[Jasen]

So long as you have the SA (and any other logins) set with difficult passwords, no one can open your database, even if they open enterprise manager. Unless they get onto a PC you're already logged into and run your enterprise manager.
SQL server requires a login/pass to connect and do anything.

________
Remember, you're unique... just like everyone else.

 

[MarQ]

thats the big problem, because i have put SQL2k into a computer in which where i have also installed ny program. that means whoever uses the PC can possibly use the enterprise manager since it is already logged on..am i thinking correctly?..i just can not understand well sql2k..what to do

 

[Jasen]

Do they not log onto the PC with different accounts? Even if it's installed, enterprise manager won't show your server or SQL login details unless it's been setup in that user profile.
You could go as far as to even set the file permissions on the .exe to deny execute for their accounts.

________
Remember, you're unique... just like everyone else.

 

[mrdenny]

Setting permissions on the exe won't work for Enterprise Manager. EM is an MMC snapin.

When setting up your SQL Server ensure that only people that you want to be admins on the SQL Server are members of the sysadmin builtin server role. This includes removing the sysadmin rights to the BUILTIN\Administrators group. Be sure to specifically add your self to the sysadmin's list before revoking the sysadmin rights to the BUILTIN\Administrators group.

You'll also want to be sure to set a Long and Strong password for the sa account. Setup a seperate login for your application to use. Ensure that this account also has a long strong password which is different from the sa password.

Only grant the application account the minimum amount of permissions that it needs in order to function within the guidelines set forth by the applications. For example, if the app never updates a table the application's login should not have update rights to that table.

Ideally all DML should be executed via stored procedures for the maximum amount of security. This way you only grant the rights to execute the stored procedures, and you revoke the rights on the tables them selves. This forces the application to use the stored procedures, and prevents someone who has broken into the server from being able to access the tables.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)