Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: Add User/Set Roles in Code and Read Roles

    Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?

    I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.

    After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Look for following sprocs in BOL and sp_helptext sprocs in QA

    Might be a big help
    Code:
    sp_helprole
    sp_helprotect
    sp_helprolemember
    
    sp_addrole
    sp_addrolemember
    However something you might need to check out

    The Holy Book says:
    sp_addrole cannot be used inside a user-defined transaction.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    insert into sysusers values
    ('new_user_id', 0, 'new_role_name', NULL, 0x00, getdate(), getdate(), 'dbo', NULL)

  4. #4
    Join Date
    Mar 2003
    Posts
    130
    Originally posted by Enigma
    Look for following sprocs in BOL and sp_helptext sprocs in QA

    Might be a big help
    Code:
    sp_helprole
    sp_helprotect
    sp_helprolemember
    
    sp_addrole
    sp_addrolemember
    However something you might need to check out

    The Holy Book says:
    sp_addrole cannot be used inside a user-defined transaction.
    sp_grantlogin, sp_grantdbaccess, sp_addrolemember, and sp_helpuser worked great. thanks.

Posting Permissions

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