create proc sp_help
	@objname nvarchar(776) = NULL		-- object name we're after
as
	-- PRELIMINARY
	set nocount on
	declare	@dbname	sysname

	-- OBTAIN DISPLAY STRINGS FROM spt_values UP FRONT --
	declare @no varchar(35), @yes varchar(35), @none varchar(35)
	select @no = name from master.dbo.spt_values where type = 'B' and number = 0
	select @yes = name from master.dbo.spt_values where type = 'B' and number = 1
	select @none = name from master.dbo.spt_values where type = 'B' and number = 2

	-- If no @objname given, give a little info about all objects.
	if @objname is null
	begin
		-- DISPLAY ALL SYSOBJECTS --
        select
            'Name'          = o.name,
            'Owner'         = user_name(uid),
            'Object_type'   = substring(v.name,5,31)
        from sysobjects o, master.dbo.spt_values v
        where o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
        order by Object_type desc, Name asc

		print ' '

		-- DISPLAY ALL USER TYPES
		select
			'User_type'		= name,
			'Storage_type'	= type_name(xtype),
			'Length'		= length,
			'Prec'			= TypeProperty(name, 'precision'),
			'Scale'			= TypeProperty(name, 'scale'),
			'Nullable'		= case when TypeProperty(name, 'AllowsNull') = 1
											then @yes else @no end,
			'Default_name'	= isnull(object_name(tdefault), @none),
			'Rule_name'		= isnull(object_name(domain), @none),
			'Collation'		= collation
		from systypes
		where xusertype > 256
		order by name

		return(0)
	end

	-- Make sure the @objname is local to the current database.
	select @dbname = parsename(@objname,3)

	if @dbname is not null and @dbname <> db_name()
		begin
			raiserror(15250,-1,-1)
			return(1)
		end

	-- @objname must be either sysobjects or systypes: first look in sysobjects
	declare @objid int
	declare @sysobj_type char(2)
	select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@objname)

	-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
	if @objid is null
	begin
		-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
		select @objid = xusertype from systypes where name = @objname

		-- IF NOT IN SYSTYPES, GIVE UP
		if @objid is null
		begin
			select @dbname=db_name()
			raiserror(15009,-1,-1,@objname,@dbname)
			return(1)
		end

		-- DATA TYPE HELP (prec/scale only valid for numerics)
		select
			'Type_name'		= name,
			'Storage_type'	= type_name(xtype),
			'Length'		= length,
			'Prec'			= TypeProperty(name, 'precision'),
			'Scale'			= TypeProperty(name, 'scale'),
			'Nullable'		= case when allownulls=1 then @yes else @no end,
			'Default_name'	= isnull(object_name(tdefault), @none),
			'Rule_name'		= isnull(object_name(domain), @none),
			'Collation'		= collation
		from systypes
		where xusertype = @objid

		return(0)
	end

	-- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO
	select
		'Name'				= o.name,
		'Owner'				= user_name(uid),
        'Type'              = substring(v.name,5,31),
		'Created_datetime'	= o.crdate
	from sysobjects o, master.dbo.spt_values v
	where o.id = @objid and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'

	print ' '

	-- DISPLAY COLUMN IF TABLE / VIEW
	if @sysobj_type in ('S ','U ','V ','TF','IF')
	begin

		-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
		declare @numtypes nvarchar(80)
		select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'

		-- INFO FOR EACH COLUMN
		print ' '
		select
			'Column_name'			= name,
			'Type'					= type_name(xusertype),
			'Computed'				= case when iscomputed = 0 then @no else @yes end,
			'Length'				= convert(int, length),
			'Prec'					= case when charindex(type_name(xtype), @numtypes) > 0
										then convert(char(5),ColumnProperty(id, name, 'precision'))
										else '     ' end,
			'Scale'					= case when charindex(type_name(xtype), @numtypes) > 0
										then convert(char(5),OdbcScale(xtype,xscale))
										else '     ' end,
			'Nullable'				= case when isnullable = 0 then @no else @yes end,
			'TrimTrailingBlanks'	= case ColumnProperty(@objid, name, 'UsesAnsiTrim')
										when 1 then @no
										when 0 then @yes
										else '(n/a)' end,
			'FixedLenNullInSource'	= case
						when type_name(xtype) not in ('varbinary','varchar','binary','char')
							Then '(n/a)'
						When status & 0x20 = 0 Then @no
						Else @yes END,
			'Collation'		= collation
		from syscolumns where id = @objid and number = 0 order by colid

		-- IDENTITY COLUMN?
		if @sysobj_type in ('S ','U ','V ','TF')
		begin
			print ' '
			declare @colname sysname
			select @colname = name from syscolumns where id = @objid
						and colstat & 1 = 1
			select
				'Identity'				= isnull(@colname,'No identity column defined.'),
				'Seed'					= ident_seed(@objname),
				'Increment'				= ident_incr(@objname),
				'Not For Replication'	= ColumnProperty(@objid, @colname, 'IsIDNotForRepl')
			-- ROWGUIDCOL?
			print ' '
			select @colname = null
			select @colname = name from syscolumns where id = @objid and number = 0
						and ColumnProperty(@objid, name, 'IsRowGuidCol') = 1
			select 'RowGuidCol' = isnull(@colname,'No rowguidcol column defined.')
		end
	end

	-- DISPLAY PROC PARAMS
	if @sysobj_type in ('P ') --RF too?
	begin
		-- ANY PARAMS FOR THIS PROC?
		if exists (select id from syscolumns where id = @objid)
		begin
			-- INFO ON PROC PARAMS
			print ' '
			select
				'Parameter_name'	= name,
				'Type'				= type_name(xusertype),
                'Length'			= length,
                'Prec'				= case when type_name(xtype) = 'uniqueidentifier' then xprec
										else OdbcPrec(xtype, length, xprec) end,
                'Scale'				= OdbcScale(xtype,xscale),
                'Param_order'		= colid,
				'Collation'		= collation

			from syscolumns where id = @objid
		end
	end

	-- DISPLAY TABLE INDEXES & CONSTRAINTS
	if @sysobj_type in ('S ','U ')
	begin
		print ' '
		execute sp_objectfilegroup @objid
		print ' '
		execute sp_helpindex @objname
		print ' '
		execute sp_helpconstraint @objname,'nomsg'
		if (select count(*) from sysdepends where depid = @objid and deptype = 1) = 0
		begin
			raiserror(15647,-1,-1) -- 'No views with schemabinding reference this table.'
		end
		else
		begin
            select distinct 'Table is referenced by views' = obj.name from sysobjects obj, sysdepends deps
				where obj.xtype ='V' and obj.id = deps.id and deps.depid = @objid
					and deps.deptype = 1 group by obj.name

		end
	end
	else if @sysobj_type in ('V ')
	begin
		-- VIEWS DONT HAVE CONSTRAINTS, BUT PRINT THESE MESSAGES BECAUSE 6.5 DID
		print ' '
		raiserror(15469,-1,-1) -- No constraints defined
		print ' '
		raiserror(15470,-1,-1) --'No foreign keys reference this table.'
		execute sp_helpindex @objname
	end

	return (0) -- sp_help

