why permissions section for Database user is empty in MS Sql server 2008R2
sql-server-2008-r2, user-interface, user-permissions
Solution
A. Set up Read/Write
- Go to Security/Logins and find your login, double click it
- Go to user mapping, and click on the database that you have access to
- In the bottom pane under 'Database Role Membership', tick `db_datareader` and `db_datawriter`
This gives the user Login SELECT, INSERT, UPDATE, DELETE
B. Revoke DELETE and grant EXECUTE
Create a role that does this:
- Go to your database / Security / Roles
- Right click, New / Database Role
- Give the role a name, I will use `executor` for this example and press OK
I don't know how to do the next steps in SSMS, You'll need to do it in T-SQL:
- Start a new query in your database
Type this and press F5:
`GRANT EXECUTE TO executor;`
`DENY DELETE TO executor;`
Now repeat A3 but select your newly created role, 'executor'
Every new user (or group) that you create needs to be a member of these three roles. The best practice is to add a windows group to SQL Server once, and add users to that windows group.
Lastly test this - I don't know for sure that it works.
With regards to the database user securables:
You have to explicitly populate this list to see what it contains. It doesn't populate automatically. Press Search and search for some objects (i.e. all objects belonging to the schema dbo). Now you have a list of objects in the top. Click on an object and click the 'Effecttive' tab on the bottom. This is the users effective (final) permissions for this object. If you want to override this at the object level you can assign something on the explicit tab
Problem
NOTE: I am note DB Admin and I am not that much in sql server security I am using MS SQL SERVER 2008R2 What I want to do is to give a user a minimal permissions or just what he required I have a local user in my windows and I add this user in the logins of the database after that I went to this user in my specific database and try to change his set of permissions but the section is coming empty why it is coming empty? and how to do this, I mean giving him the permissions that he just need nothing more? Please I want to do this from the user interface without T-sql EDIT I Just want to give the user read, write, execute nothing more and also I need to know more about how to control users permissions in more details