Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

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?
0
Chizl
Asked:
Chizl
  • 4
  • 4
2 Solutions
 
chapmandewCommented:
you should be able to run the script you have through VB...just assign it to a command and execute it.
0
 
chapmandewCommented:
make sure you're looking at the correct db when you run the report.
0
 
ChizlAuthor Commented:
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
chapmandewCommented:
you can do it that way too....either way, just make sure you're connected to the correct db when you run it.
0
 
ChizlAuthor Commented:
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.
0
 
chapmandewCommented:
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.
0
 
ChizlAuthor Commented:
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

0
 
ChizlAuthor Commented:
I found the problem..  Permissions based on the user/pass I was using for the connection string..  Strang enough, I didn't get a permissions error.   But when I changed it to use my login credintials, it works.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now