-
Is there any tool that documents all the users and their privilage levels?
Is there any tool that documents all the users and their privilage levels in a given database??
I looked at SQL Scribe tool but it doesn't care about documenting users at all!
We need this for documenting hundreds of databases for SOX purpose.
Thanks,
-
Try with sp_helplogins, it lists all db roles the login belongs to.
-
sp_helplogins can't serve my requirement as it doesn't provide detail info as to what privilages are defined within a role if role based security is assigned. It will just list the name of the role under the user's login.
I need to see detail info in a report format without having to query multiple times for each login and role separately.
Any third-party tool available for this??
-
This script goes down to object level returning all Users and Roles in the NorthWind database.
-- Get User/Roles
use NorthWind
declare @chvDBName varchar(50)
set @chvDBName = 'NorthWind'
SELECT
'Database' = @chvDBName,
'UserOrRoleName' = sysusers.name,
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
FOR LOGINS:
--------------------------------------
select
'Server' = UPPER(@@SERVERNAME),
'LoginName' = loginname,
'HasAccess' = case when hasaccess = 1 then 'Yes' else 'No' end,
'LoginType' = case when isNTname = 1 then 'NTUserOrGroupLogin' else 'SQLServerLogin' end,
'Is_NT_Group' = case when isNTgroup = 1 then 'Yes' else 'No' end,
'Is_NT_User' = case when isNTuser = 1 then 'Yes' else 'No' end,
'SrvRole_sysadmin' = case when sysadmin = 1 then 'Yes' else 'No' end,
'SrvRole_securityadmin' = case when securityadmin = 1 then 'Yes' else 'No' end,
'SrvRole_serveradmin' = case when serveradmin = 1 then 'Yes' else 'No' end,
'SrvRole_setupadmin' = case when setupadmin = 1 then 'Yes' else 'No' end,
'SrvRole_processadmin' = case when processadmin = 1 then 'Yes' else 'No' end,
'SrvRole_diskadmin' = case when diskadmin = 1 then 'Yes' else 'No' end,
'SrvRole_dbcreator' = case when dbcreator = 1 then 'Yes' else 'No' end
from master..syslogins
As for sarbanes....... I dont think i want to see another catastrophe like u know who.
-
Excellent!! thanks KingSexy182.
-
I need to include DENY in your code, how do I find what is the corresponding number for DENY?
----------
SELECT
'Database' = @chvDBName,
'UserOrRoleName' = sysusers.name,
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
-----------
-
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
-
Thank you MAK. That is working great!!
-
Thanks to King.
I just updated his script.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|