SQL Server - User

Card Puncher Data Processing

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





Discover More
Card Puncher Data Processing
SQL Server - Database Owner (dbo) user

The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database...
Card Puncher Data Processing
SQL Server - Login

A login can: join a role be authorized to use a database, see Create a Database User. be granted permission The database user is the identity of the login when it is connected to a database. The...
Card Puncher Data Processing
SQL Server - Principal

in SQL Server A principal is a user or a group
Card Puncher Data Processing
SQL Server - System Account User (SA)

The SA user is THE admin user of the whole SQL Server application. The SA users are always defaulted to the dbo schema.



Share this page:
Follow us:
Task Runner