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

[robert madrian]

Hello,

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

robert

[Andrew J. Kelly]

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

"robert madrian" <off...@madrian.at> wrote in message
news:elv1ToJi...@TK2MSFTNGP04.phx.gbl...

[Bob Barrows]

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

[Bob Barrows]

robert madrian wrote:
> Hello,
>
> how can I script all objects without permissions (Grants) ?
>

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

[robert madrian]

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" <reb0...@NOyahoo.SPAMcom> schrieb im Newsbeitrag
news:uAVl8sJi...@TK2MSFTNGP06.phx.gbl...

[Bob Barrows]

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

[robert madrian]

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" <reb0...@NOyahoo.SPAMcom> schrieb im Newsbeitrag

news:uFdlXaKi...@TK2MSFTNGP02.phx.gbl...

[Bob Barrows]

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

[robert madrian]

Sorry Bob,

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

thank's

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> schrieb im Newsbeitrag

news:eTJQ0lLi...@TK2MSFTNGP06.phx.gbl...