How to enable a user using a script?

Question

I create a user with CREATE LOGIN...

sp_grantdbaccess does not seem to enable the user

How can I enable a user using a script?

UPDATE: GRANT CONNECT does not seem to work, maybe I wasn't clear enough, This is a SQL user (not Windows user) on SQL Server 2014, I'm looking at the main "Security-->Logins" tab that is common to all databases...

enter image description here

• choudhury smrutiranjan parida: What the error you are getting? Pls follow the link here: mssqltips.com/sqlservertip/2038/…
• Konrad Gajewski: Can you be more specific?
• Andriy M: You seem to be confusing two different concepts: server logins and database users. You may want to take a look at this section of the manual: Principals (Database Engine)

Answer 1

if you want to access a database using a login you created, you need some decisions to make, first : you have to create a user in database for the login you created, it could be a same name or a different name for it. you also need to decide what amount of permissions you need for the login,

the sample below is for creating a login with a specific password, that is going to access adventureworks2012 with db_datareader permission over database:

USE [master] 
GO 
CREATE LOGIN [test] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO 
USE [AdventureWorks2012] 
GO 
CREATE USER [test] FOR LOGIN [test] 
GO 
USE [AdventureWorks2012] 
GO 
ALTER ROLE [db_datareader] ADD MEMBER [test] GO

Answer 2

There is few steps to Enable Database Users though TSQL

step 1

Use databasename
 GO
 SELECT 
 principal_id AS [Principal ID]
 ,name AS [Database User Name]
 ,type AS [User Type]
 ,type_desc AS [User Type Description]
 FROM sys.database_principals 
 WHERE TYPE IN ('G','U','S')
 ORDER BY type ASC
 GO

Example:-It shows all user in database and the output will be like that .The output will depend on your system configuration.

Principal ID Database User Name User Type User Type Description
1 dbo S SQL_User 
4 sys s SQL_User
8 INTRANET\DBAdmins G WINDOWS_GROUP
9 INTRANET\TMAdmin U WINDOWS_USER
where 
 S = SQL_USER
 U = WINDOWS_USER
 G = WINDOWS_GROUP

step 2

You can find all the users within the database which are disabled either by using SSMS or using TSQL.

USE databasename
 GO
 SELECT 
 SU.NAME
 ,DP.principal_id
 ,dp.type
 ,dp.type_desc
 ,su.hasdbaccess
 FROM sys.database_principals DP
 INNER JOIN sys.sysusers SU ON dp.principal_id = SU.uid
 WHERE DP.TYPE IN ('G','U','S')
 AND SU.hasdbaccess = 0
 ORDER BY DP.TYPE ASC
 GO

for example The output comes like this way

NAME principal_id type type_desc hasdbaccess
INFORMATION_SCHEMA 3 S SQL_USER 0
 sys 4 S SQL_USER 0

where hasdbaccess=0

Step 3

To enable all Users who are disabled within the user database

Use databasename
 GO
 SELECT 'GRANT CONNECT TO [' + SU.name + '];' FROM sys.database_principals DP
 INNER JOIN sys.sysusers SU ON dp.principal_id = SU.uid
 WHERE DP.TYPE IN ('G','U')
 AND SU.hasdbaccess = 1
 GO
Sample Output
 
GRANT CONNECT TO [Domain\User1];
 GRANT CONNECT TO [Domain\AdminGroup];

Open a New Query window and run the above script under the context of the database to GRANT CONNECT to user and to resolve the issue.

for your further ref :- http://www.mytechmantra.com/LearnSQLServer/How-to-Enable-Database-Users-in-SQL-Server/