Check Again --SQL Server 2000

USE master
GO

IF OBJECT_ID('sp_ListPermissions') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID
('sp_ListPermissions'),'IsProcedure')=1
DROP PROCEDURE sp_ListPermissions
GO

CREATE PROCEDURE sp_ListPermissions
@object nvarchar(515) = NULL,
@column sysname = NULL
AS
/*
Procedure to list a uses effective permissions
Author : Itzik Ben−Gan
*/
SET NOCOUNT ON
DECLARE @permissions int
DECLARE @objectid int

SET @objectid = object_id(@object)

--Check input
IF @object IS NOT NULL AND @objectid IS NULL
BEGIN
RAISERROR('Invalid object %s supplied.', 16, 1, @object)
RETURN 1
END

IF @objectid IS NOT NULL
BEGIN

PRINT 'Object permissions on: ' + @object + ' for
login: ' + SUSER_SNAME()
+ ', user: ' + USER_NAME() + '...'

IF @column IS NOT NULL
BEGIN

SET @permissions = permissions(@objectid, @column)
PRINT 'On column: ' + @column + '...'
IF @permissions & 1 = 1 PRINT 'SELECT'
IF @permissions & 2 = 2 PRINT 'UPDATE'
IF @permissions & 4 = 4 PRINT 'REFERENCES'

END -- @column IS NOT NULL
ELSE -- @column IS NULL
BEGIN

SET @permissions = permissions(@objectid)
IF @permissions & 1 = 1 PRINT 'SELECT ALL'
IF @permissions & 2 = 2 PRINT 'UPDATE ALL'
IF @permissions & 4 = 4 PRINT 'REFERENCES ALL'
IF @permissions & 8 = 8 PRINT 'INSERT'
IF @permissions & 16 = 16 PRINT 'DELETE'
IF @permissions & 32 = 32 PRINT 'EXECUTE'
IF @permissions & 4096 = 4096 PRINT 'SELECT ANY (at least one column)'
IF @permissions & 8192 = 8192 PRINT 'UPDATE ANY (at least one column)'
IF @permissions & 16384 = 16384 PRINT 'REFERENCES ANY(at least one column)'

END -- @column IS NULL

END -- @objectid IS NOT NULL
ELSE -- @objectid IS NULL
BEGIN

SET @permissions = permissions()
PRINT 'Statement permissions for login: ' + SUSER_SNAME() + ', user: ' + USER_NAME() + '...'
IF @permissions & 1 = 1 PRINT 'CREATE DATABASE'
IF @permissions & 2 = 2 PRINT 'CREATE TABLE'
IF @permissions & 4 = 4 PRINT 'CREATE PROCEDURE'
IF @permissions & 8 = 8 PRINT 'CREATE VIEW'
IF @permissions & 16 = 16 PRINT 'CREATE RULE'
IF @permissions & 32 = 32 PRINT 'CREATE DEFAULT'
IF @permissions & 64 = 64 PRINT 'BACKUP DATABASE'
IF @permissions & 128 = 128 PRINT 'BACKUP LOG'
--IF @permissions & 256 = 256 PRINT 'Reserved'

END -- @objectid IS NULL
GO

grant exec on sp_ListPermissions to public
GO
--================================================

declare @chvDBName varchar(50)
set @chvDBName = db_name()

SELECT
'Database' = @chvDBName,
'UserOrRoleName' = sysusers.name,
'GrantType' = case protecttype
when 204 then 'RANT_W_GRANT'
when 205 then 'GRANT'
when 206 then 'REVOKE' end,
Permission = CASE action
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 26 THEN 'REFERENCE'
WHEN 224 THEN 'EXECUTE'
ELSE 'Unknown'
END
, 'ObjectName' = sysobjects.name
, 'ObjectType' = case when sysobjects.xtype = 'U' then 'Table'
when sysobjects.xtype = 'V' then 'View'
when sysobjects.xtype = 'P' then 'Procedure'
else 'Other'
end,
'Is_Role' = case when issqlrole = 1 then 'Yes' else 'No' end
FROM sysprotects, sysobjects, sysusers
WHERE sysobjects.id = sysprotects.id
AND sysprotects.action IN (193, 195, 196, 197, 224, 26)
AND sysprotects.uid = sysusers.uid
AND sysobjects.name not like 'dt[_]%'
AND sysobjects.name not like 'dt%'
AND sysobjects.name not like 'sel[_]%'
AND sysobjects.name not like 'sp_ins[_]%'
AND sysobjects.name not like 'sp_upd[_]%'
AND sysobjects.name not like 'sp_sel[_]%'
AND sysobjects.name not like 'sp_cft[_]%'
AND sysobjects.name not like 'ctsv[_]%'
AND sysobjects.name not like 'tsvw[_]%'
AND sysusers.name not like 'MSmerge[_]%'
AND sysobjects.xtype <> 'S'
AND sysobjects.name not in ('MSsubscription_agents', 'sysmergearticles', 'sysconstraints', 'syssegments')
ORDER BY sysusers.name, sysobjects.xtype, sysobjects.name
GO

