Security in SQL Server.

At once, security was the least feature I checked on SQL Server. In our office environment, the database is always on the backend, no direct access is allowed to the server. the appliation server is behind the fiewall, and the database server is always accessed through stored procedures. I had a few dynamic sql in stored procedures, however, they too are really secure (or I felt so). Most of the time, the security threat comes from inside. Some are dure to the limitations in SQL Server 2000.

• dbo rights are given to developpers so that they can create objects in DBo schema;
• sa rights are given to developpers so that they can use profiler to to debug a stored procedure.

The first issue comes as a by product of how schema defined in SQL Server 2000; It follows a simple formula of schema = user. To avoid users from creating tables in their user schema (and move to another schema when they leave the company) DBAs encouraged all to use DBO schema. That gives full access to developpers even to drop existing objects.

SQL 2005 handles it very well by seperating users from schema. User may use a default schema. However, schema is simply a logical collection of objects.

Second scenario is even serious. To run profiler, a user should be able to have sa rights. A person with sa right can do anything. including removing all other users (this includes the actual "DBA" as well) BANG! Currently I was avoiding this by running a profiler against the "profile user" to identify whats going on!

SQLS erver has solved this issue by allowing explicit access to run profiler.

Let me explore and share more security features later.

How DBCC CHECKIDENT behaves?

DBCC CHECKIDENT is a handy command to reset the value of the identity fields. However, they behave slightly differently at different situations

What will be the outcome of the following command:
DBCC CHECKIDENT ('Table_a', reseed, 100) . Is it hundred?

Well, it depends. it depends on whether the table is brand new (vergin?) or got some data already. If the table ever had data, even if you truncate table, the identity will start from next value.
That is, if the table even had data the next identity value will be 101 and not 100.
Try yourself. If anyone gets a different value, check your code :)