3

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?

2

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.

0

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.

  • Below query returned 1. – Yusif Yusifov Nov 8 '14 at 13:52
  • I use SSMS to create Login and map to database. – Yusif Yusifov Nov 8 '14 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.