need script to check permission on objects

Question

need script to check permission on objects

AppSup_dba

SSCrazy

Points: 2215
More actions
November 30, 2010 at 8:00 am

#231756

Hey all,
I need a script/pointers to generate the permission (execute etc) on SPs/Views.
(I know how to check it via Management studio)
Any help!!
Thankx
Cheers

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

check sys.database_permissions.

A good trick if you know how to do something with SSMS is to run a profiler trace of your own SSMS spid and you will capture the resulting sql that SSMS executes.

If you have any questions about how to use it, let me know.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

PKDC SSChasing Mays Points: 618 More actions · Answer 2

You can use the following script to extract all user object permission in a database.

select a.name, d.name+'.'+c.name, b.permission_name, b.state_desc, c.type from sys.database_principals a join

sys.database_permissions b on a.principal_id=b.grantee_principal_id join

sys.objects c on b.major_id=c.object_id join

sys.schemas d on c.schema_id=d.schema_id

where c.type <>'s'

AppSup_dba SSCrazy Points: 2215 More actions · Answer 3

Thankx for your response.

Let me tell you my scenario.

I want to modify a Stored Procedure. I am not allowed to use ALTER. I have to DROP and CREATE the SPs. For this purpose i have to generate the permissions on the original SP so that i can recreate the permissions on the modified SP.

How to do this using SQL script?

Cheers

Robert Klimes SSCoach Points: 17865 More actions · Answer 4

You have all the pieces you need in the script provided by Ki Chiang. Instead of individual columns in the select, add them together as a string to create your grant/deny statements.

select ''+b.state_desc +' '+b.permission_name+' on '+d.name+'.'+c.name+' to '+a.name+'' collate SQL_Latin1_General_CP1_CI_AS

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

PKDC SSChasing Mays Points: 618 More actions · Answer 5

Here is the modified script for your purpose. (type S= system and type P= store procedure)

select state_desc+' '+b.permission_name+' on '+d.name+'.'+c.name+' to ['+a.name+']' COLLATE DATABASE_DEFAULT

from sys.database_principals a join

sys.database_permissions b on a.principal_id=b.grantee_principal_id join

sys.objects c on b.major_id=c.object_id join

sys.schemas d on c.schema_id=d.schema_id

where c.type <>'s' and c.type='p'

AppSup_dba SSCrazy Points: 2215 More actions · Answer 6

thanks people.... i got it what i was looking for... above two scripts are fine...

Cheers