declare @sqlMajor int
declare @sqlMinor int

declare @sidSearch varbinary(85); SELECT @sidSearch = 0xEC4FF58CD326CA449052AB56E61071FD
declare @sidString varchar(34); SELECT @sidString = N'0xEC4FF58CD326CA449052AB56E61071FD'
declare @nameSearch nvarchar(128); SELECT @nameSearch = N'atm'
declare @userSearch nvarchar(128); SELECT @userSearch = N'atm'
declare @pwdSearch nvarchar(128); SELECT @pwdSearch = N't1m3'

declare @sidAccount varbinary(85)
declare @nameAccount nvarchar(128)
declare @nameDB nvarchar(128)
declare @nameAlias nvarchar(128)
declare @execStmt nvarchar(4000)

-- first determine the version of SQL server, so that we can do
-- things differently when creating accounts

set nocount on
CREATE TABLE #msVersion (
	[Index] tinyint not null,
	[Name] varchar(50) not null,
	[Value] int null,
	[String] varchar(255) null
)

INSERT INTO #msVersion EXEC [master]..[xp_msver]

select @sqlMajor = [Value] / 0x10000, @sqlMinor = [Value] & 0xFFFF
from #msVersion
where [Name] = N'ProductVersion'

DROP TABLE #msVersion

-- use the @sidSearch and @nameSearch variables to locate the
-- system login from the syslogins table that matches the
-- login that is searched for. This will first match the SID,
-- then the account name.

SELECT @sidAccount = [sid], @nameAccount = [loginname]
FROM [master].[dbo].[syslogins]
WHERE [sid] = @sidSearch

IF @sidAccount IS NULL
	SELECT @sidAccount = [sid], @nameAccount = [loginname]
	FROM [master].[dbo].[syslogins]
	WHERE [loginname] = @nameSearch

-- if at this point, no account exists, we'll create one with
-- the supplied SID and name

IF @sidAccount IS NULL 
BEGIN

	-- create the account and associate with the current database
	-- as its default database

	select @nameDB = DB_NAME()

	-- if this is SQL 2005 or later, we need to use the CREATE LOGIN
	-- T-SQL statement to create the login...

	IF (@sqlMajor >= 9)
	BEGIN

		-- we cannot include the CREATE LOGIN T-SQL directly in the
		-- batch as it will fail on SQL 2000, so we have to generate
		-- a dynamic statement and execute it indirectly

		select @execStmt = N'CREATE LOGIN ' + QUOTENAME(@nameSearch) +
				   N' WITH PASSWORD = N' + QUOTENAME(@pwdSearch, N'''') + N',' +
				   N' SID = ' + @sidString + N',' +
				   N' DEFAULT_DATABASE = ' + QUOTENAME(@nameDB) + N',' +
		                   N' CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF'

		execute (@execStmt)

	END ELSE
	BEGIN

		exec sp_addlogin @loginame = @nameSearch, @passwd = @pwdSearch, @defdb = @nameDB, @sid = @sidSearch

	END

	-- update the @sidAccount and @nameAccount variables with the
	-- correct information..

	SELECT @sidAccount = [sid], @nameAccount = [loginname]
	FROM [master].[dbo].[syslogins]
	WHERE [sid] = @sidSearch

END

-- if the login is aliased to a user, then we'll remove that alias now..

if exists (select [sid] from [dbo].[sysusers] where [sid] = @sidAccount and [isaliased] = 1)
	exec sp_dropalias @loginame = @nameAccount
if exists (select [sid] from [dbo].[sysusers] where [name] = @nameAccount and [isaliased] = 1)
	exec sp_dropalias @loginame = @nameAccount

-- if this login is associated with a user in the database, then we need to
-- drop the existing user association

select @nameAlias = [name] from [dbo].[sysusers] where [sid] = @sidAccount
if @nameAlias is not null
	exec sp_dropuser @name_in_db = @nameAlias

-- if a user exists with the same name as the user we are going to create, then
-- we must drop the existing user first

if exists (select [sid] from [dbo].[sysusers] where ([name] = @userSearch) and ([issqluser] = 1))
	exec sp_dropuser @name_in_db = @userSearch

-- the AttendanceLogin/AttendanceUser has access only to the Ae_login stored
-- procedure, otherwise it is denied all access to all tables and stored 
-- procedures

exec sp_grantdbaccess @loginame = @nameAccount, @name_in_db = @userSearch
exec sp_addrolemember @rolename = 'db_denydatawriter', @membername = @userSearch

-- finally, grant sysadmin permissions to the atm account

exec sp_addsrvrolemember 'atm', 'sysadmin'