Storing an array of type string inside a database

I’m building a table of procedures, and I need to figure out a way so a ‘logged in user’ only has access to that procedure.

My initial thought is to store this information in a Certified column within the table like so 'JohnD', 'JasonD', 'JaneD'. Then I could only show that procedure if the user logged in is one of the usernames stored in the Certified column.

  • How to UnPivot for multiple columns SQLServer
  • Is a simple ASP.NET site using SQL Server known as 2-Tier Architecture?
  • Getting ranges that are not in database
  • SSRS 2008 R2 - Subscribe facility
  • Can I recreate a temp table after dropping it?
  • I cannot change start mode for SQL Server browser
  • I gather users from an Active Directory, so I don’t have a users table. I easily have access to their user name and full names etc.

    The procedures table will have lots of procedures, then my KPI table could have many KPI’s for each procedures. However I only want the person logged in to be able to view the KPI’s he is certified for.

    Am I going about this in a good way?

  • SQL Server: “a connection was successfully established with server…existing connection was forcibly closed by the remote host.”
  • check if found specific record then find another record with the same group
  • Error while changing the data type of the view column
  • SQL Left Outer join with where clause reduces results from left outer join
  • SQL Server migration to mySQL, tips & potential issues?
  • sql server - how to execute the second half of 'or' clause only when first one fails
  • 2 Solutions collect form web for “Storing an array of type string inside a database”

    This is a many-to-many relationship. Try this:

    Procedure table
      ... other columns in the procedure table
    User Table
      ... other columns in the user table
    User_to_Procedure table (this is the join table)

    put one row in the user_to_procedure table per combination of user-can-access-procedure.

    The user’s table is optional if you have another way to identify the user (like active directory). In that case of active directory, the user_id in the join table is the identifier you get from active directory.

    Here is a wikipedia link for join table. The wiki page refers to it as a junction table and lists join table as a synonym.

    Just because you’re getting users from the AD doesn’t mean you can’t have a user table, or a reference to a user in your procedures table. You can populate its records on demand, that is, when a user needs to access something related to procedures, you can check whether that user is authenticated, and then you create the relevant record on the fly.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.