I came across a situation where I had to find all the stored procedures or user defined functions that do not have “execute” permission under a particular user. Here is a simple SQL Statement one which can be used under SQL Server 2005 to find those objects.

 Note: Make sure that you have logged in as a most privileged user through a Query Analyzer window and then replace the  ‘user_name’ for which you want to check permission. For example, following code will show all the procedures which do not have Execute permission for 'user_name'.

Declare @ObjName Varchar(100)

Create table #TempTable1 (Entity_Name varchar(100), Permission_Name varchar(25))

Create table #TempTable2 (Entity_Name varchar(100), Permission_Name varchar(25))

 

INSERT #TempTable1(Entity_Name)

SELECT Name FROM sys.objects

WHERE type='P'

ORDER by Name

 

EXECUTE AS USER = 'user_name';

 

Declare TableList cursor LOCAL FAST_FORWARD FOR     

SELECT Entity_Name FROM #TempTable1

ORDER by Entity_Name

OPEN TableList     

     

FETCH NEXT FROM TableList INTO @ObjName         

WHILE @@fetch_status = 0        

 BEGIN   

        INSERT INTO #TempTable2  

  SELECT Entity_Name,Permission_Name 

  FROM fn_my_permissions(@ObjName, 'OBJECT')

        FETCH NEXT FROM TableList INTO @ObjName   

 End

 

CLOSE TableList

DEALLOCATE TableList

 

SELECT Entity_Name FROM #TempTable1

WHERE Entity_Name NOT IN (SELECT Entity_Name FROM #TempTable2);

 

Drop table #TempTable1;

Drop table #TempTable2;

432c598d-4a6b-409c-a146-713649a0b64b|2|3.5

You might have come across situation when you wanted to log SQL Connections and Locks for a particular day or period. In the following example, by using two tables and one stored procedure, you can log these activities and analyze at a later time. This stored procedure can be turned into a SQL Job for any given interval so that you do not have to manually run it.

Step 1 : Create following 2 tables.

 If Exists(SELECT * FROM Sysobjects WHERE name='tbl_Who2_Log'AND Type='U')

 Drop Table tbl_Who2_Log

Go

 

CREATE TABLE [dbo].[tbl_Who2_Log](

            [RID] [bigint] IDENTITY(1,1) NOT NULL,

            [SPID] [bigint] NOT NULL,

            [Status] [varchar](100) NULL,

            [Login] [varchar](100) NULL,

            [HostName] [varchar](200) NULL,

            [BlkBy] [varchar](50) NULL,

            [DBName] [varchar](50) NULL,

            [Command] [varchar](200) NULL,

            [CPUTime] [bigint] NULL,

            [DiskIO] [bigint] NULL,

            [LastBatch] [varchar](20) NULL,

            [ProgramName] [varchar](200) NULL,

            [SSPID] [bigint] NULL,

            [LogDate] [datetime] NOT NULL

           CONSTRAINT [DF_tbl_Who2_Log_LogDate] DEFAULT (getdate())         

) ON [PRIMARY]

 

GO

 

ALTER TABLE dbo.tbl_Who2_Log

       ADD CONSTRAINT PK_tbl_Who2_Log PRIMARY KEY ([LogDate] ASC,[RID] ASC)

Go

 

 

If Exists(SELECT * FROM Sysobjects WHERE name='tbl_Lock_Log'AND Type='U')

 Drop Table tbl_Lock_Log

Go

 

CREATE TABLE [dbo].[tbl_Lock_Log](

            [RID] [bigint] IDENTITY(1,1) NOT NULL,

            [SPID] [bigint] NOT NULL,

            [DBID] [bigint] NULL,

            [ObjId] [bigint] NULL,

            [IndId] [bigint] NULL,

            [Type] [varchar](25) NULL,

            [Resource] [varchar](25) NULL,

            [Mode] [varchar](25) NULL,

            [Status] [varchar](25) NULL,

            [LogDate] [datetime] NOT NULL

           CONSTRAINT [DF_tbl_Lock_Log_LogDate] DEFAULT (getdate())

) ON [PRIMARY]

 

GO

 

ALTER TABLE dbo.[tbl_Lock_Log]

       ADD CONSTRAINT PK_tbl_Lock_Log PRIMARY KEY ([LogDate] ASC,[RID] ASC)

Go

 

Step 2:  Create following Stored Procedure

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].Usp_Log_WhoLock')

                                                     and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure Usp_Log_WhoLock

