0

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.

8
  • Is it under dbo schema or some other schema? If it falls under dbo then above should work. – Learning_DBAdmin Jan 22 '20 at 10:16
  • It is please see my screen shot for details as attached, thanks. – eNtsa2019 Mkontwana Jan 22 '20 at 10:20
  • 1
    it seems that UserReg is a login not a db object. – McNets Jan 22 '20 at 10:24
  • 1
    grant all has been deprecated, And you should consider hiding or masking sensitive details of your PC/server before posting on any public forum, you could be soft target of hackers otherwise. Having said that - could you please run below --> use db_name; go; SELECT * FROM fn_my_permissions (NULL, 'DATABASE') ; go; – Learning_DBAdmin Jan 22 '20 at 10:26
  • You should be using grant select/insert/update(permission) on object_name(table_name/view_name) to user_name(inside database which is mapped to a login) – Learning_DBAdmin Jan 22 '20 at 10:28
3

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.

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