SQL Logins and SQL Users

Code snippets for creating new LOGINS and USERS.

Remember LOGINS are at the server/instance level and USERS are at the database level. In most cases the LOGIN should be created BEFORE the USER.

Permission conflicts: OWNER permissions trump DENY and DENY trumps GRANT.

LOGINS

-- CREATE LOGIN  (At server / instnace level)
use master;
create login [Domain\User] from windows;
 
create login [MySqlLogin] WITH PASSWORD=N'1234' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;      -- Check and enforce AD rules

-- CHANGE PASSWORD
USE [master];
ALTER LOGIN [MyUser] WITH PASSWORD=N'MyNewPassword';

--SERVER LEVEL PERMISSION
Grant view server, control server to [MyLogin];

--RENAME SA ACCOUNT
ALTER LOGIN sa WITH NAME = [SaNewName];  

--GROUP PERMISSIONS
use master;
create login [Domain\MyAdGroup] from windows;

-- ROLES at SERVER LEVEL
Create server role [MyNewRole]; 
Alter server role [MyNewRole] add MEMBER [MyDomain\Name];

USERS

Mapped User	Created based on a SQL Server login.   CREATE USER [MyLogin] FOR LOGIN [MyLogin] WITH DEFAULT_SCHEMA = [dbo]; ( Will use dbo schema as default, if not specified)
Unmapped User	Users created without a SQL Server login, (For Signing Code, Execute As)   CREATE USER [test] WITHOUT LOGIN   EXECUTE AS USER = ‘MyLogin’; EXEC sp1; REVERT;
Contained User	As UserName/Password but not a SQL Server Login Configure: Instance/Properties/Advanced/Enable Contained db=true Database node/Properties/Options/DropDown: Containment Type = Partial   USE [MyDatabase]; CREATE USER [test] WITH PASSWORD=N’Pass1′, DEFUALT_SCHEMA=[dbo];
Refresh User	Alter user MyUserName with login = MyUserName;

In my situation I typically add users to a GROUP in MSAD (Active Directory) and then add the GROUP as a login and user to the database.

use mydatabase;
create user [Domain\MyAdGroup] from login [Domain\MyAdGroup];

-- Alter, reset, reconnect user at database level.
alter user MyUserName with login = MyUserName;

Roles

sp_helpdbfixedrole;
sp_addrolemember 'db_datareader', 'MyDomain\MyUser';

create role MyRoleName;    -- at database level only
sp_addrolemember 'MyRoleName', 'MyUserName';  --or -- alter role [MyRoleName] add member [MyUserName];
grant insert on object::MyDatabase to MyRoleName; --or-- grant insert, delete on dbo.MyDatabase to MyLogin

use mydatabase;
Alter server role [sysadmin] add MEMBER [MyDomain\Name];

MSSQL RDS CONSIDERATIONS

-- Example permissions to GRANT LOGINS
GRANT ALTER ANY CONNECTION TO [NewUserName] WITH GRANT OPTION;
GRANT ALTER ANY LINKED SERVER TO [NewUserName] WITH GRANT OPTION;
GRANT ALTER SERVER STATE TO [NewUserName] WITH GRANT OPTION;
GRANT ALTER TRACE TO [NewUserName] WITH GRANT OPTION;
GRANT CREATE ANY DATABASE TO [NewUserName] WITH GRANT OPTION;
GRANT VIEW ANY DATABASE TO [NewUserName] WITH GRANT OPTION;
GRANT VIEW ANY DEFINITION TO [NewUserName] WITH GRANT OPTION;
GRANT VIEW SERVER STATE TO [NewUserName] WITH GRANT OPTION;

-- Example permissions to GRANT ADMIN LOGINS
GRANT ALTER ANY LOGIN TO [NewUserName] WITH GRANT OPTION;

-- Example for an SSIS Service Account User
 GRANT VIEW ANY DATABASE TO [SSISuser] WITH GRANT OPTION;
 GRANT VIEW ANY DEFINITION TO [SSISuser] WITH GRANT OPTION;
 GRANT VIEW SERVER STATE TO [SSISuser] WITH GRANT OPTION;