In SQL server, if I want to grant a user KLB permission to create tables, but I don’t want to grant the database role dB_ Ddladmin, because it will expand its permissions, is it feasible to grant the following permissions? As follows:
So is this authorization OK? The answer is that authorization will report an error“The specified schema name “dbo” either does not exist or you do not have permission to use it.”
You need to grant the following permissions to create the table with the login name KLB.
But this will expand the permissions of the login name KLB. In fact, if you create a new user schema in SQL server, you can solve this problem.
CREATE SCHEMA test AUTHORIZATION klb
If the corresponding user mode already exists
GRANT ALTER ON SCHEMA::test TO klb
After authorization as above, compared with the following script, you will find that KLB can create tables in test mode, but not in dbo mode. In fact, this is also the reason for the separation design of SQL server user mode.