I have database in SQL Server 2012 SP2 Enterprise. I created new SQL Login and mapped to X database with db_owner role. Now I can't create table on this database. When I run simple create table script I got below error.
Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database 'INVENTORY_TEST'.
There is no database or server trigger. I did not find any SQL Server bug related to this problem. Any idea?
The problem solved. In the Database options - Permissions tab public Database role was added and it was Deny permission for Create database option. After I removed Deny permissions it is OK now. User can create table.
Did you run something similar to this to create your login and user: -
use master;
create login Test_Login with password = 'o6aWkNWHUzfUEDmcR*!R';
use INVENTORY_TEST;
create user Test_User for login Test_Login;
exec sp_addrolemember db_owner,Test_User;
Then to test, this should run without error: -
use INVENTORY_TEST;
execute as Login = 'Test_Login'
create table dbo.t(i int)
revert
Also, this should return 1 if you have configured the user to be a member of the db_owner role correctly: -
use INVENTORY_TEST;
select IS_ROLEMEMBER('db_owner','Test_User');
A last point is members of db_owner can drop your database. It is usually good practice to grant the minimum permissions a user requires to perform their role and nothing more.
