jwcolby
jwcolby at colbyconsulting.com
Tue Jun 8 13:47:04 CDT 2010
And what about us womenfolk? ;) John W. Colby www.ColbyConsulting.com Darren - Active Billing wrote: > 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 >> >