SQL Server - User

Articles Related

Management

Create

A login must be created in the master.

CREATE USER userName FOR LOGIN loginName;

CREATE SCHEMA userName AUTHORIZATION userName ;
ALTER USER userName WITH DEFAULT_SCHEMA = userName ;
 
-- Permission
EXEC sp_addrolemember 'db_ddladmin', 'userName';
EXEC sp_addrolemember 'db_datawriter', 'userName';
EXEC sp_addrolemember 'db_datareader', 'userName';
-- Admin ?
EXEC sp_addrolemember 'db_owner', 'userName';
EXEC sp_addrolemember 'db_accessadmin', 'userName';
 
-- Not needed (double met role) ?
GRANT CONNECT TO userName ;
GRANT CREATE TABLE TO userName ;
GRANT CREATE VIEW TO userName ;

Show

• SQL Server

SELECT * FROM sys.database_principals;

• SQL Azure

SELECT * from master.sys.sysusers