issue sp_helprotect

30 pts.
Tags:
sp_helprotect
T-SQL
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 (
Id
,Type1Code

,ObjType
,ActionName
,ActionCategory
,ProtectTypeName

,ColId
,OwnerName
,ObjectName
,GranteeId
,GrantorId
,GranteeName
,GrantorName
,ColumnName)
select c.object_id
,’1Regul’
,o.type collate database_default
,p.type collate database_default
,p.class
,p.state collate database_default
,c.column_id
,schema_name(o.schema_id)
,o.name
,p.grantee_principal_id
,p.grantor_principal_id
,user_name(p.grantee_principal_id)
,user_name(p.grantor_principal_id)
,c.name
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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,280 pointsBadges:
    report
  • BrianFerry
    I would look at new enterprise security reporter that includes very powerful sql security reporting abilities. The tool is avialable from: http://www.scriptlogic.com/products/enterprisesecurityreporter/sql You will be able to create customizable reports on security of objects and schemas, role membership, database users and server logins.
    20 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following