More newbie system table help

  • This kind of goes along with my previous post, so I thought I would try again. I am looking to make a link with a role, an object, and a user. Say I have a role called 'DataEntry'. I can say SELECT * FROM sysusers WHERE name = 'DataEntry' It seems the only useful piece of information there is the UID. So I guess my question is where can I look to find a table(s) of users and the roles to which they are assigned? Then I need to be able to find out the users that have permissions to an object. So in a nut shell I want to run a SQL command from Access when someone hits a command button. The code should look at their user_id, and be able to tell if they have permissions to execute a stored procedure. I don't need help with the code, but I do need help with where to look. Does any of this make sense, or am I rambling again?

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • users and roles are both in sysusers table

    you can use sp_helprole to get just the roles, or look in BOL to see what the other columns mean and you can use them to filter just users, or just roles, etc.

    you can also use sp_helprolemember to get all the roles and users that are in the role.

    you could then use the results to run sp_helprotect and see if they have execute on procedures. you can use this procedure to either pass a user or role and get object permissions back, or you can pass it the object to get the users/roles back that have permissions on the object. check BOL for more info on these useful stored procedures

    Hope this helps

  • Wow, Adam, I guess I am going to have to learn more about system procedures. The ones you have listed I think will take me a long way toward what I want to do. I will definitely post back in a few days to let you know how it turns out. Thanks so much for the tip.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Greg,

    Check for "System Stored Procedures, Overview" in the Index of Books Online. Gives a fairly large listing of "supported" system stored procedures. There are also some "extended" stored procedures that are also supported.

    There's a whole lot more that are not like xp_DirTree... 😉 With the correct operands, it's a powerful tool for finding files to import.

    Also, lookup "System Tables"... wealth of knowledge there.

    And... most of the system stored procedures live in the Master database... don't be afraid to open them up and take a peek at how the boys from Redmond did things.

    Last, but not least, learn how to use the F4 key in Query Analyzer to help you find things... don't be afraid... press it... 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Also, if you know specifically which objectname you want to check against

    (in this case exec on a proc) you could use the PERMISSIONS() function for that purpose

    use master

    declare @proc sysname

    set @proc = 'sp_help'

    if (select permissions(object_id(@proc)) &32) = 32

    begin

    print 'user has exec on ' + @proc

    end

    else

    begin

    print 'user has NOT exec on ' + @proc

    end

    /Kenneth

  • I feel like a kid in a candy store now. Thank you all so much for the valuable help. In BOL for PERMISSIONS() the following is listed and seems to be consistent with Kenneths post, and is *exactly* what I need to do:

    IF PERMISSIONS(OBJECT_ID('authors'))&8=8

    PRINT 'The current user can insert data into authors.'

    ELSE

    PRINT 'ERROR: The current user cannot insert data into authors.'

    I am unclear, however, on exactly what that is telling me. If I use 32 for execute, and the answer is 32 does that mean the user has execute permissions? I tried 31, and 33 after the & and the answers were 31 and 33 respectively. I tried all sorts of numbers after the & and there does not seem to be any consistency. Is there somewhere other the BOL that gives a more detailed explanation of what is going on?

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • PERMISSIONS return a number, and from that number we check if a certain bit is set or not.

    (That's what the bitwise operator & is used for)

    So, for execute proc permissions, if the object id is a proc, bit 6 needs to be turned on.

    Decimal 32 is 100000 in binary.

    select 100000 & 32 returns 32

    but select 100000 & 16 returns 0 because bit 5 isn't 'set'.

    You get the idea?

    What we can check for and which values are listed in BOL under 'permissions funtion'.

    It can vary a bit depending on which paremeters you give it.

    Also, if you're on SQL 2005, it's now flagged for deprecation, and they instead recommend new development to reference sys.fn_my_permissions instead for the same functionality.

    /Kenneth

  • Thanks again Kenneth. That stuff is a bit over my head for now. I have no idea what bitwise and hexidecimal and binary are, so I think it would not serve anyone well to try to explain it to me. I have found a wealth of stuff on google, so I guess if I want to get it I will have to start from scratch. The code you helped me with does work though, but I want to know why.

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Un-bloody-believable!

    How could possibly a dude having no idea about binary get any job in IT industry???

    _____________
    Code for TallyGenerator

  • I didn't have an idea about binaries untll about 15 months ago... this was not thaught in my school. Not for sql anyways.

  • Sergiy (9/28/2007)


    Un-bloody-believable!

    How could possibly a dude having no idea about binary get any job in IT industry???

    BWAAAAA-HAAAAAAA-HAAAAAA!!!! Oh, stop it! You're killing me!!! 😀 They don't even teach numbering systems in Computer Science or Mathematics anymore! Or so it would seem! They don't need to know that stuff anymore which is why bloat-ware is so prevalent nowadays! These are the same guys that think a flip-flop is when you change your mind :hehe:

    I can't tell you the number of self proclaimed "9 out of 10 in both Oracle and SQL Server" weenies I've interviewed that have no knowlege of bit-wise ops.

    I can't tell you the number of folks I've interviewed who have Master Degree's in computer science that don't know that a "full byte" is 255 or that a full byte had two different values depending on whether a sign bit was present or not.

    I can't tell you the number of folks I've interviewed who have Masters or PHDs in mathematics that couldn't tell me what bit will be set by 24 or what the value of 1416 is.

    Very few know the basics anymore... and you've seen many of them on these very forums. Why are you so surprised that someone doesn't know how to use the Binary numbering system? 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You should send those in for the QOTD.

    Can't wait to see the stats on those questions.

  • Jeff Moden (9/29/2007)


    I can't tell you the number of folks I've interviewed who have Masters or PHDs in mathematics that couldn't tell me what bit will be set by 24 or what the value of 1416 is.

    16 and E, if I'm not completely loosing it.

    I've got a query that does some job time calculations off MSDB and it's full of bitwise calcs (about 4 of them) There's not one other person on the IT floor at my company who understands that query. I hate to think what'll happen if they need to modify that and I'm not around....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sergiy (9/28/2007)


    Un-bloody-believable!

    How could possibly a dude having no idea about binary get any job in IT industry???

    Sergiy it is not as bad as it seems. Suffice it to say I am not in IT, but I do work for a company with deep pockets. I started with a split Access database, and simply asked my director if we could get SQL Server. I had no idea what that was, but I knew it was better than Access. So rather than do the right thing and hire a professional developer to take over where I left off, my company handed me a new server. I have been struggling to keep above water for the past year and a half, but now that I am at least floating I try to always learn new stuff. Training was not part of the package, so basically everything I know I learned from Jeff Moden on another forum. As I told Jeff some time ago, I am like someone who only needs a skateboard but whose parents bought me a Porsche.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Greg, it was not about you at all.

    It was about industry.

    Industry which is in deep sh*t.

    Machine computing is based on binary math. It's the way everything got processed in computers, any single operation is being translated into binary form and then done using binary math operations.

    To become a doctor you MUST know names of ALL bones in human body.

    Not to mention all other exams you must to pass.

    And nobody will take into account if they taught you in school or not.

    And nobody will take care about the way you've got to the point.

    You MUST KNOW - the only thing which counts.

    Even to become a hairdresser you must know essential chemistry, just not to burn customer's head with chemicals used in the industry.

    Every industry has it's own essential knowledge, educational base you must have to apply for a profession.

    Because there is a PROFESSIONAL STANDARD.

    What’s a professional standard for IT industry?

    What’s the educational census required to become a “computer guy”?

    I’m not aware of any.

    Don’t mention MS certification. It’s not even funny.

    Learning a user manual for the newest piece of medical equipment from GE would not help you get a position of a doctor. Why learning MS user manuals should help to get a developer position?

    So, there is a trouble:

    IT industry doesn’t have professional standards.

    That means that all of us working in IT,

    WE DON’T HAVE A PROFESSION.

    IT positions are filled with cowboys, smart talking frauds and quick-fingers hackers not having a clue about such things a design.

    That’s why we don’t have respect from other people who do have a profession.

    Even those who are really professional get disrespect, because people have no way to tell them from cowboys.

    That’s why there are plenty of stories like “What if houses would be built as programs”.

    It’s very sad indeed…

    :sick:

    _____________
    Code for TallyGenerator

Viewing 15 posts - 1 through 15 (of 26 total)

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