A Question about SQL Server Security When Building Client-Server Applications.
I have a client-server application that uses SQL Server 2k as the backend. One of the things that I have done successfully is to require that all applications access the database via stored procedures. I don't have any grant permissions on any tables, views, or functions. I have already blogged about the problems with having a dbo as a user and enforcing row-level security, so none of my application user contexts are dbo's--at least until today.
I want members of a certain role to be able to manage which users are able to access the database via the application. I've built an ASP .NET Page that provides the UI for adding/removing users, setting their roles, etc.. The problem is that since my application-level 'admin' role is not in fact a dbo, it cannot execute the SQL 2k stored procedures to add/remove login/users to the database.
For now I've created a special login and added it to the securityadmin server role and set it up as a dbo to get around this problem. My question for the community is this: is there a way to allow my app to add and remove users using SQL Server Security and without knowing the login of a dbo, perhaps through a stored procedure? Can I create a role or something with those kind of permissions that is still not a dbo?