Database Permission of SQLserver

asked 2020-09-17T04:26:45.653+00:00
Avyayah 1,126 Reputation points

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;

25365-user.jpg

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,473 questions
{count} votes

Accepted answer
  1. answered 2020-09-18T03:48:47.83+00:00
    Miamiao-MSFT 4,216 Reputation points

    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.

    No comments

2 additional answers

Sort by: Most helpful
  1. answered 2020-09-17T05:19:35.723+00:00
    Shashank Singh 6,211 Reputation points

    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.

    No comments

  2. answered 2020-09-17T08:11:09.813+00:00
    Miamiao-MSFT 4,216 Reputation points

    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.

    No comments