• how about creating a procedure that runs under elevated privilesges, and give the helpdesk permissions to that?

    here's just one example:

    CREATE procedure pr_CallBoostedSecurityProcess( @name varchar(128), @password varchar(128) )

    WITH EXECUTE AS 'superman'

    --this is a sysadmin login and also database user

    --(only database users can be used with EXECUTE AS clause in a proc),

    --which has been disabled, but is used for elevated permissions.

    AS

    BEGIN

    --CREATE a login, if required.

    DECLARE @cmd varchar(max)

    IF NOT EXISTS(SELECT 1 FROM master.sys.server_principals WHERE name = @name)

    BEGIN

    SELECT @cmd = 'CREATE LOGIN ' + QUOTENAME(@name)

    + ' WITH PASSWORD = ''' + @password +''','

    + 'N'' DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;'

    PRINT @cmd

    EXECUTE(@cmd)

    END --IF

    --Create Our role with specific permissions, if required.

    IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'DATABASE_ROLE' and name = 'MyStandardPermissions')

    BEGIN

    SELECT @cmd = 'CREATE ROLE MyStandardPermissions;'

    PRINT @cmd

    EXECUTE(@cmd)

    SELECT @cmd ='EXEC sp_addrolemember N''db_datareader'', N''MyStandardPermissions'';'

    PRINT @cmd

    EXECUTE(@cmd)

    SELECT @cmd ='EXEC sp_addrolemember N''db_datawriter'', N''MyStandardPermissions'';'

    PRINT @cmd

    EXECUTE(@cmd)

    END --IF

    --Create our user, if required

    IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'SQL_USER' and name = @name)

    BEGIN

    SELECT @cmd = 'CREATE USER ' + QUOTENAME(@name) + ' FOR LOGIN ' + QUOTENAME(@name) + ';'

    PRINT @cmd

    EXECUTE(@cmd)

    --assign the role to our user

    SELECT @cmd ='EXEC sp_addrolemember N''MyStandardPermissions'', N''' + @name + ''';'

    PRINT @cmd

    EXECUTE(@cmd)

    END --IF

    END --PROC

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!