RE: Looking for a script to audit user table rights
From: dwh2200 (dwh2200_at_discussions.microsoft.com)
Date: 09/28/04
- Next message: Kevin McDonnell [MSFT]: "Re: URGENT:Cannot force SSL Encryption on from SQL Client"
- Previous message: Marcos Marrero: "xp_cmdshell"
- In reply to: Jerry: "Looking for a script to audit user table rights"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 28 Sep 2004 11:31:03 -0700
You can use the script below to right out all the user privileges that are
explicitly defined to users and user-defined roles...
select distinct
b.[name] as [Object],
c.[name] as [User],
CASE c.[issqlrole]
WHEN 1 THEN 'ROLE'
WHEN 0 THEN 'USER'
END AS [Role or User],
CASE a.[action]
WHEN 26 THEN 'REFERENCES'
WHEN 178 THEN 'CREATE FUNCTION'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
WHEN 236 THEN 'CREATE RULE'
END as [Entitlement],
CASE a.[protecttype]
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'REVOKE'
END as [Admin Level]
from sysprotects a,
sysobjects b,
sysusers c
where a.id = b.id
and a.uid = c.uid
order by c.name
I'd bounce it off something like this for more info on role membership, as
the script above won't give you the whole picture by itself:
SELECT a.name AS [UserId],
CASE
WHEN ISNULL(b.name, ' ') = 'public' THEN ' '
ELSE ISNULL(b.name, ' ')
END AS [Alias],
ISNULL(c.name, ' ') AS [Role],
ISNULL(d.name, ' ') AS [Role Memberships]
FROM dbo.sysusers a LEFT JOIN dbo.sysusers b ON (a.altuid = b.uid)
LEFT JOIN dbo.sysmembers e ON (a.uid = e.memberuid)
LEFT JOIN dbo.sysusers c ON (e.groupuid = c.uid)
LEFT JOIN dbo.sysmembers f ON (c.uid = f.memberuid)
LEFT JOIN dbo.sysusers d ON (f.groupuid = d.uid)
WHERE A.ISSQLROLE = 0
ORDER BY ISNULL(b.name, ' ')
"Jerry" wrote:
> Hello everyone,
>
> Does anyone know of a quick way to audit all users in a
> database and display their rights and permissions on a
> table level. I would hate to have to do it one user at a
> time. There has to be an easier way.
>
> I'm going through a Sarbanes Oxley audit and need to
> provide them this information.
>
> thanks everyone.
>
- Next message: Kevin McDonnell [MSFT]: "Re: URGENT:Cannot force SSL Encryption on from SQL Client"
- Previous message: Marcos Marrero: "xp_cmdshell"
- In reply to: Jerry: "Looking for a script to audit user table rights"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
