We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Medium Priority
223 Views
Last Modified: 2012-05-06
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?
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
you should be able to run the script you have through VB...just assign it to a command and execute it.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

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

Author

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

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

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

Author

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.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
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.

Author

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

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.