Looking for a script to audit user table rights

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Looking for a script to audit user table rights

Author

Topic

demonbane
Starting Member

10 Posts

Posted - 2004-09-28 : 10:42:43

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.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-28 : 11:18:47

will sp_table_privileges help?

Go with the flow & have fun! Else fight the flow

demonbane
Starting Member

10 Posts

Posted - 2004-09-28 : 11:29:33

It does work to an extent but I was wondering if there was a way that it will look at all tables in a database. I have over 25k tables. It would be bad to have to run this stored proc that many times. I can't seem to find a way to list individual users and the table rights they have on the entire database. I may be grasping at straws; this audit has be going.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-28 : 11:46:37

use db1
EXEC sp_table_privileges '%'
use db2
EXEC sp_table_privileges '%'
...

might help.
google returns nothing?

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-30 : 05:02:29

DECLARE @rowcount int,@rowCount2 int
DECLARE @Object table (RowNum int identity(1,1),username nvarchar(100),objName nvarchar(100))

insert into @Object(username,objname)
SELECT usr.name as UserName,obj.name AS ObjectName
FROM
sysusers AS usr
join syspermissions AS per
ON usr.uid=per.grantee
JOIN sysobjects AS obj
ON obj.id = per.id
WHERE
obj.xtype IN ('U', 'P', 'FN')
AND obj.name NOT LIKE 'dt_%'
ORDER BY usr.name
set @rowcount = @@rowcount

create table #Permission
(RowNum int identity(1,1),Owner nvarchar(50),Object nvarchar(100), Grantee nvarchar(100),Grantor nvarchar(100),ProtectType nvarchar(50), [Action] nvarchar(50), [column] nvarchar(100))
declare @rownum int,@name nvarchar(4000),@user nvarchar(100)
set @rownum=1
set @rowCount2=0
while @rownum<=@rowcount
begin
select @name=objname,@user=username from @Object where ROWNUM=@rownum
insert #Permission exec sp_helprotect @name,@user
set @rowCount2=@rowCount2+ @@rowcount
set @rownum=@rownum+1
end

select rownum,owner,object,grantee,grantor,protectType,[action],[column] from #permission
drop table #permission

is this what you're looking for? I actually used this in a stored procedure and pass parameters for specific or all users.

hi spirit, thanks for that sproc, didn't know it existed...maybe i'll modify mine

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-30 : 07:24:43

damn girl you're on a roll... already past 400?? some insomnia....

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 03:12:59

quote:
Originally posted by spirit1

damn girl you're on a roll... already past 400?? some insomnia....

Go with the flow & have fun! Else fight the flow

i found another hobby to keep me awake, www.sql-ex.ru

, this is really fun, how about you? still looking for jenny's picture?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-01 : 05:48:55

and how are you scoring on those tests?
jenny's pic? no... their search returns an error. some search....

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 05:57:29

lolz, i was curious enough to search Kim though, but Jenny has no picture. They need to improve their search mechanism like searching for names instead of ID's.

the tests are ok, but there's seem to be some set answers, getting frustrated on submitting n-number of answers with correct result set but still incorrect! i even emailed them and asked why my answer is incorrect and they emailed back saying there is no need for this and that to get the answer.

Talk about dictatorship. But it's pretty entertaining...

wepisan
Starting Member

1 Post

Posted - 2004-10-21 : 10:26:12

You can try this also:

It worked great for our SOX compliance issues

DECLARE @user VARCHAR(30)

SET @user = NULL

CREATE TABLE userPermissions (
Owner VARCHAR(50) -- Owner
,Object VARCHAR(50) -- Object
,Grantee VARCHAR(50) -- Grantee
,Grantor VARCHAR(50) -- Grantor
,ProtectType VARCHAR(50) -- ProtectType
,Action VARCHAR(50) -- Action
,ColumnPermission VARCHAR(50) -- Column
)
INSERT INTO userPermissions
EXEC sp_helprotect @username = @user

SELECT
ProtectType + SPACE(0) + Action + ' ON [' + Owner + '].[' + Object + ']' +
CASE
WHEN (PATINDEX('%All%', ColumnPermission)=0) AND (ColumnPermission <> '.') THEN ' (' + ColumnPermission + ')'
ELSE SPACE(0)
END + ' TO [' + Grantee + ']' FROM userPermissions ORDER BY Grantee ASC

DROP TABLE userPermissions

GO

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 11:16:36

Do these do enough?

sp_helprotect @name='MyObjectName'

sp_helprotect @username='UserNameToCheckWhatPermissionsItHas'

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources