You do not have permission [closed]

Posted on

Question :

enter image description hereI have this grant permission below

use eNtsaRegistrationDB
go
grant select on UserReg to Users;

Cannot find the object ‘UserReg’, because it does not exist or you do not have permission.

Both of table were created and granted permission(UserReg) under properties on my sql server Login folder as well mapped with my Damain, although using Window authentication mode. Still getting that issue, please help mates.

Answer :

Your question has already been answered in multiple comments and I hope you were able to solve them by now.

Let me try to summarize them for you in terms of roles, logins and users.

Logins and users: Logins and Users are basic security concepts in SQL
Server. They are often, and incorrectly, considered to be pretty much
one in the same so it is sometimes confusing to some SQL Server users.
Another important security concept tied to a login and user in SQL
Server is Security Identifiers (SID). A login is a security principal
at the scope of the SQL Server instance, and a SQL Server instance can
contain numerous databases. There are two main types of logins;
Windows authenticated login and SQL Server authenticated login. Simply
stated, a login allows you to connect to a SQL Server instance.

Once you are connected to SQL Server instance, you will typically need
access to a particular database. To allow access to a database, this
login must be mapped to a database user. Internally within SQL Server,
a login is mapped and identified to a user using security identifier
(SID).

Roles: Using SQL Server database roles, is the simplest
security method to assign and manage user permissions. I think this is
the most common method that Database Administrators (DBA) use to
handle permissions using either fixed database roles or creating
user-defined database roles. This comes from over two decades of doing
SQL database administration work.

Traditionally SQL Server provides two types of database-level roles:
fixed-database roles that are predefined in the database and
user-defined database roles that you can create. The database roles
are defined at the database level and exist on each database. When the
DBA maps the logins to databases, he/she can also create members of
these database roles that manage the security in the database.

Having above definition, in your case you need to be considerate towards granting role – role can be granted on any objects(tables, views, functions, stored procedure etc) to a login(login is not a user as stated above).

For example – we can grant read permission on table employee in a database called Usersdb to a login called John. This can be done using below commands on database:

USE Usersdb;
go
CREATE USER John FOR LOGIN John
go
grant select on employee to John
go

Above example is based on two consideration that login John is not already a user in database Usersdb and employee table is created under default schema dbo.

Second issue with your command is grant all. As it has already been highlighted in comments that this is deprecated by Microsoft and hence shouldn’t be used.

This option (ALL) is deprecated and maintained only for backward
compatibility. It does not grant all possible permissions. Granting
ALL is equivalent to granting the following permissions:

If the securable is a table, ALL means DELETE, INSERT, REFERENCES,
SELECT, and UPDATE

So, you need to use below commands collectively instead of grant all

GRANT DELETE ON tablename to login;
GRANT INSERT ON tablename to login;
GRANT UPDATE ON tablename to login;
GRANT SELECT ON tablename to login;
GRANT REFERENCES ON tablename to login;

Many points have been already highlighted in comments by Mr. Randi Vertongen, Ronaldo, McNets.

Hope above helps.

Leave a Reply

Your email address will not be published. Required fields are marked *