Randy Dyess has published an article called "Auditing Your SQL Server Environment: Part II (Reviewing Role Memberships)"

The following content is intellectual property of Randy Dyess, the original posting can be found here:

"Roles should be in foremost in your mind when planning the security of your SQL Server environments. Auditing inherited SQL Server installations is a relatively easy thing to accomplish, and all DBAs should audit their environment and create documentation if they have not already done so. Once you can document the logins assigned to each of your fixed and user-defined roles, you can start to remove any duplication of permissions which can reduce the time needed to troubleshoot future permission errors."

--Use the master database
USE master
go

IFOBJECT_ID('dbo.spRoleMembers') ISNOTNULL
DROPPROCEDURE dbo.spRoleMembers
GO

CREATEPROCEDURE dbo.spRoleMembers
AS
/************************************************************
Creation Date: 04/28/02 Created By: Randy Dyess
Web Site: www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Loops through all databases and obtains member
for database roles as well as server role members.
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates:
None

************************************************************/
SETNOCOUNTON

--Variables
DECLARE @lngCounter INTEGER
DECLARE @strDBName VARCHAR(50)
DECLARE @strSQL NVARCHAR(4000)

--Temp table to hold database and user-define role user names
CREATETABLE #tRolemember
(
strServerName VARCHAR(50) DEFAULT@@SERVERNAME
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)

--Temp table to hold database names
CREATETABLE #tDBNames
(lngID INTEGER IDENTITY(1,1)
,strDBName VARCHAR(50)
)

--Create permanent table
IFOBJECT_ID ('dbo.tRolemember') ISNULL
BEGIN
CREATETABLE dbo.tRolemember
(
strServerName VARCHAR(50)
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)
END

--Obtain members of each server role
INSERTINTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC dbo.sp_helpsrvrolemember

--Obtain database names
INSERTINTO #tDBNames (strDBName)
SELECT name FROM master.dbo.sysdatabases
SET @lngCounter = @@ROWCOUNT

--Loop through databases to obtain members of database roles and user-defined roles
WHILE @lngCounter > 0
BEGIN

--Get database name from temp table
SET @strDBName = (SELECT strDBName FROM #tDBNames WHERE lngID = @lngCounter)

--Obtain members of each database and user-defined role
SET @strSQL = 'INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC ' + @strDBName + '.dbo.sp_helprolemember'

EXECsp_executesql @strSQL

--Update database name in temp table
UPDATE #tRolemember
SET strDBName = @strDBName
WHERE strDBName ISNULL

SET @lngCounter = @lngCounter - 1

END

--Place data into permanent table
INSERTINTO tRolemember
SELECT trm.* FROM #tRolemember trm
LEFTJOIN tRoleMember prm
ON trm.strUserName = prm.strUserName
AND trm.strDBName = prm.strDBName
AND trm.strRoleName = prm.strRoleName
AND trm.strServerName = prm.strServerName
WHERE prm.strServerName ISNULL

GO

--Test Stored Procedure
EXEC dbo.spRoleMembers

PRINT'Display by User'
SELECT strUserName, strDBName, strRoleName, strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDERBY strUserName

PRINT'Display by Role'
SELECT strRoleName, strDBName, strUserName,strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDERBY strRoleName

PRINT'Display by Database'
SELECT strDBName, strRoleName,strUserName, strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDERBY strDBName

Randy also has published a script about auditing user's passwords

The following content is intellectual property of Randy Dyess, the original posting can be found here:

IF OBJECT_ID('dbo.spAuditPasswords') ISNOTNULL
DROPPROCEDURE dbo.spAuditPasswords
GO

CREATEPROCEDURE dbo.spAuditPasswords
AS
/****************************************************************************
Creation Date: 03/22/02 Created By: Randy Dyess
Web Site: www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Perform a simple audit of user's passwords
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates: None
Date Author Purpose
---------- -------------------------- ---------------------------------
****************************************************************************/
SETNOCOUNTON

--Variables
DECLARE @lngCounter INTEGER
DECLARE @lngCounter1 INTEGER
DECLARE @lngLogCount INTEGER
DECLARE @strName VARCHAR(256)

--Create table to hold SQL logins
CREATETABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin SYSNAMENULL
,lngPass INTEGER NULL
)

--Insert non ntuser into temp table
INSERTINTO #tLogins (strLogin)
SELECT name FROM master.dbo.sysloginsWHERE isntname = 0
SET @lngLogCount = @@ROWCOUNT

--Determine if password is null and user iis SQL Login
PRINT'The following logins have blank passwords'
SELECT name AS'Login Name'FROM master.dbo.syslogins
WHERE password ISNULL
AND isntname = 0


--Determine if password and name are the ssame
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
    SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)

    UPDATE #tLogins
    SET lngPass = (SELECT PWDCOMPARE (@strName,(SELECT password FROM master.dbo.sysloginsWHERE name = @strName)))
    WHERE numID = @lngCounter

    SET @lngCounter = @lngCounter - 1
END

PRINT'The following logins have passwords the same as their login name'
SELECT strLogin AS'Login Name'FROM #tLogins WHERE lngPass = 1

--Reset column for next password test
UPDATE #tLogins
SET lngPass = 0

--Determine if password is only one characcter long
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
    SET @lngCounter1 = 1
    SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)
    WHILE @lngCounter1 < 256
    BEGIN
        UPDATE #tLogins
        SET lngPass = (SELECT PWDCOMPARE (CHAR(@lngCounter1),(SELECT password FROM master.dbo.sysloginsWHERE name = @strName)))
        WHERE numID = @lngCounter
        AND lngPass <> 1
        
        SET @lngCounter1 = @lngCounter1 + 1
    END

    SET @lngCounter = @lngCounter - 1
END

PRINT'The following logins have one character passwords'
SELECT strLogin AS'Login Name'FROM #tLogins WHERE lngPass = 1
GO

--Test
EXEC dbo.spAuditPasswords

And last but not least Bradley Morris has published an article about "How to Script User and Role Object Permissions in SQL Server"

This weblog is sponsored by FITS.
We support PASS Germany!

Definition from the SQL Server 2005 Product Guide:

"Analysis Management Objects (AMO) allows client applications to access the range of administrative commands and capabilities that are available to Analysis Services by using an object library that can provide object-level validation capabilities. This is an alternative to manually generating both the DDL scripts for Analysis commands and the often-lengthy contents of the ASSL ObjectDefinition element. Applications that use AMO can either connect and work directly with objects on an Analysis Services instance, or they can create such objects without an existing connection and persist the metadata for later deployment. AMO also “wraps” Analysis Services Scripting Language (ASSL) commands and elements."

Microsoft SQL Server 2005 Management Objects Collection from the Feature Pack for Microsoft SQL Server 2005 - April 2006

Introducing AMO Concepts

Readme for AMOBrowser Sample

AMO Security Classes

Programming AMO Security Objects

Analysis Services Concepts and Objects

Permissions and Access Rights (SSAS)

This weblog is sponsored by FITS.
We support PASS Germany!

Strong passwords: How to create and use them

Password checker

This weblog is sponsored by FITS.
We support PASS Germany!