quote:

---

Originally posted by SMerrill

I am embarking on making an ERD that will list the servers, databases, database objects, logins, roles, assigned permissions, blah blah. I have worked on this for many days, and the security model is pretty complex.

---

Private Sub ReadSecurity(ServerName As String)
    Dim cnnMe            As ADODB.Connection
    Dim cnnRemote        As ADODB.Connection
    Dim cmdHelp          As New ADODB.Command
    Dim cmdInsert        As New ADODB.Command
    Dim rst              As ADODB.Recordset
    Dim aRec             As Variant
    Dim iRec             As Long
    Dim strConn          As String
    Dim SkipServer       As Boolean
    Const RowDim         As Long = 2
    Const colLoginName1  As Long = 0
    Const colSID         As Long = 1
    Const colDefDBName   As Long = 2
    Const colDefLangName As Long = 3
    Const colAUser       As Long = 4
    Const colARemote     As Long = 5
    
    Const colLoginName2  As Long = 0
    Const colDBName      As Long = 1
    Const colUserName    As Long = 2
    Const colUserOrAlias As Long = 3
    
    With CurrentProject
        Set cnnMe = .Connection
    End With
    SkipServer = False
    Set cnnRemote = New ADODB.Connection
    On Error Resume Next
    'NOTE: *.UDL files are a good way to make up the following string:
    With cnnRemote
        strConn = Replace( _
            "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
            & "Persist Security Info=False;Data Source=|1;", "|1", ServerName)
        .ConnectionString = strConn
        .ConnectionTimeout = 2
        .Open
        If Err.Number <> 0 Then SkipServer = True
        If SkipServer Then
            Debug.Print "Unable to connect to " & ServerName
        Else
            Debug.Print ServerName
        End If
    End With
    
    If SkipServer Then GoTo ExitHere
    On Error GoTo 0
    With cmdHelp
        .ActiveConnection = cnnRemote
        .CommandText = "sp_helplogins"
        .CommandType = adCmdStoredProc
        Set rst = .Execute
        aRec = rst.GetRows
    End With
    
    With cmdInsert
        .ActiveConnection = cnnMe
        .CommandText = "sp_SQLHelpLogins1_Insert"
        .CommandType = adCmdStoredProc
    End With
    
    For iRec = LBound(aRec, RowDim) To UBound(aRec, RowDim)
        With cmdInsert
            .Parameters("@ServerName") = ServerName
            .Parameters("@LoginName") = aRec(colLoginName1, iRec)
            .Parameters("@SID") = aRec(colSID, iRec)
            .Parameters("@DefDBName") = aRec(colDefDBName, iRec)
            .Parameters("@DefLangName") = aRec(colDefLangName, iRec)
            .Parameters("@AUser") = aRec(colAUser, iRec)
            .Parameters("@ARemote") = aRec(colARemote, iRec)
            .Execute
        End With
        DoEvents
    Next iRec
    
