[AccessD] A2003: SQL Server - Create Users

I do this for one of my databases:

CREATE PROCEDURE [dbo].[stpAddUser](@uid AS VARCHAR(36), @pwd AS VARCHAR(36)) AS
/*
--Debug/Test variables
DECLARE @uid AS VARCHAR(36)
DECLARE @pwd AS VARCHAR(36)

SET @uid = 'dmcafee1'
SET @pwd = 'testPW1'
*/


IF NOT EXISTS (select * from master.dbo.syslogins where loginname = @uid)
	BEGIN
--		Print 'Login does not exist, create login:'

		EXEC sp_addLogin @uid, @pwd, 'YOURSQLDBNAMEHERE'
-- 		PRINT 'EXEC sp_addLogin ' + @uid + ', ' + @pwd + ', YOURSQLDBNAMEHERE'

		EXEC sp_grantdbaccess @uid, @uid
--		PRINT 'sp_grantdbaccess ' + @uid + ', ' + @uid

		EXEC sp_addrolemember N'MomsUpdate', @uid
--		PRINT 'EXEC sp_addrolemember YourRoleNameHere, ' + @uid

	END

--To delete
--EXEC YOURSQLDBNAMEHERE.dbo.sp_DropUser '00046'







On Sun, Jun 6, 2010 at 11:02 PM, Darren - Active Billing
<darren at activebilling.com.au> wrote:
> Hi team
>
>
>
> In my Access 2003 app connecting to a 2005 SQL Server
>
> I want to
>
> 1        Create a user (This I can do) with a PWD (This I can do)
> I also want to
> 2        Add this user to a 'default' database
>
> And
>
> 3        Assign them to 3 already configured roles on this default dB
>
> Make sense?
>
> This is all I have so far (Part 1)
>
>
>
> use SOMEDB
>
> exec sp_droplogin Me.txtLoginName
>
> exec sp_addlogin Me.txtLoginName, Me.txtLoginPassword
>
>
>
> This will drop and create the user with password
> Anyone got any suggestions for the other 2 bits?
>
> Many thanks
>
> Darren
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>