SQL Server - User

2 - Management

2.1 - 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 ;

2.2 - Show

  • SQL Server
SELECT * FROM sys.database_principals;
  • SQL Azure
SELECT * FROM master.sys.sysusers
db/sql_server/user.txt ยท Last modified: 2018/07/24 11:44 by gerardnico