Login/User modification from an application?

I have an application and I want to add/edit/del Logins and Users in Sql
Server 2005 through the application, can I do it? I am using windows
authentication. Currently I call a stored proc that I try to pass a login
and it doesn't work. For example this is my Delete.
ALTER PROCEDURE [dbo].[usp_UserRoleDelete]
-- Add the parameters for the stored procedure here
@.LoginName nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
Begin
--Drop the login which drops the user?
Drop Login [@.LoginName]
End
END
When it runs I get you can't add a login called @.LoginName. If I take the
brackets off it will not compile. Can i do what I'm trying to do? I've als
o
tried using sp_DropLogin w/o success. I did see somewhere in documentation
for the sp_dropLogin "sp_droplogin cannot be executed within a user-defined
transaction." There is nothing about the in the Drop Login documentation.
Is this the problem?
Thanks in advance,
Greg P.Just to follow up, mainly what I'm trying to do is used passed parameters in
my add/alter/drop statements. Is there a way to have the @.LoginName
evaluated before the the whole line is evaluated?
"Greg P." wrote:

> I have an application and I want to add/edit/del Logins and Users in Sql
> Server 2005 through the application, can I do it? I am using windows
> authentication. Currently I call a stored proc that I try to pass a login
> and it doesn't work. For example this is my Delete.
> ALTER PROCEDURE [dbo].[usp_UserRoleDelete]
> -- Add the parameters for the stored procedure here
> @.LoginName nvarchar(50)
> AS
> BEGIN
> SET NOCOUNT ON;
> Begin
> --Drop the login which drops the user?
> Drop Login [@.LoginName]
> End
> END
> When it runs I get you can't add a login called @.LoginName. If I take the
> brackets off it will not compile. Can i do what I'm trying to do? I've a
lso
> tried using sp_DropLogin w/o success. I did see somewhere in documentatio
n
> for the sp_dropLogin "sp_droplogin cannot be executed within a user-define
d
> transaction." There is nothing about the in the Drop Login documentation.
> Is this the problem?
> Thanks in advance,
> Greg P.|||No...it's because DROP LOGIN itself doesn't accept
parameters and that's what you are asking it to do. You
would need to use dynamic SQL but you really would want to
read the following first:
http://www.sommarskog.se/dynamic_sql.html
So for the stored procedure you posted to work, one option
would be to dynamically build the statement and pass it into
an EXEC.
So instead of the line:
Drop Login [@.LoginName]
you would use something like:
EXEC('DROP LOGIN ' + @.LoginName)
-Sue
On Mon, 25 Sep 2006 13:31:01 -0700, Greg P.
<GregP@.discussions.microsoft.com> wrote:

>I have an application and I want to add/edit/del Logins and Users in Sql
>Server 2005 through the application, can I do it? I am using windows
>authentication. Currently I call a stored proc that I try to pass a login
>and it doesn't work. For example this is my Delete.
>ALTER PROCEDURE [dbo].[usp_UserRoleDelete]
> -- Add the parameters for the stored procedure here
> @.LoginName nvarchar(50)
>AS
>BEGIN
> SET NOCOUNT ON;
> Begin
> --Drop the login which drops the user?
> Drop Login [@.LoginName]
> End
>END
>When it runs I get you can't add a login called @.LoginName. If I take the
>brackets off it will not compile. Can i do what I'm trying to do? I've al
so
>tried using sp_DropLogin w/o success. I did see somewhere in documentation
>for the sp_dropLogin "sp_droplogin cannot be executed within a user-defined
>transaction." There is nothing about the in the Drop Login documentation.
>Is this the problem?
>Thanks in advance,
>Greg P.