issue sp_helprotect

30 pts.
if i give sp_helprotect on one table. it shows the list of access few tables permissions including given table. can i know why it is happens

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you are passing the procedure a table name it should give you back a single row for each user and each right granted to the table. Can you post the code that you are running?

We are having the same problem, and the reason is that we’ve removed select permission on one column in a table. This statement in sp_helprotect then gives all the fields (except the one we removed permissions for) in the output for both stored procedures, and for tables.

— Propagate object-level SL/UP/RF permission to columns as appropriate
insert #t1_Prots (


select c.object_id
,o.type collate database_default
,p.type collate database_default
,p.state collate database_default
from sys.columns c join sys.database_permissions p on p.class = 1 and p.major_id = c.object_id and p.minor_id = 0 and p.type in (‘RF’,’SL’,’UP’)
join sys.all_objects o on o.object_id = c.object_id
where not exists
(select * from sys.database_permissions m
where m.class = 1 and m.major_id = p.major_id and m.minor_id = c.column_id
and m.grantee_principal_id = p.grantee_principal_id and m.grantor_principal_id = p.grantor_principal_id
and m.type = p.type)
and exists
(select * from sys.database_permissions n
where n.class = 1 and n.major_id = p.major_id and n.minor_id > 0
and n.grantee_principal_id = p.grantee_principal_id and n.grantor_principal_id = p.grantor_principal_id
and n.type = p.type)

This seems like a flaw in the procedure itself. Create a wrapper around sp_helprotect, and then remove all rows not corresponding to the table/procedure that you specified as a parameter.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    69,115 pointsBadges:
  • BrianFerry
    I would look at new enterprise security reporter that includes very powerful sql security reporting abilities. The tool is avialable from: You will be able to create customizable reports on security of objects and schemas, role membership, database users and server logins.
    20 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: