Create SQL Server Login

1. Local / Domain User (Windows Authentication)

USE master

EXEC sp_grantlogin '{Server Name}\{User Name}'

USE {Database Name}
GO

EXEC sp_grantdbaccess '{Server Name}\{User Name}'
GO

ALTER ROLE db_datareader ADD MEMBER [{Server Name}\{User Name}]
GO

ALTER ROLE db_datawriter ADD MEMBER [{Server Name}\{User Name}]
GO

2. IIS Application Pool (Windows Authentication)

USE master

EXEC sp_grantlogin 'IIS APPPOOL\{Application Pool Name}'

USE {Database Name}
GO

EXEC sp_grantdbaccess 'IIS APPPOOL\{Application Pool Name}'
GO

ALTER ROLE db_datareader ADD MEMBER [IIS APPPOOL\{Application Pool Name}]
GO

ALTER ROLE db_datawriter ADD MEMBER [IIS APPPOOL\{Application Pool Name}]
GO

3. SQL Server Login (SQL Authentication)

USE master

CREATE LOGIN {Login Name} WITH PASSWORD = 'Pass1234';

USE {Database Name}
GO

CREATE USER {Login Name} FOR LOGIN {Login Name};

ALTER ROLE db_datareader ADD MEMBER [{Login Name}]
GO

ALTER ROLE db_datawriter ADD MEMBER [{Login Name}]
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s