GK - Persmission on each database ( objects )

Hi,
Second attempt to post this same message ( yesterdays post is not to be foud
in this forum ? ).
I like to retrieve ALL permissions on all abjects for each user ( each role,
each login ) in ALL of the databases within one server environment. I tried
the standard "sp_" procs but they are to limited for this.
I have to deal with approx 250 databases on approx 60 servers so I like to h
ave something which I can use on server level and not on table level ( like
the standard procs ).
As an Oracle DBA I'm not that familiar with scripting on SQL server 2000 so
l need your help on this one.
who can help me out?
Thanks in advance,
Regards, GKramer
The netherlands.Hi,
Have a look into syspermissions system table, which stores all the
previleges granted using GRANT statement.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:D6C88E0C-8C67-4DAE-AB3E-9CBCD061AA28@.microsoft.com...
> Hi,
> Second attempt to post this same message ( yesterdays post is not to be
foud in this forum ? ).
> I like to retrieve ALL permissions on all abjects for each user ( each
role, each login ) in ALL of the databases within one server environment. I
tried the standard "sp_" procs but they are to limited for this.
> I have to deal with approx 250 databases on approx 60 servers so I like to
have something which I can use on server level and not on table level ( like
the standard procs ).
> As an Oracle DBA I'm not that familiar with scripting on SQL server 2000
so l need your help on this one.
> who can help me out?
> Thanks in advance,
> Regards, GKramer
> The netherlands.
>|||Hi,
Have a look into syspermissions system table, which stores all the
previleges granted using GRANT statement.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:D6C88E0C-8C67-4DAE-AB3E-9CBCD061AA28@.microsoft.com...
> Hi,
> Second attempt to post this same message ( yesterdays post is not to be
foud in this forum ? ).
> I like to retrieve ALL permissions on all abjects for each user ( each
role, each login ) in ALL of the databases within one server environment. I
tried the standard "sp_" procs but they are to limited for this.
> I have to deal with approx 250 databases on approx 60 servers so I like to
have something which I can use on server level and not on table level ( like
the standard procs ).
> As an Oracle DBA I'm not that familiar with scripting on SQL server 2000
so l need your help on this one.
> who can help me out?
> Thanks in advance,
> Regards, GKramer
> The netherlands.
>|||Hari,
Thanks for your quick response, but where do the id's refer to ? ( Where c
an I find the ERD according to the sys-tables )
1 id int 4 0
0 grantee smallint 2 0
0 grantor smallint 2 0
0 actadd smallint 2 0
0 actmod smallint 2 0
Guus Kramer|||Hi,
Details will be there in spt_values table in Master database.
0 - means it is Public
For Grantor and Grantee execute along with user_name function.
user_name(grantee),user_name(grantor)
For actadd and actmode join it with spt_values table.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:96235761-C242-4D52-8FAE-22E3B6C8A424@.microsoft.com...
> Hari,
> Thanks for your quick response, but where do the id's refer to ? ( Where
can I find the ERD according to the sys-tables )
> 1 id int 4 0
> 0 grantee smallint 2 0
> 0 grantor smallint 2 0
> 0 actadd smallint 2 0
> 0 actmod smallint 2 0
> Guus Kramer|||Look at sp_helprotect and the PERMISSIONS function also.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Cindy,
As I told the forum the SP_ proc are way to limited. I need something wihich
generates an output like this;
database -- login -- (connected to ) role -- object(s) -- object(s) permissi
on
I'm not familiar with scripting MS sql ( I'm a former Oracle DBA and 3 month
s on the (SQLserver) job now ) and I can not find any documentation of how t
he systables are related ( ERD ).
Please help me on this because I have to examin 300 database on 60 server!!
Best regards,
Guus Kramer,
The Netherlands