Select permissions for user on SQL Server 2008 database

1143130 pts.
Tags:
SQL Server 2008
SQL Server database
I have to create a new user on our SQL Server 2008 database with only select permissions. This user will have read-access only. Is there a way to do this? Thanks.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Thanks for coming to ITKE.
I did a web search and found a few sites that had information to assist you. Here is a link to one of them. https://msdn.microsoft.com/en-us/library/ms178569.aspx

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • edwinjhoffer
    3,740 pointsBadges:
    report
  • Elizine
    If you want to give your user all read permissions, you could use:

    EXEC sp_addrolemember N'db_datareader', N'your-user-name'
    That adds the default db_datareader role (read permission on all tables) to that user.

    There's also a db_datawriter role - which gives your user all WRITE permissions (INSERT, UPDATE, DELETE) on all tables:

    EXEC sp_addrolemember N'db_datawriter', N'your-user-name'
    If you need to be more granular, you can use the GRANT command:

    GRANT SELECT, INSERT, UPDATE ON dbo.YourTable TO YourUserName
    GRANT SELECT, INSERT ON dbo.YourTable2 TO YourUserName
    GRANT SELECT, DELETE ON dbo.YourTable3 TO YourUserName
    and so forth - you can granularly give SELECT, INSERT, UPDATE, DELETE permission on specific tables.

    And yes, you can also do it graphically - in SSMS, go to your database, then Security > Users, right-click on that user you want to give permissions to, then Properties adn at the bottom you see "Database role memberships" where you can add the user to db roles.
    1,145 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: