convert SQL to stored procedure

• ksexton

  SSCrazy

  Points: 2790

  More actions

  June 3, 2004 at 11:17 am

  #180377

  I need help turning these SQL statements into a stored procedure: it creates a sql user account, grants it access, and adds it to a role. I need the procedure to accept the username and password as input paramenters.

   

  if not exists (select * from [dca-siebdb].master.dbo.syslogins where loginname = N'delete_me5')

  BEGIN

             

  declare

  @logindb nvarchar(132),

  @loginlang nvarchar(132) select @logindb = N'workgrps',

  @loginlang = N'us_english'

             

  if @logindb is null or not exists (select * from [dca-siebdb].master.dbo.sysdatabases where name = @logindb)

                          select @logindb = N'master'

              if @loginlang is null or (not exists (select * from [dca-siebdb].master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')

                          select @loginlang = @@language

              exec [dca-siebdb].master.dbo.sp_addlogin N'delete_me_5', null, @logindb, @loginlang

  END

  GO

  if not exists (select * from [dca-siebdb].workgrps.dbo.sysusers where name = N'delete_me_5' and uid < 16382)

              EXEC [dca-siebdb].workgrps.dbo.sp_grantdbaccess N'delete_me_5', N'delete_me_5'

  GO

  exec [dca-siebdb].workgrps.dbo.sp_addrolemember N'SSE_ROLE', N'delete_me_5'

  GO

• Nicholas Cain

  SSC-Dedicated

  Points: 33009

  More actions

  June 3, 2004 at 11:39 am

  #508878

  Something like this?

  ---

  CREATE PROCEDURE ADD_LOGINS @USER VARCHAR(20), @PWD VARCHAR(20)

  AS

  if not exists (select * from [dca-siebdb].master.dbo.syslogins where loginname = @USER)

  BEGIN

   declare

   @logindb nvarchar(132),

   @loginlang nvarchar(132) select @logindb = N'workgrps',

   @loginlang = N'us_english'

    if @logindb is null or not exists (select * from [dca-siebdb].master.dbo.sysdatabases where name = @logindb)

                          select @logindb = N'master'

              if @loginlang is null or (not exists (select * from [dca-siebdb].master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')

                          select @loginlang = @@language

              exec [dca-siebdb].master.dbo.sp_addlogin @USER,@PWD, @logindb, @loginlang

  END

  if not exists (select * from [dca-siebdb].workgrps.dbo.sysusers where name = @USER and uid < 16382)

              EXEC [dca-siebdb].workgrps.dbo.sp_grantdbaccess @USER,@USER

    exec [dca-siebdb].workgrps.dbo.sp_addrolemember N'SSE_ROLE', @USER

  GO

  ---

  
  Shamless self promotion - read my blog http://sirsql.net

• ksexton

  SSCrazy

  Points: 2790

  More actions

  June 3, 2004 at 12:03 pm

  #508884

  Thanks- I thought I could figure out the whole thing if I was hsown part of it. But I was wrong.

  Here is the other piece I need to incorporate into the proc. (basically creates the same login in a diff. db on a different server)

  if not exists (select * from [dca-05].master.dbo.syslogins where loginname = N'delete_me_5')

  BEGIN

   declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'

   if @logindb is null or not exists (select * from [dca-05].master.dbo.sysdatabases where name = @logindb)

    select @logindb = N'master'

   if @loginlang is null or (not exists (select * from [dca-05].master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')

    select @loginlang = @@language

   exec [dca-05].master.dbo.sp_addlogin N'delete_me_5', null, @logindb, @loginlang

  END

  GO

  if not exists (select * from [dca-05].TLG.dbo.sysusers where name = N'delete_me_5' and uid < 16382)

   EXEC [dca-05].TLG.dbo.sp_grantdbaccess N'delete_me_5', N'delete_me_5'

  GO

  exec [dca-05].TLG.dbo.sp_addrolemember N'Users', N'delete_me_5'

  GO