question

Bugmesh avatar image
Bugmesh asked

Passing parameters to sp_add_user - multiple databases

In the procedure below I am creating a process that can be run by the helpdesk when a request for a new user comes in. Since this is very repetitious, I thought to pass parameters to: 1. Create the Login 2. Add that new user to 3 different databases 3. Add that user to 2 different DB roles in those databases 4. Insert that user information into additional tables THE PROBLEM: sp_adduser only allows for: sp_adduser -login-, -pwd-, -role- **SQL Server Assumes the current database How do I intelligently identify the DB that the sp_add_user should be using since you cannot put a USE clause inside of a Stored Procedure? I appreciate your anticipated assistance USE [Special_DB] GO /****** Object: StoredProcedure [dbo].[usp_addstarsuser] Script Date: 10/23/2014 1:51:37 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_addstarsuser] (@login varchar(25), @password varchar(25), @description varchar(25), @Firstname varchar(25), @Lastname varchar(25)) AS Declare @db varchar(25) Declare @db1 varchar(25) Declare @db2 varchar(25) Declare @role1 varchar(25) Declare @role2 varchar(25) Declare @Person_ID int Declare @userame varchar(25) Declare @user_name varchar(25) Declare @name varchar(25) set @login = @login set @password = @password set @db = 'OPACBILLING' set @db1 = 'Conversion_Staging_DB' set @db2 = 'Payment_DB' set @role1 = 'STANDARD' set @role2 = 'starsuser' set @userame = @login set @user_name = @login set @name = @login set @description = @description set @Firstname = @Firstname set @Lastname = @Lastname exec sp_addlogin @login, @password, @db exec sp_adduser @login, @userame, @role1 exec sp_adduser @login, @userame, @role2 exec sp_adduser @login, @userame, @role1 exec sp_adduser @login, @userame, @role2 exec sp_adduser @login, @userame, @role1 exec sp_adduser @login, @userame, @role2 CREATE TABLE #NewStarsUser( group_name varchar(6) Default 'stars12', [user_name] varchar(25), name varchar(25), username varchar(25), Firstname varchar(25), Lastname varchar(25), [description] varchar(25), Person_ID int , SuspenseUser char(1) Default 'Y', [priority] int Default '0', user_type int Default '0' ) Select group_name, [user_name] INTO OPACSECURITY..security_groupings from #NewStarsUser Select name,[description],[priority],user_type INTO OPACSECURITY..security_users from #NewStarsUser Begin Select Firstname, Lastname INTO OPACPENDING..Person from #NewStarsUser End set @Person_ID = (select person_ID from OPACPENDING..Person where Firstname = @Firstname and Lastname =@Lastname) Select @person_ID, UserName, SuspenseUser INTO OPACPENDING..Users from #NewStarsUser
parametersuseradd
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thomas, That appears to have done the trick I appreciate it !
0 Likes 0 ·
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. Sounds like @DenisT had the answer, so if you can mark it please if it's right.
0 Likes 0 ·
DenisT avatar image
DenisT answered
You can try using EXEC [DB_NAME].[SCHEMA_NAME].sp_adduser. Should do the trick.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bugmesh avatar image
Bugmesh answered
This is working now and in case you wanted to see the final version, here it is: USE [Special_DB] GO /****** Object: StoredProcedure [dbo].[usp_addstarsuser] Script Date: 10/27/2014 1:53:26 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_addstarsuser] (@login varchar(25), @password varchar(25), @description varchar(25), @Firstname varchar(25), @Lastname varchar(25)) AS /* Create a temp table to hold values that will be moved into multiple perm table */ CREATE TABLE #NewStarsUser( group_name varchar(25), [user_name] varchar(25), name varchar(25), username varchar(25), Firstname varchar(25), Lastname varchar(25), [description] varchar(25), Person_ID int , SuspenseUser char(1), [priority] int, user_type int) /* Declare all the variables used in the procedure*/ Declare @db varchar(25) Declare @db1 varchar(25) Declare @db2 varchar(25) Declare @role1 varchar(25) Declare @role2 varchar(25) Declare @Person_ID int Declare @userame varchar(25) Declare @user_name varchar(25) Declare @name varchar(25) Declare @group_name varchar(25) Declare @SuspenseUser char(1) Declare @priority int Declare @user_type int /* Set values that will be inserted by the variables */ set @login = @login set @password = @password set @db = 'OPACBILLING' set @db1 = 'Conversion_Staging_DB' set @db2 = 'Payment_DB' set @role1 = 'STANDARD' set @role2 = 'starsuser' set @userame = @login set @user_name = @login set @name = @login set @description = @description set @Firstname = @Firstname set @Lastname = @Lastname set @group_name = 'star12' set @SuspenseUser = 'Y' set @priority ='0' set @user_type ='0' /* This will create the login, add the user to Conversion_Staging_DB, OPACBILLING, and Payment_DB add the user to the Standard role and then to the staruser role */ exec sp_addlogin @login, @password, @db exec CONVERSION_STAGING_DB.dbo.sp_adduser @login, @userame, @role1 exec CONVERSION_STAGING_DB.dbo.sp_addrolemember @role2, @userame exec OPACBILLING.dbo.sp_adduser @login, @userame, @role1 exec OPACBILLING.dbo.sp_addrolemember @role2, @userame exec Payment_DB.dbo.sp_adduser @login, @userame, @role1 exec Payment_DB.dbo.sp_addrolemember @role2, @userame /* Populate the temp table */ Insert Into #NewStarsUser (group_name, [user_name],name,username,Firstname,Lastname,[description],SuspenseUser,[priority],user_type ) Values (@group_name,@user_name,@name,@userame,@Firstname,@Lastname,@description,@SuspenseUser,@priority,@user_type) /* Add the user information to the OPACSECURITY and OPACPENDING table */ Insert INTO OPACSECURITY.dbo.security_groupings (group_name, [user_name]) Values (@group_name,@user_name) Insert INTO OPACSECURITY.dbo.security_users (name,[description],[priority],user_type) Values (@name,@description,@priority,@user_type) Insert INTO OPACPENDING.dbo.Person (Firstname,Lastname) Values (@Firstname,@Lastname) Declare @username varchar(25) set @Person_ID = (select person_ID from OPACPENDING.dbo.Person where Firstname = @Firstname and Lastname =@Lastname) --set @username = @username Insert Into #NewStarsUser (Person_id) Values (@Person_id) Insert Into OPACPENDING.dbo.Users (person_ID, UserName, SuspenseUser ) VALUES (@person_ID, @FirstName, @SuspenseUser ) drop table #NewStarsUser GO
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.