GO

 

CREATE Procedure dbo.Usp_Log_WhoLock

As

 

/*****************************

* Procedure:        Usp_Log_WhoLock

* PURPOSE:          Logs SQL Connection and Locks

* NOTES:    

* CREATED:      Vishwa(06/24/2006)

*

* MODIFIED  

* DATE                AUTHOR                 DESCRIPTION

*-------------------------------------------------------------------------------

* {date}                {developer}        {brief modification description}

*

*                                              

************************************/ 

SET NOCOUNT ON

Declare @ConnectionCount Int, @LockCount Int

Declare @CurrentTime DateTime

SET @CurrentTime= GetDate()

 

-----FOR SQL Server 2000 based -----------------------

INSERT Into tbl_Who2_Log(spid, Status, Login, HostName, BlkBy, DBName,

                        Command, CPUTime,DiskIO,LastBatch,ProgramName,SSPID)

EXEC dbo.sp_who2

 

/***FOR SQL Server 2005 based ------------------------

 INSERT Into tbl_Who2_Log(spid, Status, Login, HostName, BlkBy, DBName,
                        Command, CPUTime,DiskIO,LastBatch,ProgramName,SSPID)
SELECT spid, Status, Loginame, HostName, Blocked, dbid,
        cmd, CPU,physical_IO,Last_Batch,Program_Name,hostprocess
FROM master.dbo.sysprocesses

*******************/

 

INSERT tbl_Lock_Log (spid,dbid,ObjId,IndId,Type, Resource,Mode,Status)

EXEC dbo.sp_lock

 

 

SELECT @ConnectionCount = COUNT(SPID)

FROM tbl_Who2_Log (NOLOCK)

WHERE DateDiff(Minute,LogDate,@CurrentTime)=0

 

 

SELECT @LockCount = COUNT(SPID)

FROM tbl_Lock_Log (NOLOCK)

WHERE DateDiff(Minute,LogDate,@CurrentTime)=0

 

 

PRINT 'At ' + Convert(VarChar(20),@CurrentTime)

                        + ' Total Connections Count: ' +

                        Convert(VarChar,@ConnectionCount) +

                        ' and Total Locks Count: ' + Convert(VarChar,@LockCount)

 

 

SET NOCOUNT OFF

Step 3: Turn the above Stored Procedure in to a SQL Job.

NOTE: You will need to clean up the logged transactions in above table on frequent basis otherwise these tables will start occupying too much space.

d4f32724-f906-429a-b643-d94eb73071d1|1|5.0

At some point you might need to know, how many tables you have in database, number of rows in each table, size of table and indices. Following is a simple SQL Script which can provide you a snapshot of all such details.

---

 Declare @TabName Varchar(100)

Create table #TempTable (Name varchar(100), Rows int,Reserved varchar(25),Data varchar(25),Index_size varchar(25),Unused varchar(25))

 

Declare TableList cursor LOCAL FAST_FORWARD FOR     

SELECT Name FROM sysobjects

WHERE xtype='U'

ORDER by Name

OPEN TableList     

     

FETCH NEXT FROM TableList INTO @TabName         

WHILE @@fetch_status = 0        

 BEGIN   

        INSERT INTO #TempTable Exec sp_spaceused @TabName

        FETCH NEXT FROM TableList INTO @TabName   

 End

CLOSE TableList
DEALLOCATE TableList

SELECT * FROM #TempTable

076dc02d-5842-42ab-a189-a830ff594b90|2|4.0