granting access to a database is done by creating a USER mapped to the login; then the user is placed in roles (or granted access directly to teh user, but that's not the best practice)

access to objects isn't given to a login, but instead to the USER that is tied tot eh login.

example:

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ClarkKent' AND type = 'S') --'S' = SQL login

BEGIN

--create our user

CREATE LOGIN [ClarkKent]

WITH PASSWORD=N'NotTheRealPassword',

DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=ON,

CHECK_POLICY=ON

--Make sure I'm in the correct database!

USE Dev;

GO

--create the Role for my Dev guys/role which runs DDL scripts

CREATE ROLE [Dev_Admins]

ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [Dev_Admins]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [Dev_Admins]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [Dev_Admins]

GRANT EXECUTE,ALTER TO [Dev_Admins]

--create role for my normal users

CREATE ROLE [DevUsers]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [DevUsers]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [DevUsers]

GRANT EXECUTE TO [DevUsers]

--create my user for the login

CREATE USER [ClarkKent] FOR LOGIN [ClarkKent]--make sure I'm in the correct database, and create some roles

--Add Clark to my normal user role:

EXEC sp_addrolemember N'DevUsers', N'ClarkKent'