September 26, 2007 at 2:59 pm
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.
September 26, 2007 at 3:57 pm
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
September 26, 2007 at 9:07 pm
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.
September 26, 2007 at 10:06 pm
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
Change is inevitable... Change for the better is not.
September 27, 2007 at 5:07 am
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
September 27, 2007 at 7:01 am
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.
September 28, 2007 at 2:02 am
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
September 28, 2007 at 9:29 am
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.
September 28, 2007 at 7:12 pm
Un-bloody-believable!
How could possibly a dude having no idea about binary get any job in IT industry???
_____________
Code for TallyGenerator
September 28, 2007 at 8:33 pm
I didn't have an idea about binaries untll about 15 months ago... this was not thaught in my school. Not for sql anyways.
September 29, 2007 at 10:22 pm
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
Change is inevitable... Change for the better is not.
September 30, 2007 at 9:04 am
You should send those in for the QOTD.
Can't wait to see the stats on those questions.
September 30, 2007 at 1:21 pm
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
September 30, 2007 at 3:22 pm
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.
September 30, 2007 at 4:28 pm
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