SQL Server: Creating a user database with permissions At work, I am tasked with creating a user database.  It needs to account for (end)user permissions for 8 different installed programs.  The database will be hosted by each client containing their specific information behind a firewall.  I have the majority of it built out (tables below), but I don't know how to incorporate permissions.

I could put all available permissions in a table, and then link them to users/groups and applications in another table.  Or I could put all available permissions in a table, then link them to users/groups in another table, and then applications in yet another table.  Problem is that both of these options don't play well with the applications because I cannot think of a way to actually search for a specific permission (ex: call to database to find if user can do 'x') without the programmer hard-coding the permission Ids into the program.

I could have a table per application with each of that application's permissions as a column, but there are 70+ permissions for each application and the tables would get huge (also not very expandable if we need to add more permissions later).

So the end questions is; How do I set up permissions for end-user accounts in a way that the programmer does not have to know and hard-code the Ids into the applications?

Table - Description


Applications - Basically a list of our applications so we have an Id to link the permissions to
GlobalSettings - Settings used for all users, mostly password requirments
Groups - Lists all end-user administrator defined groups of users (this way a permission can be tied to a group of users at one time)
PreviousPasswords - Lists the last x hashed passwords for each user (the ones they can't use again)
UnusablePasswords - Lists passwords we won't let our end-users use (such as password1)
User_Group_Link - Links a User to a Group
Users - Lists all end-users and their needed data (birthday, name, etc)
Some Ideas (Anyone: please feel free to Edit/Add)
ApplicationPermissions (Application Level Permissions)

ApplicationID
PermissionID
Allow (Y/N/1/0)
Value (As in a Max Number of &quot;X&quot; allowed)
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID

User_Application_Permissions (User Level Permissions)

UserID
ApplicationID
Allow (Y/N/1/0)
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID

UserPermissions (Descriptive/Attribute Table)

PermissionID
PermissionDescription
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID

Users (Descriptive/Attribute Table)

UserID
UserName
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID