1

I have this query

alter table NashvilleHousing
add FixedSaleDate Date;

update NashvilleHousing
set FixedSaleDate = CONVERT(Date, SaleDate)

got this error:

Msg 4902, Level 16, State 1, Line 11 Cannot find the object "NashvilleHousing" because it does not exist or you do not have permissions.

It is a local database I own, I made the database by importing an Excel file

Using this query

USE PortfolioProjects;
GO

SELECT * 
FROM sys.objects 
WHERE type = 'U' -- U stands for user table
  AND name = 'NashvilleHousing';

got this table

enter image description here

so I understand that the table does exists (and It does I create it and have do queries on it)

then i use this query trying to find my permissions

USE PortfolioProjects;
GO

SELECT dp.name AS principal_name, dp.type_desc AS principal_type,
       pe.permission_name, pe.state_desc AS permission_state
FROM sys.database_principals AS dp
JOIN sys.database_permissions AS pe 
       ON pe.grantee_principal_id = dp.principal_id
JOIN sys.objects AS o
       ON pe.major_id = o.object_id
WHERE o.name = 'NashvilleHousing'
      AND o.object_id = 901578250;

and got an empty table so I use the next query to find what user I am

SELECT SUSER_SNAME();

and got this as a result: MUXIKA\Dell so (MUXIKA\Dell) must be my user

then I try this query to se what permission MUXIKA\Dell or the user I am has

USE PortfolioProjects;
GO

SELECT dp.name AS principal_name, dp.type_desc AS principal_type,
       pe.permission_name, pe.state_desc AS permission_state,
       o.name AS object_name, o.type_desc AS object_type
FROM sys.database_principals AS dp
LEFT JOIN sys.database_permissions AS pe 
       ON pe.grantee_principal_id = dp.principal_id
JOIN sys.objects AS o
       ON pe.major_id = o.object_id
WHERE dp.name = 'MUXIKA\Dell';

and got an empty table again

when I try to use queryes to give my self (MUXIKA\Dell) permission whit this query

USE PortfolioProjects;
GO

-- Replace 'NashvilleHousing' with the name of your table
-- Replace 'YourUserName' with the name of the user or role you want to grant permissions to

-- Grant SELECT permission to a user or role
GRANT SELECT ON OBJECT::NashvilleHousing TO MUXIKA\Dell;

-- Grant INSERT permission to a user or role
GRANT INSERT ON OBJECT::NashvilleHousing TO MUXIKA\Dell;

-- Grant UPDATE permission to a user or role
GRANT UPDATE ON OBJECT::NashvilleHousing TO MUXIKA\Dell;

-- Grant DELETE permission to a user or role
GRANT DELETE ON OBJECT::NashvilleHousing TO MUXIKA\Dell;

-- Grant EXECUTE permission to a user or role (if applicable)
GRANT EXECUTE ON OBJECT::NashvilleHousing TO MUXIKA\Dell;

goes to this error Incorrect syntax near '(Backslash symbol)'.

I try to alter and update tables, but it seems my user doesn't have permission so I am trying to verify witch is my user and check if it has any permission and give it all the permission if that's the problem.

Thanks for your help

2
  • I'm guessing here that your default schema isn't dbo, Does alter table dbo.NashvilleHousing add FixedSaleDate Date; work Commented Apr 11 at 8:01
  • it is a dbo but alter table dbo.NashvilleHousing add FixedSaleDate Date; did not work... Commented Apr 11 at 22:19

2 Answers 2

0

Copying from the example in Books on Line :-

GRANT SELECT ON Person.Address TO [AdventureWorks2022\RosaQdM];  

wrap your username in square brackets & I would add the correct schema to prefix the tablename. You can't grant execute on a table.

1
  • Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. Msg 4606, Level 16, State 1, Line 61 Granted or revoked privilege EXECUTE is not compatible with object. ----> that was the result I got Commented Apr 11 at 22:32
0

What you are missing is ALTER on your table, that's why you've got

Msg 4902, Level 16, State 1, Line 11 Cannot find the object "NashvilleHousing" because it does not exist or you do not have permissions.

Your

alter table NashvilleHousing
add FixedSaleDate Date;

is a DDL statement and requires ALTER TABLE you do not have.

To your other questions:

USE PortfolioProjects;
GO

SELECT * 
FROM sys.objects 
WHERE type = 'U' -- U stands for user table
  AND name = 'NashvilleHousing';

This query returns you the table in question because you have SELECT on this table, the simplest reason why it can happen without granting you the explicit SELECT on this table is you are a member of db_datareader database role, and that is why the query

SELECT dp.name AS principal_name, dp.type_desc AS principal_type,
       pe.permission_name, pe.state_desc AS permission_state,
       o.name AS object_name, o.type_desc AS object_type
FROM sys.database_principals AS dp
LEFT JOIN sys.database_permissions AS pe 
       ON pe.grantee_principal_id = dp.principal_id
JOIN sys.objects AS o
       ON pe.major_id = o.object_id
WHERE dp.name = 'MUXIKA\Dell';

shows nothing to you.

The simplest way to check if you a member of this role is to execute this:

select is_rolemember('db_datareader')

If you are only member of db_datareader and/or db_datawriter roles you cannot modify any table.

To check all the permissions you have on this table execute this:

select *
from sys.fn_my_permissions('NashvilleHousing', 'object')

Unless you see ALTER in the result you cannot modify this table.

P.S. You cannot grant permissions to yourself. P.P.S. The error you've got is refered only to your ALTER statement and don't tell you if you have or not the UPDATE permission. You just did not reach your second statement due the first that failed.

2
  • the query --->select is_rolemember('db_datareader') gives as a result a table with a 1 and that's it and the query ---> select * from sys.fn_my_permissions('NashvilleHousing', 'object') gives an empty table as a result, it's like I don't have any permission, but how could that be? if I create the database locally from an Excel file Commented Apr 11 at 22:28
  • @JoseMujica >>>the query "select is_rolemember('db_datareader')" gives as a result a table with a 1 <<<this is because as I said you are membere of db_datareader database role and that is why you can "see" this table in sys.objects, but you don't have ALTER so you have the error that you have. To see all your permissions in this database you can execute select permission_name from sys.fn_my_permissions(null, 'database') order by 1
    – sepupic
    Commented Apr 12 at 10:58

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.