Several stored procedures are available for you to use in maintaining user groups and permissions. Some of them will return useful information, for example, sp_helpgroup, which lists all of the groups in your database or all of the users in a particular group. Sometimes, however, no stored procedure is available that will return the specific information you require. There’s no stored procedure, for example, for listing logins that don’t have user names in your database. For these cases, I’ve provided an SQL SELECT statement that should do the job. My goal was to build a simple form (see Figure 12-4) that you could incorporate into your application to manage the database security. Assuming you’ve analyzed the user roles for the database, created the appropriate groups for these roles, and assigned permissions to the groups, the only day-to-day maintenance you’ll have to do is add and delete users and assign users to groups.
Figure 12-4 Simple administration using drag and drop
The form you incorporated into your application will have a ListView control that shows all logins in the database that don’t have a user name. These logins can be dragged and dropped into a TreeView control that shows all of the groups and the users in the groups. Users can also be dragged and dropped between the groups in the TreeView control or dragged back to the ListView control to revoke their access to the database.