CODEWITHSUNDEEP
sql-serverpermissions
anonymous
anonymous

Reputation: 113

Select permissions were revoked in SQL Server but the user can still access all tables

I have revoked select permissions for a user in SQL Server and gave them access to one table but the user can still query all tables. 

REVOKE SELECT ON "dbo"."TableName" FROM "Domain\user.name"

I have double and triple check all the permissions on both the login and user. Can someone please steer me in the right direction?

Upvotes: 0

Views: 1737

Answers (1)

Philip Kelley
Philip Kelley

Reputation: 40359

To identify all permissions someone may have in SQL, you have to look at:

  • The SQL Login configured for their domain login. Is it a member of any server-level groups? Does it have any server-level permissions?
  • What databases does it have access to?
  • Within those databases, what permissions does it have?

I'm guessing you've already done that. Next level:

  • Identify all domain groups for which SQL Logins have been created.
  • Determine which of these groups your user is a member of. Do the same checks as above, e.g. what can members of that group do in this SQL Instance.
  • Note that domain groups can contain domain groups. Depending on how in (or out of) control you Domain Administrators are, you could have crazy levels of nesting going on. And this is in the Domain, active directory, which you may or may not have sufficient access rights to review in detail.
  • Don't forget Local (that machine) groups, often set up by default. Is the user a member of a local machine group with elevated rights? You won't find anything out about this at the domain level.

This of course assumes that they are only using their own personal domain login, without aliasing, "Running As", SQL authenticated logins, application logins, and probably some even more obscure things I can't think of right now. (They probably aren't, unless they're griefing you.)

Note that this was off the top of my head. Configuring SQL Security is a Dark Art; figuring out who's been configured with what can be a nightmare (and worse when dealing with applications running on system accounts.) Good luck!

Upvotes: 1

Related Questions