SQL Server Login Lockdown

Question

Is it possible to lock down MS SQL sql logins? Like a read only user only should be able to access DB data but will not be able to access/view stored procedures/views etc.?

Thanks

Answer 1

Hi @shaikh shoaib ,

Create or change the read and write permissions on the account.
1.Create a new user
You could add the user to the Database Level Role db_datareader.
Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
Please to this blog about how to create a read-only user: https://hub.acctivate.com/articles/create-a-read-only-sql-server-user-account
Please to this blog about db_datareader: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms189121(v=sql.90)?redirectedfrom=MSDN
2.Change properties
Security->Logins->UserName->Properties
Follow the same steps like before.
Note: The important steps are selecting the database for mapping and granting permissions.

Then use this account to log in.
Lastly, you can see that the account can only read and not write.

> but will not be able to access/view stored procedures/views etc.

Login with your sa account, select YourDatabase->Views->System Views-> Properties and follow the steps:

Then set the permission.
Finally, use your account(such as my [Oh]) to login in.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
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

Not very clear, what you are looking for.
Grant SELECT permissions only = ReadOnly on the table as per your requirement; so in which detail do you have an issue/question on?

Answer 3

Yes, it is possible. In fact, by default a login has very little permission. No access to any database beyond the system databases, and if you add a user to a database, the user by default has access to no tables at all.

So if you want a login DOMAIN\USER to be a readonly user in database DB1, you would do:

USE DB1
go
CREATE USER [DOMAIN\USER]
ALTER ROLE db_datareader ADD MEMEBER [DOMAIN\USER]

If you find that the login can do more than this, this may be because the login is member of an AD group which has wider permissions, or permissions have been granted to public.

Answer 4

Thanks all, will go through your kind replies soon.