Hi guys,
Sorry for never giving feedback here but work has been hectic taking all my free time but no, sorry Brian I could not get your script going and was strapped for time at the time and got a different script and here it is.
declare @userid sysname = 'jon', @password sysname = 'password', @sqlstr nvarchar(250)
exec sp_addlogin @loginame = @userid,
@passwd = @password,
@defdb = 'ToolboxDB',
@deflanguage = [British],
@sid = null,
@encryptopt= null
EXEC sys.sp_addsrvrolemember @userid, @rolename = N'sysadmin'
USE [ToolboxDB]
set @sqlstr = 'CREATE USER '+@userid+' FOR LOGIN '+@userid
EXECUTE sp_executesql @sqlstr
Allow me to explain. The top part creates a login in sql server (not the database). You will see a security folder just below the database folder as follows:
exec sp_addlogin @loginame = @userid,
@passwd = @password,
@defdb = 'ToolboxDB',
@deflanguage = [British],
@sid = null,
@encryptopt= null
The following part adds the login to a server role. I have sysadmin here but you can change that according to your own needs.
EXEC sys.sp_addsrvrolemember @userid, @rolename = N'sysadmin'
You can also add it to more than one role if needed. The following part maps your login to a specific database and here you can also use more than one database just put the code in for each database.
USE [ToolboxDB]
set @sqlstr = 'CREATE USER '+@userid+' FOR LOGIN '+@userid
EXECUTE sp_executesql @sqlstr
Check out the link below for more on sp_executesql but the reason why I used this is because when I used Brian's script I kept getting "Cannot find the stored procedure ..........".
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)