Hi All,
I find this site very helpful so I thought I'd give back to the community. I wanted to write a script that would allow me to see which users had update permissions on any given table. I'm using Windows Security in NAV 6. The code below is not all original work. The section that creates the user table was found on this site.
I plan on creating a web form on our intranet that will prompt for the table name and possibly the access type (Read/Update/Insert) and plugs the values in the appropriate spots before executing the SQL code.
Anyways, thought this might be useful for some poor soul trying to perform SOX auditing.
Declare
@Tablename varchar(50),
@ObjectID int
-- This can be modified to see access for any given table
Select @Tablename = 'Item'
-- Get the object ID of the selected table
-- NAV company name can be changed below
Select @ObjectID = ID
From [Object]
Where Name = @Tablename
and [Company Name] = 'EPW - LIVE'
-- Start of Create User Table
-- The following section just creates a table containing
-- Windows user names and SIDs
Declare @Users table (Name varchar(30), NAVSID varchar(255))
Declare
@SID varchar(255),
@OrigSID varchar(255),
@A bigint,
@B bigint,
@C bigint,
@D bigint,
@Value varbinary(255)
Declare PrintUser INSENSITIVE CURSOR FOR
Select SID From [Windows Login]
Open PrintUser
Fetch Next From PrintUser Into @SID
While (@@fetch_status <> -1)
Begin
Select @OrigSID = @SID
SET @SID = REVERSE(@SID)
SET @D = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))
SET @SID = SUBSTRING(@SID,CHARINDEX('-',@SID)+1,255)
SET @C = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))
SET @SID = SUBSTRING(@SID,CHARINDEX('-',@SID)+1,255)
SET @B = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))
SET @SID = SUBSTRING(@SID,CHARINDEX('-',@SID)+1,255)
SET @A = REVERSE(SUBSTRING(@SID,1,CHARINDEX('-',@SID)-1))
-- Prefix the binary value with S-1-5-21 and reverse the byte order for each group.
Select @Value =
0x010500000000000515000000
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@A)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@B)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@C)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@D)))
Insert into @Users
select Name, @OrigSID as 'NAV SID' from master..syslogins where SID = @Value
Fetch Next from PrintUser into @SID
End
Deallocate PrintUser
-- End of Create User Table
-- After we have the list of users, and the object ID,
-- it's pretty easy to find out who can modify it
Select Name From @Users Where NAVSID in (
Select Distinct [Login SID] from [Windows Access Control]
Where [Role ID] in (
Select [Role ID]
From [Permission]
Where [Object ID] = @ObjectID
and [Modify Permission] = 1
)
)
Login
Register
RSS Feed
Rules
FAQ
Search the forum
