We have a user that is has privileges to the database called IMPACT, but does not have access to a confidential table call P109A6. How can get this user access to that table?
This is the message she is getting when trying to run a query.
The SELECT permission was denied on the object 'P109A6', database 'IMPACT', schema 'dbo'
The person that normal does this is not with the company anymore.
Thanks in advance.
Check with your DBA. Normally, we would use an Active Directory group to grant permissions to various objects depending on the needs of the application. So, if you have a DBA, they should be able to find out how permissions are granted to the table.
The most straightforward way would be to grant select on that table to the user. For example, if the user is a SQL Authenticated user, you would do something like this:
GRANT SELECT ON dbo.P109A6 TO [JohnSmith];
If the user is Windows Authenticated, then it would be like this:
GRANT SELECT ON db0.P109A6 TO [YourDomain\JohnSmith];
That said, if you have a DBA, check with her to see what they normally do. If the normal procedure is to add users to relevant AD groups, as @boblarson suggested, you may not need to do anything on the database; instead, the windows sysadmin should add the user to the relevant group.
If it is a confidential table, you may not want to give select permission on the table itself if they are accessing the table through a stored procedure. In that case, just grant them EXECUTE permission on the stored procedure(s).
yes, but not recommended. Use AD groups. Then no worries if that single user departs the company. Removing users from groups is part of the HR exit process