Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2004
    Posts
    38

    Unanswered: SPROC for managing users

    I'm fairly new to SQL server, and I'm helping someone who has a fairly high employee turnover rate.

    I would like to create an interface that would allow her to add/edit/delete users and their permissions without having to get into management studio. I'll build a form in the front end application that will presumably pass parameters to a SProc that will do the work.

    Her users fall into two categories - full and read only. The tasks she'll need to perform are very simple.

    1) Add new user with full access to all tables
    2) Add new user with read only access to all tables
    3) Change user access from read only to full
    4) Change user access from full to read only
    3) Delete user

    Can someone point me in the right direction on this? Some sample code would be nice, but at the very least I'd like a better term to search by..

    Searching for "stored procedure user permissions" brings up tons of hits that mostly deal with who can and can't run a sproc..

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Fact 1: This would involve inserts, updates, and deletes to system tables.

    Fact 2: DML against system tables are bad things, unless you are fully cognizant of your actions and possible ramifications.

    Fact 3 derived from Fact 1 and Fact 2 ... This is a bad thing. If she isn't familiar with database administration practices and procedures, she needs to find someone who is. Experience is cheap, compared to the cost of lost or corrupted databases.

    Besides, if we give you the code to write it, and the system tables change (which Microsoft can and will do at will), how will you rewrite it to work, assuming it didn't hose up the system tables.

    On so many levels ... this is bad.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Don't you think this could be simply accomplished using system sprocs Tom without touching base tables?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    poots ... i value your input as always, but here we have a self professed noob trying to help a clueless user.
    If we give him the procs and he hoses something up, they are screwed.

    If M$oft later changes the underlying tables and comes up with new management procs, they are screwed.

    They need a trained professional, even if on a part time basis, IMHO.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I know from experience that writing a GUI to manage permissions in SQL Server is very complicated, and I would not recommend it to a newbie. I know because I fixed tons of bugs in exactly that area in SSMS.

    I recommend you use SSMS.

    That said, if you absolutely insist on doing this, I recommend that you use SMO to do it, and not direct sql calls. at least then you will be able to support both 2000 and 2005 with no extra work.

  6. #6
    Join Date
    Oct 2004
    Posts
    38
    Tom, I appreciate your post and respect your opinion, however it's kinda hard for me to believe that the only solution is to have a DBA visit every time they need to change permissions or add a new employee. (Remote access to their network is not an option.)

    Two questions come to mind..

    When/why would MS change the system tables? Seems like the only time would be when upgrading versions of SQL Server, and that's certainly not going to happen without my involvement.

    PF mentioned system sprocs to accomplish this.. Obviously, if there are system sprocs that accomplish this, that's what I want to use.

    If M$ updates the tables and comes up with new management procs, wouldn't I (at worst) just have to update the code my application uses to execute them?

  7. #7
    Join Date
    Oct 2004
    Posts
    38
    Jeze - by no means do I want to put together a full featured GUI.. All I need is something that will do the 5 tasks I outlined above. A few buttons, a textbox and a list box would be quite sufficient. Beyond those 5 tasks, I'll do everything in SSMS.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ok, then I recommend you check out these classes in SMO:

    User, Table, ObjectPermission (and related classes)

    http://msdn2.microsoft.com/en-us/lib...ement.smo.aspx

    For granting/revoking access on tables, etc: all securable objects have Grant/Deny/Revoke methods in SMO. For example, here are all the methods on the Table class, see in particular Grant/Deny/Revoke:

    http://msdn2.microsoft.com/en-us/lib...e_members.aspx

    If you use SMO, then you will automatically support both 2000 and 2005, as well as future versions (well, katmai at least, unless they drop support for SMO in katmai, which is VERY unlikely).

  9. #9
    Join Date
    Oct 2004
    Posts
    38
    I browsed through the system stored procedures in BOL, and it seems like I can do what I need by passing the appropriate arguments to sp_addlogin, sp_droplogin, sp_addrolemember, and sp_drop rolemember..

    Is there really that much risk using these to execute my 5 simple tasks?

  10. #10
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    I don't think it's nescesary to update system tables. Just create an sp that uses dynamic SQL (sp_executesql) to:

    1) CREATE/grant or DROP/revoke a login
    2) With a cursor loop through master..sysdatabases, next steps for each db found (except master, model, tempd and msdb)
    3) Grant db access
    4) Add to/remove from role db_owner or db_datareader

    Ofcourse the order in which to do these steps is dependent on what your doing (adding or removing)

    Syntax varies depending on
    - SQL 2000 or 2005
    - SQL logins or Windows accounts.

    Hint changing is best implemented by first removing and then adding the user again (saves work and a lot of IF-statements).

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    most of those procs are deprecated in 2005, and won't be supported in future versions.

    if you don't care about that, go for it.

    do yourself a favor though, and make sure this app uses windows authentication when it connects to sql server, and does NOT connect as SA with the password hardcoded into the app! I have seen this before believe it or not.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would never recommend effing about with system tables and agree 100% not to do that. I admit I did not know that the system sprocs were deprecated in 2005 so things are more complicated than I realised.

    Agreed that there are risks but my reading of the question was "my client needs to manage user access somehow. How can I best offer a controlled environment to do this?". Assuming that hiring a DBA is prohibitive then they either need to train someone up or come up with something else. IME the security will be "managed" somehow it is just a question of how well it will be managed. A custom GUI, though challenging, would be a step above someone blundering around in SQLMS with SA equivalent privileges but not the requisite knowledge. Assuming, of course, it is well written....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    yea, in 2005 the way to do this kind of stuff is with CREATE LOGIN/CREATE USER etc.

    of course, if you use SMO as I was pushing, it's all transparent, because SMO handles the version dependent stuff.

  14. #14
    Join Date
    Oct 2004
    Posts
    38
    This is a fresh install of SSE2005 with only about 20-25 users all using windows authentication.

    and pf is exactly right - Giving someone with almost no DB knowledge the keys to SSMSE scares me more than using some sprocs that will eventually need to be replaced.

    Thanks for the SMO idea jeze.. Getting that integrated into my app will probably require a little more work than building & passing some statements to the server, but it might pay off with a little more flexibility & later on. I'll research it a bit more and let you guys know which route I take..

    Thanks!

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Create a Windows group to add all the NT logins to, then give this group rights to access the database as above. This reduces all the permissions, and administration to membership in the group.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •