That is true. I am already able to create the login, but cannot grant connect to the database....It says the login is not available or you do not have permission...
----------------------CREATE LOGIN-----------------------------------------------
declare @SQL_DBvarchar(20),
@STRSQL1varchar(500),
@STRSQL2varchar(500),
@STRSQL3varchar(500),
@STRSQL4varchar(500)
SET @SQL_DB='USE '+@DATABASE_NAME
SET @STRSQL1=(' IF NOT EXISTS (SELECT *
FROM sys.server_principals
WHERE name='''+@DB_USER+''')')
SET @STRSQL2=(' BEGIN
CREATE LOGIN '+@DB_USER+'
WITH PASSWORD=N'''+@DB_USER+''',
DEFAULT_DATABASE=['+@DATABASE_NAME+'],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
END')
SET @STRSQL3=(' EXEC sys.sp_addsrvrolemember @loginame = N'''+@DB_USER+''', @rolename = N''sysadmin''')
SET @STRSQL4=(' ALTER LOGIN ['+@DB_USER+'] DISABLE')
EXEC (@SQL_DB+@STRSQL1+@STRSQL2+@STRSQL3+@STRSQL4)
------------------------CREATE USER----------------------------------------------
SET @STRSQL1=(' IF NOT EXISTS (SELECT *
FROM '+@DATABASE_NAME+'.sys.server_principals
WHERE name='''+@DB_USER+''')')
SET @STRSQL2=(' CREATE USER ['+@DB_USER+']
FOR LOGIN ['+@DB_USER+']
WITH DEFAULT_SCHEMA=[dbo]')
EXEC (@SQL_DB+@STRSQL1+@STRSQL2+@STRSQL3)
SET @STRSQL1=(' GRANT CONNECT TO ['+@DB_USER+']')
EXEC (@SQL_DB+@STRSQL1)
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.