Stored procedures to wrap user administration

neilsolent () writes:

I want to create a stored procedure to add users to my database (plus
delete, modify, etc .. later).

The reason I want to do this is to audit user creation. If the users
that run the sp don't have admin rights, they can't run the built-in
procedures like sp_add_login. They have to run this custom sp, which
automatically adds rows to an audit table.

The problem I have is, if sp_add_login etc is simply coded into the
custom sp, it doesn't seem to elevate the access correclty.

Has anyone done this before, got any tips?

First of all, this is unclear. You first talk about database users, but
then you mention sp_addlogin which adds a login to the server, which is
a little different.

In any case, you should use CREATE USER, CREATE LOGIN etc.

The way do this is certificate signing. You create a user from the
certificate which you grant the required permissions. For server-level
affairs, you also need to have the certificate in master.

I describe this in detail in this article on my web site:
http://www.sommarskog.se/grantperm.html

Erland Sommarskog, SQL Server MVP,

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sql...14207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sql...95970.aspx