--**************************************
--
-- Name: SQL Server Login Info
-- Description:Retrieve the SQL Server l
-- ogin configuration, trusted logins etc.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/vb/scripts/Sh
-- owCode.asp?txtCodeId=236&lngWId=5--for details.--**************************************
--

USE master
go
IF object_id('sp_Mylogininfo') IS NOT NULL
DROP PROCEDURE sp_Mylogininfo
go
CREATE PROCEDURE sp_Mylogininfo
(
@loginmode varchar(30) = NULL OUTPUT,
@integrated varchar(30) = NULL OUTPUT,
@showdetails bit = 0
)
as
/********************************************************************************/
/* Created BY : Umachandar Jayachandran (UC) */
/* Created ON : 20 October 1996 */
/* Description: This stored PROCEDURE can be used to obtain information */
/* about a SQL Server login. IF executed WITH no parameters*/
/* the stored PROCEDURE will report IF the CURRENT USER IS */
/* connected through standard OR integrated/mixed type */
/* login. The output can also be obtained through variables*/
/* TO make it easy to USE FROM other SPs. The showdetails */
/* flag will display the CURRENT NT logins AND groups which*/
/* have been granted access TO SQL Server. */
/********************************************************************************/
/* Resources : http://www.umachandar.com/resources.htm */
/********************************************************************************/
SET nocount ON
SET ansi_defaults OFF
DECLARE @account varchar(30), @nt_domain varchar(30), @nt_username varchar(30),
@privilege varchar(30), @showresults bit
IF @loginmode IS NULL or @integrated IS NULL
SELECT @showresults = 1
CREATE TABLE #loginconfig (name varchar(30), config_value varchar(30) null)
INSERT #loginconfig EXEC master..xp_loginconfig
SELECT @loginmode = config_value FROM #loginconfig WHERE name = 'login mode'
IF @loginmode = 'standard'


begin
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
CREATE TABLE #logininfo (account varchar(60), type varchar(30),
privilege varchar(30) null, mapped_login varchar(60) null,
permission_path varchar(255) null)
INSERT #logininfo EXEC master..xp_logininfo
DECLARE accounts insensitive CURSOR FOR
SELECT account FROM #logininfo WHERE charindex('SYSTEM', account) = 0
OPEN accounts
while('FETCH IS OK' = 'FETCH IS OK')


begin
FETCH next FROM accounts INTO @account
IF @@fetch_status < 0 BREAK
INSERT #logininfo EXEC master..xp_logininfo @account, 'members'
end
DEALLOCATE accounts
SELECT @nt_domain = nt_domain, @nt_username = nt_username FROM sysprocesses
WHERE spid = @@spid
-- First check for sa
IF suser_id() = 1


begin
-- Next CHECK FOR NT USER privilege
IF exists( SELECT privilege FROM #logininfo
WHERE account = @nt_domain + '\' + @nt_username
AND privilege = 'admin')
SELECT @integrated = 'Yes'
ELSE
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
-- Second check for users
SELECT @integrated = CASE WHEN @nt_username = suser_name() THEN 'Yes' ELSE 'No' END
SHOW_RESULTS_LABEL:
IF @showresults = 1


begin
SELECT @loginmode AS "Server Login Mode",
@integrated AS "Integrated"
end
IF @showdetails = 1


begin
PRINT ''
PRINT 'Server Security Configuration'
SELECT * FROM #loginconfig
PRINT ''
PRINT 'Server Security Details'
SELECT * FROM #logininfo
end
go
GRANT EXECUTE on sp_Mylogininfo TO PUBLIC
go
-- Usage Examples:
PRINT 'With no parameters...'
EXEC sp_Mylogininfo
PRINT ''
go
PRINT 'With show details option...'
EXEC sp_Mylogininfo @showdetails = 1
PRINT ''
go
PRINT 'With output parameters...'
DECLARE @loginmode varchar(30), @integrated varchar(30)
SELECT @loginmode = '', @integrated = ''
EXEC sp_Mylogininfo @loginmode out, @integrated out
SELECT @loginmode AS LoginMode, @integrated as Integrated