Database Permission of SQLserver

Question

Granted permission to the user on the database, after executing the following query:
SELECT pr.principal_id, pr.name, pr.type_desc,
pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id;

Is this correct? How do I grant permission to the user on select and update to the database

Answer 1

Hi @Avyayah ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To grant select and update permission to a user on a database you need to run following

EXEC sp_addrolemember N'db_datareader', N'userName'--for read permission  
go   
EXEC sp_addrolemember N'db_datawriter', N'your-user-name'--for insert,update and delete permission  

sp-addrolemember-transact-sql please refer to the document for details.

Answer 3

Hi @Avyayah ,

Check the perimissions in one db:

Use dbname  
go  
  
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]   
FROM [sys].[database_permissions] sec   
  JOIN [sys].[database_principals] prin   
    ON sec.[grantee_principal_id] = prin.[principal_id]   
WHERE sec.class = 0   
ORDER BY [User], [Permission];   

--Grant Permission:

USE dbname;  
GRANT select,update ON DATABASE::dbname TO UserName;  
GO  

More information: sql-server-create-user,grant-database-permissions-transact-sql

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.