Wednesday, March 25, 2009

Change the owner of object in SQL Server 2000

UPDATE [dbname].dbo.sysobjects SET uid=6 WHERE uid = 1

To get user id:
select DISTINCT o.name, o.uid from dbo.sysusers o left join master.dbo.syslogins l on l.sid = o.sid where ((o.issqlrole != 1 and o.isapprole != 1 and o.status != 0) or (o.sid = 0x00) and o.hasdbaccess = 1)and o.isaliased != 1 order by o.name

If it throws the error 
'Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.'

Then change the configuration
exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
and then try again.

Find objects in database

To find perticular object in database:

SELECT name, owner= user_name(uid) FROM [dbname].dbo.sysobjects WHERE type= 'U' order by name 

Type:

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
V = View
X = Extended stored procedure

Grant objects permission to users in SQL Server 2000

GRANT
    { ALL [ PRIVILEGES ] |
permission [ ,...n ] }
    {
        [
( column [ ,...n ] ) ] ON { table | view }
        | ON {
table | view } [ ( column [ ,...n ] ) ]
        | ON {
stored_procedure | extended_procedure }
        | ON {
user_defined_function }
    }
TO user
 [ ,...n

Arguments:

ALL

Specifies that all applicable permissions are being granted

permission

Is an object permission that is being granted.  

For table, table-valued function, or a view: 

SELECT, INSERT, DELETE, REFERENCES, or UPDATE. A column-list can be supplied along with SELECT and UPDATE permissions. If a column-list is not supplied with SELECT and UPDATE permissions, then the permission applies to all the columns in the table, view, or table-valued function.

For stored procedure: EXECUTE. 

column

Is the name of a column in the current database for which permissions are being granted.

table

Is the name of the table in the current database for which permissions are being granted.

view

Is the name of the view in the current database for which permissions are being granted.

stored_procedure

Is the name of the stored procedure in the current database for which permissions are being granted.

extended_procedure

Is the name of the extended stored procedure for which permissions are being granted.

user_defined_function

Is the name of the user-defined function for which permissions are being granted.

user

Is name of user whom permission should be granted. 

Example:

To grant a permission to table employee in database Emp to user hitesh :

GRANT ALL ON [emp].[employee] TO [hitesh]


Sunday, March 15, 2009

Reuse the object using Object Pool

There are some requirement that we need to instantiate the object again and again. New object always takes some resource (in memory). Our normal practice is, create a object and dispose (it happens automatically when go out of scope) it once no longer required. It is ok if number of objects creation are not more. 

But there are some situations where we can hold the object instead of disposing it and reuse it again. 

Hence, I have created generic object pool, which tries to get the object from pool. If pool is empty it creates new and return. One can push object back to pool once no longer in use. 

Create generic object pool class
public class ObjectPool where T : class, new()
{
Stack queue;

public ObjectPool()
{
queue = new Stack();
}

public ObjectPool(int capacity)
{
queue = new Stack(capacity);
}

To pop the object from pool. It creates new object if couldn't found from pool 
public T Pop()
{
T retObj = null;
lock (queue)
{
if (queue.Count > 0)
retObj = queue.Pop();
}

if (retObj == null)
retObj = new T();
return retObj;
}

To push back the object into pool for reuse
public void Push(T obj)
{
if (obj == null) return;

lock (queue)
{
queue.Push(obj);
}
}

To get the number of objects in pool

public int Length
{
get
{ return queue.Count; }
}
}

Friday, March 13, 2009

SQL Server 2005: Shrink and Truncate Log file size

USE
GO
DBCC SHRINKFILE(<Logical Name of Transaction Log>, 1)
BACKUP<Database Name> LOG WITH TRUNCATE_ONLY

1 (second parameter of DBCC SHRINKFILE) is target size of file in megabytes.

It won’t take backup of log file. Hence log will be lost. Make sure to take backup if you really want log.