[AccessD] A2003: SQL Server - Create Users

Hi Rusty and David

Rusty - I never knew I could do this - now I'm impressed
David - I copied elements of your paste below and it worked first go

Thank you men folk - I just need to wrap this into an access screen with a
function or two and we are done

Many thanks again - I'm delighted :-)
 
Darren

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Tuesday, 8 June 2010 2:20 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com