3

I am completely scratching my head right now. I have two database instances that I am trying to add a table to, and both give me the same error:

The user does not have permission to perform this action.

and I have triple checked the permissions, I am on a sysadmin account. Running the following query to see what create permissions I have yields the following:

SELECT SYSTEM_USER

-- Now get the permissions assigned to you by the server administrator 
use tempDB 
GO 

;with getPermissions as ( SELECT * FROM fn_my_permissions (NULL, 'DATABASE') ) 
select permission_name from getPermissions 
where permission_name like 'create%' 
GO

enter image description here

I have also tried creating another login with sysadmin privileges to no avail. Any ideas?

Error log picture below:

enter image description here

USE [db_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table](
    [a] [int] IDENTITY(1,1) NOT NULL,
    [b] [nvarchar](50) NULL,
    [c] [decimal](18, 3) NULL,
    [d] [bit] NULL,
    [e] [nvarchar](50) NULL,
    [f] [nvarchar](10) NULL,
    [g] [nvarchar](500) NULL,
    [h] [int] NULL,
    [i] [int] NULL,
    [j] [int] NULL,
    [k] [int] NULL,
    [l] [int] NULL,
    [m] [int] NULL,
    [n] [int] NULL,
    [o] [int] NULL,
    [p] [int] NULL,
    [q] [int] NULL,
    [r] [int] NULL,
    [s] [int] NULL,
    [t] [int] NULL,
    [u] [int] NULL,
    [v] [int] NULL,
    [w] [int] NULL,
    [x] [int] NULL,
    [y] [int] NULL,
    [z] [int] NULL,
    [aa] [int] NULL,
    [ab] [int] NULL,
    [ac] [int] NULL,
    [ad] [int] NULL,
    [ae] [int] NULL,
    [af] [int] NULL,
    [ag] [int] NULL,
    [ah] [int] NULL,
    [ai] [int] NULL,
    [aj] [int] NULL,
    [ak] [int] NULL,
    [al] [int] NULL,
    [am] [int] NULL,
    [an] [int] NULL,
    [ao] [int] NULL,
    [ap] [int] NULL,
    [aq] [int] NULL,
    [ar] [int] NULL,
    [as] [int] NULL,
    [at] [int] NULL,
    [au] [int] NULL,
    [av] [int] NULL,
    [aw] [int] NULL,
    [ax] [int] NULL,
    [ay] [int] NULL,
    [az] [int] NULL,
    [ba] [int] NULL,
    [bb] [varchar](12) NULL,
    [bc] [varchar](12) NULL,
    [bd] [varchar](12) NULL,
    [be] [varchar](12) NULL,
 CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED 
 (
    [A] ASC
 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Table] ON
INSERT [dbo].[Table] ([a], [b], [c], [d], [e], [f], [g], [h], [i], [j], [k], [l],
                      [m], [n], [o], [p], [q], [r], [s], [t], [u], [v], [w], [x],
                      [y], [z], [aa], [ab], [ac], [ad], [ae], [af], [ag], [ah],
                      [ai], [aj], [ak], [al], [am], [an], [ao], [ap], [aq], [ar],
                      [as], [at], [au], [av], [aw], [ax], [ay], [trkCusT4SA], [az],
                      [ba], [bb], [bc], [bd])
              VALUES (36, N'City', CAST(2.500 AS Decimal(18, 3)), 1, N'SSS', N'CTY',
                      N'http://google.com', 200, -4, 6400, 6700, 150, 450, 6400,
                      6700, 1400, 1800, 3400, 3800, 8000, 8400, 10100, 10500, 850,
                      2430, 2880, 4460, 7480, 9060, 9540, 11070, 6400, 6700, 150,
                      450, 6400, 6700, 1400, 1800, 3400, 3800, 8000, 8400, 10100,
                      10500, 850, 2430, 2880, 4460, 7480, 9060, 9540, 11070, NULL,
                      NULL, NULL, NULL)

ALTER TABLE [dbo].[Tracks] ADD  CONSTRAINT [IX_Table] UNIQUE NONCLUSTERED 
(
    [A] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
        IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO 

Running SELECT * FROM sys.dm_exec_trigger_stats

Yields the following result:

enter image description here

21
  • What does select user_name() return? If it's not dbo you're not a sysadmin. Feb 10, 2021 at 18:59
  • @DavidBrowne-Microsoft Hi David, it returns dbo
    – MaxB
    Feb 10, 2021 at 19:04
  • is there any user at all on the database that can create a table? maybe privileged user that is not a sysadmin or db_owner. Is the create table the only thing throwing the error?
    – Ronaldo
    Feb 10, 2021 at 19:55
  • @Ronaldo no other users have high enough privilege. And yes it seems that create or alter table throws the error. I attempted to create another login with the necessary privileges and had the same issue.
    – MaxB
    Feb 10, 2021 at 20:51
  • is database read-only?
    – MBuschi
    Feb 11, 2021 at 7:23

3 Answers 3

4

This is not likely a permissions issue, at least not the type of permission you are thinking of. Typical permissions errors show up as follows:

Msg 262, Level 14, State 1, Line XXXXX
CREATE TABLE permission denied in database 'tempdb'.

Now that we have a more complete error message, we can see that indicates a "severity" of 19.

According to Microsoft SQL Server documentation:

Severity         Meaning


17-19          Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem.
19               Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log.

This sounds more like (hypothetically) using SQL Server Express and reaching the 10 GB DB limit (even a CREATE TABLE statement needs to add meta data) than it does a permissions problem.

With more info it will hopefully be possible to narrow down the specific source of this. Here is what testing has shown so far:

  • database is not read-only
  • user = dbo
  • able to run statements in question on several other identical instances of this exact database just fine. Just one local instance giving the error
  • unable to create a table using CREATE TABLE dbo.[_test] ([Col1] INT);. but able to edit tables using SSMS and also add rows
  • unable to create anything, not just Tables (View / Function / etc). get the same error.
  • The size of the database is 5008.00 MB and space available is 114.16 MB
  • able to create a temp table while in the problem DB: USE [problem_db]; CREATE TABLE #Bob (Col1 INT);
  • able to create a real table in tempdb: USE [tempdb]; CREATE TABLE dbo.Bob2 (Col1 INT);
  • DBCC TRACESTATUS(-1); shows no trace flags in use
  • Compatibility mode is SQL Server 2014 (120), but have also tried 2017 (140)
  • able to create a schema in the problem DB: CREATE SCHEMA [_DeleteMe];
  • UNable to create a table in the new schema: CREATE TABLE [_DeleteMe].[_test] ([Col1] INT);
  • Owner SID did not match between sys.databases and dbo user in the problem DB, but that has been fixed and still not able to create a table
  • TRUSTWORTHY is OFF, but also OFF on instance where this DB is just fine

Here are some places to check for things can cancel submitted operations:

SELECT * FROM sys.triggers;
SELECT DB_NAME(ts.[database_id]) AS [Database],
       OBJECT_NAME(ts.[object_id], ts.[database_id]) AS [Trigger],
       OBJECT_DEFINITION(ts.[object_id]) AS [Definition],
       *
FROM   sys.dm_exec_trigger_stats ts;

SELECT * FROM sys.database_audit_specifications;
SELECT * FROM sys.database_audit_specification_details;

SELECT * FROM sys.security_policies;
0
1

I found a similar question/answer here regarding SQL Server: https://dba.stackexchange.com/a/82367/123612

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.

Edit: While this may seem obvious, have you tried right-clicking on SQL Studio and running as administrator?

1
  • Unfortunately that was not the case, and yup I have been running SSMS as Administrator.
    – MaxB
    Feb 10, 2021 at 18:16
1

Are you able to login with sa and try to create the table?

sa Login doc says:

The sa login has all permissions on the server and cannot be limited.

The DENY doc says:

DENY takes precedence over all permissions, except that DENY does not apply to object owners or members of the sysadmin fixed server role. Security Note Members of the sysadmin fixed server role and object owners cannot be denied permissions."

Also, I created a user with no privileges on a database and tried to create a table and the error message was different from yours:

Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database 'Lab'.

So, it's possible the problem is not the lack of permission, but something else like a file not being able to grow to accommodate the new table or some other problem (do you see any error on the log?).


In addition to Solomon Rutzky's findings:

  • There's no error message on event viewer related to this problem;
  • DBCC CHECKDB returns 0 allocation or consistency errors;
  • Generating schema only scripts of the database and then creating a test database of identical schema allowed to create tables, etc.;
  • A restored backup of the problematic database on a different server keeps the problem.
14
  • I have been using the sa account do debug this actually. And what I am actually trying to do is drop a table, modify it, then reinsert it. The drop table portion of the query executes fine, but then it fails when trying to re-insert it. I'll comb through the error logs and see if anything sticks out to me.
    – MaxB
    Feb 11, 2021 at 18:20
  • @MaxB now that you have found another error on SQL Server log, can you check Windows log to see if there's anything there too?
    – Ronaldo
    Feb 11, 2021 at 19:48
  • 1
    I am able to create a new database on the server and add tables to it, so it must be a database specific issue
    – MaxB
    Feb 11, 2021 at 20:33
  • 1
    Ronaldo (and @MaxB ): good idea to script out the DB objects and recreate in a new DB on that same instance! and good to check DBCC CHECKDB. I had him check for trace flags (none in use) and creating a Schema in the problem DB (it worked, but still can't create objects in the new Schema). Feb 12, 2021 at 15:41
  • 1
    @MaxB I'm really curious about the reason of the problem, meanwhile, in order for you to keep working on a healthy database I thought of a workaround (if you can afford some downtime on the database): generate the scripts of the database, recreate it, change the original database name and import data to the new one. This way you can fix production and keep the original database to keep looking for the culprit while everything is working.
    – Ronaldo
    Feb 15, 2021 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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