1. First, after a user is established, the default schema is dbo, and the default role is public. In this case, the user will not see the dbo and other objects under the schema. Unless authorized separately. 2. Create a new schema test, and then make the owner of the schema the user created above, and set the default schema for this user as test. Then, the new user will have all the permissions of the test schema object. At this point, however, the user does not have permission to establish tables and stored procedures. 3. Grant permissions for this new user table and stored procedure: use TestDB;
GRANT CREATE TABLE to ABC;
GRANT Create PROCEDURE to ABC so that this user will have permissions to create new tables and users. However, because this user has only the permissions of the test schema, only the tables and stored procedures of the test schema can be established. Since the default schema for this user is test, it is built with the test schema when the table is created. 4. If you want users to be able to create objects of other schemas, then you need to grant this user ALTER permission on the new schema. If you want this user to see objects inside the new schema, grant the view DEFINITION permission for the new schema for this user. 5, the above is said to use the statement to establish the method. Note There are some differences when using SQL Server client tools to build tables and stored procedures. Building a table from the graphical interface gives an error indicating that only administrators and database owners have permission to do so. This is only because of the temporary data to be stored in the system table, can be ignored, so it is just to generate the statement and execution of the table, these operations are OK, no problem. So that error can be ignored.
SQL Server enables users to establish tables and stored procedures only under a schema
