-- add data collection user

exec sp_addlogin N'datacollection', 'd^t^collection', 'master', 'British'
GO
if not exists (select * from dbo.sysusers where name = N'datacollection' and uid < 16382)
EXEC sp_grantdbaccess N'datacollection'
GO
exec sp_addrolemember N'db_owner', N'datacollection'
GO

--added user for redant update program
exec sp_addlogin N'RedAntUpdate', 'r3dant2008', 'master', 'British'
GO
if not exists (select * from dbo.sysusers where name = N'RedAntUpdate' and uid < 16382)
EXEC sp_grantdbaccess N'RedAntUpdate'
GO
exec sp_addrolemember N'db_owner', N'RedAntUpdate'
GO

CREATE procedure [dbo].[Count_Users] (@dbname as varchar(20)) as
declare	 @spidlow	int,
		 @spidhigh	int

select	@spidlow=0,
		@spidhigh=32767

select distinct(hostname),db_name(dbid) as dbname,loginame
from  master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh and db_name(dbid)=@dbname and hostname <> '' and loginame <> 'datacollection' and loginame <> 'sa'
go