A quick tour of adding and administering users in SQL Server
You can add database-authenticated and Windows-authenticated logins to a Microsoft SQL Server instance and map them to users in any of the databases on the instance.
Windows-authenticated logins are the default and, therefore, the recommended type of user for SQL Server databases. By default, when your SQL Server instance is created, this is the only type of login allowed.
To use database-authenticated logins, you must set the database to use mixed-mode authentication. See the SQL Server Books Online for information on setting up the SQL Server instance to use mixed-mode authentication.
When you add database users, you assign them a default schema in the database. If the user is going to own data in the geodatabase, the default schema must have the same name as the user name. If it does not, the user cannot create datasets, copy datasets into the geodatabase, or use geoprocessing tools that result in new datasets.
You can grant privileges to the logins and/or users. Privileges can be granted to individual users, or users can be added to roles and privileges can be assigned to the roles. Roles are groups within the database that allow you to administer users as a group. SQL Server contains fixed-server and database roles that you can use. These roles have a predefined set of privileges in the SQL Server instance and databases. Alternatively, you can create your own roles and set privileges on them.