asked on

Get Permissions for Tables, Views, Procs, etc..

I'm trying to programmaticly pull user permissions for tables, views, procs, etc..
I can do this within Management Studio:
EXEC sp_helprotect 'prcProcedureName'
or
EXEC sp_helprotect 'dbo.prcProcedureName'

However, when I programmaticly do it from VBS or VB6 I get an error saying:
"There are no matching rows on which to report."

Is there another way to do it and why does this only work within Management Studio?

SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chapmandew

make sure you're looking at the correct db when you run the report.

Chizl

ASKER

Actually, I think I just figured out another way..

SELECT
	su.[Name]
FROM sysusers su
INNER JOIN sys.database_permissions dp
	ON dp.grantee_principal_id=su.uid
WHERE dp.major_id=OBJECT_ID('prcProcedureName')

Open in new window

chapmandew

you can do it that way too....either way, just make sure you're connected to the correct db when you run it.

Chizl

ASKER

I am pointing at the right connection. I have sp_helptext to pull the Stor proc right before it and it works, I replace that line with sp_helpprotect and it fails. Everything is exactly the same.

chapmandew

then I would use the script that you have that uses the join on the system tables.

the proc name is sp_helprotect, by the way...only 1 p in it.

Chizl

ASKER

So in the below VB6 Code, I'm commenting 1 and uncommenting the other.. the sp_helptext works when uncommented, but the sp_helprotech fails when uncommented.

'sql = "EXEC sp_helptext 'prcProcName'"
sql = "EXEC sp_helprotect 'prcProcName'"
Set objRS = objConn.EXECUTE(sql)

Open in new window

ASKER CERTIFIED SOLUTION

Chizl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial