SQLTeam.com | Weblogs | Forums

Granting Permission to a table

sql2012

#1

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.


#2

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.


#3

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).


#4

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