SQL to get a list of users/roles/permissions

[JA]

I'd like to perform an audit of users/roles/permissions within all my SQL
Server databases. How can I do so via a SQL query? I am looking for the
following sets of data:
1. username and role
2. role and permissions on each database table.

Thanks!!

[Uri Dimant]

JA
CREATE FUNCTION dbo.RoleCheckUser
(
@UserName sysname,
@RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @RetVal BIT
SET @RetVal = 0

SELECT @RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @UserName
AND groups.name = @RoleName
)

RETURN @RetVal
END
GO

-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO

"JA" <J...@discussions.microsoft.com> wrote in message
news:0F68942D-FD82-4D21...@microsoft.com...

[Neil Meyer]

This function doesn't do much -- it basically tells me (yes or no) if a user
is in a specific role, but then how do I list the effective permission for
that user for all non-system tables in the database?

Thanks

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:OA4%23UdU7G...@TK2MSFTNGP05.phx.gbl...

[howard-rothenburg]

--User Permissions

SELECT [UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = NULL,
[PermissionState] = perm.[state_desc],
[PermissionType] = perm.[permission_name],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM --database user
sys.database_principals princ
LEFT JOIN --Login accounts
sys.server_principals ulogin
ON princ.[sid] = ulogin.[sid]
LEFT JOIN --Permissions
sys.database_permissions perm
ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN --Table columns
sys.columns col
ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.objects obj
ON perm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas schem
ON schem.[schema_id] = perm.[major_id]
LEFT JOIN sys.database_principals imp
ON imp.[principal_id] = perm.[major_id]
WHERE princ.[type] IN ('S', 'U', 'G')
AND -- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY
ulogin.[name],
[UserType],
[DatabaseUserName],
[Role],
[PermissionState],
[PermissionType],
[ObjectType],
[ObjectName],
[ColumnName]