Urgent Script Help! :(

  • I have the following script.. I need to incorporate things such as "Create Database/Table/View...etc". Right now, it's simple pulling privs on objects. Very similar to help protect.

    The issue is, I need to pull permissions in a script very similar to sp_helprotect b/c it seems to be broken and simple need a script to replace the SP.

    Any suggestions...?

    select

    sysusers.name as username, sysobjects.name as objectname, sysobjects.xtype as ObjectType,

    CASE WHEN sysprotects_1.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Select' END as 'SELECT',

    CASE WHEN sysprotects_2.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'INSERT' END as 'INSERT',

    CASE WHEN sysprotects_3.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'UPDATE' END as 'UPDATE',

    CASE WHEN sysprotects_4.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'DELETE' END as 'DELETE',

    CASE WHEN sysprotects_5.action is null THEN CASE WHEN sysobjects.xtype = 'U' THEN 'N/A' ELSE 'No' END ELSE 'EXECUTE' END as 'EXECUTE'

    from

    sysusers

    full join sysobjects on ( sysobjects.xtype in ( 'P', 'U', 'X', 'V' ) and sysobjects.Name NOT LIKE 'dt%' )

    left join sysprotects as sysprotects_1

    on sysprotects_1.uid = sysusers.uid and sysprotects_1.id = sysobjects.id and sysprotects_1.action = 193 and sysprotects_1.protecttype in ( 204, 205 )

    left join sysprotects as sysprotects_2

    on sysprotects_2.uid = sysusers.uid and sysprotects_2.id = sysobjects.id and sysprotects_2.action = 195 and sysprotects_2.protecttype in ( 204, 205 )

    left join sysprotects as sysprotects_3

    on sysprotects_3.uid = sysusers.uid and sysprotects_3.id = sysobjects.id and sysprotects_3.action = 197 and sysprotects_3.protecttype in ( 204, 205 )

    left join sysprotects as sysprotects_4

    on sysprotects_4.uid = sysusers.uid and sysprotects_4.id = sysobjects.id and sysprotects_4.action = 196 and sysprotects_4.protecttype in ( 204, 205 )

    left join sysprotects as sysprotects_5

    on sysprotects_5.uid = sysusers.uid and sysprotects_5.id = sysobjects.id and sysprotects_5.action = 224 and sysprotects_5.protecttype in ( 204, 205 )

    where

    (sysprotects_1.action is not null or sysprotects_2.action is not null or

    sysprotects_3.action is not null or sysprotects_4.action is not null or

    sysprotects_5.action is not null)

    order by

    sysusers.name, sysobjects.name

  • Sorry Topher, I cannot figure what you are actually asking for here.

    IS your script broken and you want help with it? If so, please tell us what is wrong with it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • If I could take a stab here, I don't think he/she is saying that script they posted is broken - I went through a similar undertaking recently, and script will return each user/object and the SELECT, INSERT, UPDATE, DELETE, EXECUTE permissions associated, but would need it expanded further to identify any user with the CREATE DATABASE permission, the CREATE TABLE permission, CREATE PROC permission, etc...

    In my endeavor, we were unable to come up with a place to look for that type of permissions information... if any one can solve that, I'd love to see the solution myself...

    "Got no time for the jibba jabba!"
    -B.A. Baracus

  • Kind of. It simple needs additional info. Or, simple a way to capture more data.

    But, if you know a script that is exactly the same as sp_helprotect, that would work too :).

    But, I need to capture additional things like "Create Database". I'm simple looking to mirror sp_helprotect's output from a script....

    For example, if I look at a user acct, I see 'grant select on object1', 'create table'. Right now, my script is only grabbing 'grant select on object1'

    Thx!

  • I can see where the info is coming from somewhat..but I'm failing to see a way to tie in everything to display results.

    select distinct b.name, a.action

    from sysprotects a, sysusers b

    where (a.action = 198

    or a.action = 203

    or a.action = 207

    or a.action = 222

    or a.action = 228

    or a.action = 223

    or a.action = 235

    or a.action = 236)

    and b.islogin = 1

    order by

    b.name

    26 = REFERENCES

    193 = SELECT

    195 = INSERT

    196 = DELETE

    197 = UPDATE

    198 = CREATE TABLE

    203 = CREATE DATABASE

    207 = CREATE VIEW

    222 = CREATE PROCEDURE

    224 = EXECUTE

    228 = BACKUP DATABASE

    233 = CREATE DEFAULT

    235 = BACKUP LOG

    236 = CREATE RULE

  • did you have a look at "http://www.sqlservercentral.com/scripts/Miscellaneous/30268/ ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Auhutens, to answer your Question

    select b.name,a.action, category =

    CASE a.action

    WHEN '198' then 'Create Table'

    WHEN '203' then 'Create Database'

    WHEN '207' then 'Create View'

    WHEN '222' then 'Create Procedure'

    WHEN '228' then 'Backup Database'

    WHEN '223' then 'Create Default'

    WHEN '235' then 'Backup Log'

    WHEN '236' then 'Create Rule'

    End

    from sysprotects a, sysusers b

    where (a.action = 198

    or a.action = 203

    or a.action = 207

    or a.action = 222

    or a.action = 228

    or a.action = 223

    or a.action = 235

    or a.action = 236)

    and b.islogin = 1

    order by

    b.name

  • Thanks, that'll help out

    "Got no time for the jibba jabba!"
    -B.A. Baracus

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

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