sp_who2 to sp_whoDB

http://sqlserverinternals.blogspot.com/search?updated-min=2006-01-01T00%3A00%3A00-08%3A00&updated-max=2007-01-01T00%3A00%3A00-08%3A00&max-results=21

As a DBA, there might not be a date that you didn’t use this tool. It is a wonderful tool and you will also be asked on attending interviews. But, have you wondered of adding parameters to this procedure and use it only to show only information on specific database? Well, a small modification of this stored procedure helped me in doing this, I am sure it will help you too. There isn’t much modification to it but the idea of only seeing the connection to database that you want is in my opinion is good one. You can create this sp in master database and use the same as you use sp_who2. The only difference is you can pass the database name as a parameter.
--------------------------------:
CREATE PROCEDURE sp_whoDB
@dbname sysname = null,
@loginame sysname = NULL
as

set nocount on
declare
@retcode int
declare @dbid int

select @dbid = dbid from sysdatabases where name = @dbname

declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int

declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)

declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
--------
select
@retcode = 0 -- 0=good ,1=bad.

--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
@spidlow = 0
,@spidhigh = 32767

--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
--------
-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (lower(@loginame) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end

--------

RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN

LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
SELECT

spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'

, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'

INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
where (dbid = @dbId or @dbName is null) and spid > 12




--------Screen out any rows?

IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)

and blocked = 0



--------Prepare to dynamically optimize column widths.


Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)



SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)

,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)

,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)

,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)

,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)

,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)

,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)

,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
-- sid >= @sidlow
-- and sid <= @sidhigh
-- and
spid >= @spidlow
and spid <= @spidhigh



--------Output the report.


EXECUTE(
'
SET nocount off

SELECT
SPID = convert(char(5),spid)

,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END

,Login = substring(loginname,1,' + @charMaxLenLoginName + ')

,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END

,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END

,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')

,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')

,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')

,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
where
spid >= ' + @charspidlow + '
and spid <= ' + @charspidhigh + '

order by spid_sort

SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
sid >= ' + @charsidlow + '
and sid <= ' + @charsidhigh + '
and
**************/
LABEL_86RETURN:

if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses

return @retcode -- sp_who2

User name, group name and thier default database permissions

User name, group name and thier default database :
================================================
http://sqlserverinternals.blogspot.com/2006/06/user-name-group-name-and-thier-default.html

-----------------:
The following script will help you to list all user name, group name and thier default database. The script will help you to list all of the above on single instance but includes all databases. I use this script as starting point to fix any security issues.


set nocount on
declare @dbName sysname, -- database name
@dbid int -- database Id

IF (object_id('tempdb..#userDetails') IS not Null)
Drop Table #userDetails
-- create temp table to hold info
BEGIN
CREATE TABLE #userDetails
(DbName sysname,
UserName sysname,
GroupName sysname,
LoginName sysname,
UserDefaultDB sysname)
END

declare @dbnames table(dbid int not null primary key clustered, dbname nvarchar(100))
INSERT INTO @dbnames(dbid, dbname)
select dbid, name from master.dbo.sysdatabases where dbid > 4 and name not like '%Sharepoint%'
select @dbid = max(dbid) from @dbnames

while @dbid is not null
begin
SELECT @dbName = dbname FROM @dbnames
WHERE dbid = @dbid
EXECUTE(
'use ' + @dbName + '
INSERT INTO #userDetails(DbName, UserName, GroupName, LoginName, UserDefaultDB)
SELECT db_name() as DBName, usu.name As UserName , case when (usg.uid is null) then ''public'' else usg.name end as GroupName ,
lo.loginname ,lo.dbname as UserDefaultDbName
from sysusers usu
join
(sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
join master.dbo.syslogins lo on usu.sid = lo.sid
where (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1)
and (usg.issqlrole = 1 or usg.uid is null)
')

select @dbid = max(dbid) from @dbnames
where dbid < @dbid end select * from #userDetails order by DbName, UserName, GroupName asc

SQL 2005 Security - Revoke EXECUTE rights for PUBLIC on (potentially) unsafe extended stored procedures

Where I work, we have an amazing crew of security architects and analysts who have decades of experience in all things security. Sure, at times they may seem paranoid, but that's just because they've seen bad things that you and I couldn't even dream up. Recently I've been going through our security baseline to verify we're being as secure as possible with SQL (on the servers our team supports) and I'd like to share some code that will help identify any extended stored procedures (from SQL 2005) that our security folks have deemed potentially unsafe (when PUBLIC has been granted EXECUTE rights), as well as code to REVOKE those rights.

THE LIST


Here's the list of extended stored procedures that some folks have deemed potentially unsafe if PUBLIC could execute them:

xp_availablemedia
xp_cmdshell
xp_deletemail
xp_dirtree
xp_dropwebtask
xp_enumerrorlogs
xp_enumgroups
xp_findnextmsg
xp_fixeddrives
xp_getnetname
xp_logevent
xp_loginconfig
xp_makewebtask
xp_regread
xp_readerrorlog
xp_readmail
xp_runwebtask
xp_sendmail
xp_servicecontrol
xp_sprintf
xp_sscanf
xp_startmail
xp_stopmail
xp_grantlogin
xp_revokelogin
xp_logininfo
xp_subdirs
xp_regaddmultistring
xp_regdeletekey
xp_regdeletevalue
xp_regenumkeys
xp_regenumvalues
xp_regremovemultistring
xp_regwrite


Well, I don't know about you, but I don't want to go one-by-one through these 30-some objects, on each of our 30-some SQL servers and check/revoke rights. So I wrote my own little SQL script to identify any of these XPs (eXtended stored Procedures)

SQL TO VIEW EXTENDED STORED PROCS FOR WHICH PUBLIC HAS RIGHTS

This script will identify any of the said XPs on a SQL 2005 server which have EXECUTE rights granted to PUBLIC

SNIPPET #1 - Identify extended stored procedures for which PUBLIC has rights


USE MASTER;

SELECT
OBJECT_NAME(major_id) AS [Extended Stored Procedure],
USER_NAME(grantee_principal_id) AS [User]
FROM
sys.database_permissions
WHERE
OBJECT_NAME(major_ID) IN ('xp_availablemedia','xp_cmdshell',
'xp_deletemail','xp_dirtree',
'xp_dropwebtask','xp_enumerrorlogs',
'xp_enumgroups','xp_findnextmsg',
'xp_fixeddrives','xp_getnetname',
'xp_logevent','xp_loginconfig',
'xp_makewebtask','xp_regread',
'xp_readerrorlog','xp_readmail',
'xp_runwebtask','xp_sendmail',
'xp_servicecontrol','xp_sprintf',
'xp_sscanf','xp_startmail',
'xp_stopmail','xp_grantlogin',
'xp_revokelogin','xp_logininfo',
'xp_subdirs','xp_regaddmultistring',
'xp_regdeletekey','xp_regdeletevalue',
'xp_regenumkeys','xp_regenumvalues',
'xp_regremovemultistring','xp_regwrite')
AND USER_NAME(grantee_principal_id) LIKE 'PUBLIC'
ORDER BY 1;

OUTPUT #1

xp_regread public
xp_cmdshell public


Now, if you want to revoke the rights, you can modify that code so that it outputs a bunch of REVOKE statements which you can copy and then run from SQL Management Studio

SNIPPET #2 - Create REVOKE statements


USE MASTER;

SELECT
'REVOKE ALL ON ' + OBJECT_NAME(major_id) + ' FROM ' + USER_NAME(grantee_principal_id)
FROM
sys.database_permissions
OBJECT_NAME(major_ID) IN ('xp_availablemedia','xp_cmdshell',
'xp_deletemail','xp_dirtree',
'xp_dropwebtask','xp_enumerrorlogs',
'xp_enumgroups','xp_findnextmsg',
'xp_fixeddrives','xp_getnetname',
'xp_logevent','xp_loginconfig',
'xp_makewebtask','xp_regread',
'xp_readerrorlog','xp_readmail',
'xp_runwebtask','xp_sendmail',
'xp_servicecontrol','xp_sprintf',
'xp_sscanf','xp_startmail',
'xp_stopmail','xp_grantlogin',
'xp_revokelogin','xp_logininfo',
'xp_subdirs','xp_regaddmultistring',
'xp_regdeletekey','xp_regdeletevalue',
'xp_regenumkeys','xp_regenumvalues',
'xp_regremovemultistring','xp_regwrite')
AND USER_NAME(grantee_principal_id) LIKE 'PUBLIC'
ORDER BY 1;

OUTPUT #2


REVOKE ALL ON xp_regread FROM PUBLIC
REVOKE ALL ON xp_cmdshell FROM PUBLIC

IMPORTANT: This didn't revoke anything, this only created some REVOKE statements that you can copy from the result set into your own query window and execute them. THEN the revoking will happen.

The successful xp_logininfo on SQL Server 2005

SET NoCount ON

SET quoted_identifier OFF


DECLARE @groupname VARCHAR(100)

IF EXISTS

(SELECT * FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID(N'[tempdb].[dbo].[RESULT_STRING]'))

DROP TABLE [tempdb].[dbo].[RESULT_STRING];

CREATE TABLE [tempdb].[dbo].[RESULT_STRING] ( Account_Name VARCHAR(2500),

type varchar(10),

Privilege varchar(10),

Mapped_Login_Name varchar(60),

Group_Name varchar(100) )

-- Cursor to hold database names to be backed up

DECLARE Get_Groups CURSOR

FOR Select

name from master..syslogins

where

isntgroup = 1 and status > 9 or Name= 'BUILTIN\ADMINISTRATORS'

-- Open cursor and loop through database names

OPEN Get_Groups

FETCH NEXT FROM Get_Groups INTO @groupname

WHILE ( @@fetch_status <> -1 )

BEGIN

IF ( @@fetch_status = -2 )

BEGIN

FETCH NEXT FROM Get_Groups INTO @groupname

CONTINUE

END

Insert into [tempdb].[dbo].[RESULT_STRING]

Exec master..xp_logininfo @Groupname, 'members'


FETCH NEXT FROM Get_groups INTO @groupname

END

DEALLOCATE Get_Groups

Alter TABLE [tempdb].[dbo].[RESULT_STRING] Add Server varchar(100) NULL;

GO

Update [tempdb].[dbo].[RESULT_STRING] Set Server = CONVERT(varchar(100), SERVERPROPERTY('Servername'))

Select * from [tempdb].[dbo].[RESULT_STRING]

SET NoCount OFF

sp_ListNTUserNameDatabaseAccess

Not Compelete (To DO -- Next Week):
===================================
http://lynchtek.com/sp_ListNTUserNameDatabaseAccess.aspx
----------------------------------------------------------
ALTER Proc usp_ListAllNTUsersAndDBs (@NtGroup varchar(300))
as
Create Table #DBUsers(DB varChar(3000),ssid varBinary(85))
Create Table #NTUsers(Accountname varChar(300),type varChar(300),Privilege varChar(300), mappedlogin varChar(300),permission varChar(300))
Declare @dbname varChar(3000)

Select @dbname =''

While Not @dbname is Null
begin

Select @dbname = min(name) From master..sysdatabases Where name > @dbname
if @dbname is Null

begin

break

end

Insert Into #DbUsers (db,ssid)
Select @dbname, sid From master..sysusers Where isntgroup=1

end

Select @ntGroup=''

While Not @NtGroup is Null
begin

Select min(sl.name)
From master..sysusers sl join #DBusers db on sl.sid = db.ssid
Where sl.name > @NtGroup

if @NtGroup is Null

begin

break

end
Insert Into #NTUsers (AccountName,Type,Privilege,Mappedlogin,permission)
EXEC xp_logininfo @NtGroup,'members'

end


Select distinct accountname,name,db From #NTUSers N
join (Select sl.name,db.db From master..sysusers sl join #DBusers db on sl.sid = db.ssid) X
on X.Name = N.Permission

drop Table #NTUsers
drop Table #DBUsers

exec usp_ListAllNTUsersAndDBs 'BUILTIN\Administrators'