Script Objects (Table. SP etc.) without Permissions (Grants)

Script Objects (Table. SP etc.) without Permissions (Grants)

Post by Andrew J. » Thu, 31 Dec 2009 00:23:49


There is an option in the scripting wizard for whether to script permissions
or not. Just turn it off.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors
 
 
 

Script Objects (Table. SP etc.) without Permissions (Grants)

Post by Bob Barrow » Thu, 31 Dec 2009 00:26:02


Could you rephrase the question please? it's not clear if you are asking
how to script all objects that have no permissions granted on them, or
if you need to script objects with a user account that does not have
permissions on the objects.

The latter certainly does not sound as if it will be possible.

--
HTH,
Bob Barrows

 
 
 

Script Objects (Table. SP etc.) without Permissions (Grants)

Post by robert mad » Thu, 31 Dec 2009 00:26:43

Hello,

how can I script all objects without permissions (Grants) ?

robert
 
 
 

Script Objects (Table. SP etc.) without Permissions (Grants)

Post by Bob Barrow » Thu, 31 Dec 2009 00:31:25


Ah! There was a third option which Andrew cleverly detected.
Now the question makes sense: you want to script the objects without
scripting all the Grant statements as well. Andrew has answered that,
but if you are still puzzled, you can get more specific advice if you
specify the version of SQL Server you are using - are you using
Enterprise Manager or SSMS?
--
HTH,
Bob Barrows
 
 
 

Script Objects (Table. SP etc.) without Permissions (Grants)

Post by robert mad » Thu, 31 Dec 2009 00:49:53

Hello,

I want to script all objects that have no permissions granted on them...

in SMSS it is to much work to go through all objects and look if there is a
permissions granted on them

regards

"Bob Barrows" < XXXX@XXXXX.COM > schrieb im Newsbeitrag
 
 
 

Script Objects (Table. SP etc.) without Permissions (Grants)

Post by Bob Barrow » Thu, 31 Dec 2009 01:47:19

Oh, you want to loop through all the objects that have no permissions
granted on them and generate CREATE scripts for them? It's not
something I've ever even considered doing so I'm not going to be of much
assistance beyond offering a couple of hints (which will apply to
SQL2005 - let us know if you are using a different version). You can
figure out which objects have permissions granted on them by querying
the sys.database_permissions view. keep in mind that permissions don't
have to be explicitly granted: for example, permissions can be granted
for a schema which will affect all objects in that schema for which the
permission being granted applies.

As to figuring out how to generate the CREATE scripts, you should use
SQL Profiler to trace what the wizard does while generating scripts.





--
HTH,
Bob Barrows
 
 
 

Script Objects (Table. SP etc.) without Permissions (Grants)

Post by robert mad » Thu, 31 Dec 2009 03:33:59

it is not necessary to generate CREATE script - I only need a lisst of the
objects which have no permissions on them...

regards

"Bob Barrows" < XXXX@XXXXX.COM > schrieb im Newsbeitrag
 
 
 

Script Objects (Table. SP etc.) without Permissions (Grants)

Post by Bob Barrow » Thu, 31 Dec 2009 04:02:20


Sigh ... this is the second time you have ignored my request to specify
the version of SQL Server you are using. I will again provide an answer
that applies to SQL 2005, but if it turns out you are using a different
version, I will avoid your posts in the future.

It appears you simply need to do an outer join between
sys.database_permissions and sys.objects, like this:

select o.name,type_desc from sys.objects o left join
sys.database_permissions p
on o.[object_id]=major_id
where o.type in ('u','p','v','pc','fn','fs','ft','if','tf','sn','x')
and major_id is null
order by type_desc,o.name;

Again, this only excludes objects with explicit permissions. If you need
it to exclude objects with inherited permissions you need to let us
know.








--
HTH,
Bob Barrows
 
 
 

Script Objects (Table. SP etc.) without Permissions (Grants)

Post by robert mad » Thu, 31 Dec 2009 04:21:28

orry Bob,

I have read over - your are right I use SQLServer 2005 and and you have
solved my problem

thank's

"Bob Barrows" < XXXX@XXXXX.COM > schrieb im Newsbeitrag
news: XXXX@XXXXX.COM ...