' Get the second return set of the stored procedure:
    Set rst = rst.NextRecordset
    aRec = rst.GetRows
    
    With cmdInsert
        .CommandText = "sp_SQLHelpLogins2_Insert"
    End With
    
    For iRec = LBound(aRec, RowDim) To UBound(aRec, RowDim)
        With cmdInsert
            .Parameters("@ServerName") = ServerName
            .Parameters("@LoginName") = aRec(colLoginName2, iRec)
            .Parameters("@DBName") = aRec(colDBName, iRec)
            .Parameters("@UserName") = aRec(colUserName, iRec)
            .Parameters("@UserOrAlias") = aRec(colUserOrAlias, iRec)
            .Execute
        End With
        DoEvents
    Next iRec
ExitHere:
    Set rst = Nothing
    Set cmdHelp = Nothing
    Set cmdInsert = Nothing
    Set cnnRemote = Nothing
    Set cnnMe = Nothing
    Exit Sub
End Sub


CREATE  procedure dbo.sp_SQLHelpLogins1_Insert(
  @ServerName  SYSNAME
, @LoginName   SYSNAME
, @SID         VARBINARY(85)
, @DefDBName   SYSNAME
, @DefLangName SYSNAME
, @AUser       CHAR(5)
, @ARemote     CHAR(7)
) AS

-- When SP_HelpLogins is run from the client,
-- it uses this sproc to load the data into SQL Server.

INSERT INTO
 [DBInventory].[dbo].[temp_sphelplogins1](
[ServerName]
, [LoginName]
, [SID]
, [DefDBName]
, [DefLangName]
, [AUser]
, [ARemote]
)
VALUES(
  @ServerName
, @LoginName
, @SID
, @DefDBName
, @DefLangName
, @AUser
, @ARemote
)

CREATE TABLE [temp_sphelplogins1] (
	[ServerName] [sysname] NOT NULL ,
	[LoginName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SID] [varbinary] (85) NULL ,
	[DefDBName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DefLangName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AUser] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ARemote] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO


CREATE TABLE [temp_sphelplogins2] (
	[ServerName] [sysname] NOT NULL ,
	[LoginName] [sysname] NOT NULL ,
	[DBName] [sysname] NOT NULL ,
	[UserName] [sysname] NOT NULL ,
	[UserOrAlias] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE  procedure dbo.sp_SQLHelpLogins2_Insert(
  @ServerName  SYSNAME
, @LoginName   SYSNAME
, @DBName      SYSNAME
, @UserName    SYSNAME
, @UserOrAlias CHAR(8)
) AS

-- When SP_HelpLogins is run from the client,
-- it uses this sproc to load the data into SQL Server.
INSERT INTO [DBInventory].[dbo].[temp_sphelplogins2](
[ServerName]
, [LoginName]
, [DBName]
, [UserName]
, [UserOrAlias]
)
VALUES(
  @ServerName
, @LoginName
, @DBName
, @UserName
, @UserOrAlias
)

quote:

---

Originally posted by X002548

kidding, he was kidding.....

I've known Sean for a bit

---

quote:

---

You like emoticons, here is one <edited out>.

---

quote:

---

Originally posted by rockmoose

Not everyone starts the day with 3 cups of sarcasm and cynisism

---

quote:

---

Originally posted by rockmoose

so don't expect everyone to understand Your sense of humor.

---