1

I would like to create a user account in SQL Server 2012 with limited access, i.e., he should be only able to run queries and view data and nothing else. No backups, restores, user modifications should be allowed.

I tried looking at the built in server roles, but could not really understand it too well. The Server consists of some 7-8 different databases and we would like this user to have only querying access across all databases and nothing more. Would be great if somebody could guide me as to how to implement it.

Regards

Saurabh

Saurabh Omar
  • 39
  • 8
  • 2
    Grant it 'datareader' database role, nothing more. – Arvo Dec 09 '16 at 07:31
  • Check out this answer http://stackoverflow.com/a/3998649/5725863 – Shakeer Mirza Dec 09 '16 at 07:36
  • Possible duplicate of [SQL Server 2008: how do I grant privileges to a username?](http://stackoverflow.com/questions/3998634/sql-server-2008-how-do-i-grant-privileges-to-a-username) – Mr. Bhosale Dec 09 '16 at 07:39
  • Is there something in the server level roles that could suffice. Right now, all users have been given sysadmin server roles. The SQL Server has multiple databases in there and we would like to restrict access on all databases. – Saurabh Omar Dec 09 '16 at 07:41
  • You don't start by granting them a role with massive permissions and then take lots of those permissions away. Just grant them the permissions you want to grant them and *nothing else*. – Damien_The_Unbeliever Dec 09 '16 at 07:51
  • Thanks Damien. This is an old server and we are trying to reconfigure user rights now. that is why we would like to change roles. – Saurabh Omar Dec 09 '16 at 07:53

1 Answers1

1

Simple create role and grant access to needed objects with command GRANT. Example:

GRANT SELECT ON TABLE1 TO ROLE_ONLY_VIEW_FOR_EXAMPLE

Then you can assign this role to any user you want.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Renat Seyfetdinov
  • 413
  • 4
  • 7
  • Does this grant permission only on a specified table? Sorry for asking such a stupid question, but i am too bad with permissions. – Saurabh Omar Dec 09 '16 at 08:10
  • yes, you should set permission for each table or view or procedure. – Renat Seyfetdinov Dec 09 '16 at 13:10
  • also refer https://johnnycode.com/2013/07/12/create-a-sql-server-login-user-with-limited-permissions/ and also refer https://support.chartio.com/knowledgebase/granting-table-level-permissions-in-sql-server – Pranesh Janarthanan Nov 20 '18 at 12:45