Execute sp_lock in sql server 2005

By default users without sysadm rights can not execute sp_lock. Since this is often important to be able to do when trouble shooting we might need to grant this to the user:

You can view all permissions with this code:

SELECT * FROM sys.fn_builtin_permissions(default)
order by permission_name;


This is how to grant them to a user. In this case i needed to give the user view permissions on server state. This is ok since it just is view. We can not grant alter! This code will let the user dboim run sp_lock.

USE master;
GRANT VIEW server STATE TO dboim;
GO