This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(147.20000000000002, 99%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ET%3C/text%3E%3C/svg%3E)
Hi,
We find that for a particular SQL Login, it can access a number of databases (FinanceDev and FinanceTest) even though we cannot find corresponding Database User. Is there any way for us to revoke the access for that particular SQL Login ?
On the other hand, we are not able to drop that SQL Login as somehow it is database owner of another database (FinanceProd). We have already raised another question as follow
Your advice is sought.
It is pretty weird as we finally removed that SQL Login. However, we find that even though there is no database user & associated SQL Login, he can still login to the database server and access the following databases
FinanceDev / FinanceProd / FinanceTest !?
We have added that SQL Login again and only grant Read Only access to FinanceProd database but somehow he can still access the others ?
Presumably the user has access through an AD group.
You can run this in the database:
EXECUTE AS USER = 'DOMAIN\USER'
SELECT name, type, usage
FROM sys.user_token
REVERT
This will list all security tokens that are associated with this user.
Dear Erland,
Yes, you are right. From your query, I get the following result
name type usage
public ROLE GRANT OR DENY
Domain\FinanceG WINDOWS GROUP GRANT OR DENY
db_datareader ROLE GRANT OR DENY
db_datawriter ROLE GRANT OR DENY
AD Group FinanceG is granted both db_datareader and db_writer roles. Peter is a member of that AD Group.
In this way, my understanding is that Domain\Peter is able to log in database via the AD Group. Is my understanding correct ?
Thanks again.
Yes, that is correct.
And I like to add that this is a very important feature, since this permits you to control access through the AD which is very convenient. Not the least when someone leaves the company. Remove the person from the AD, and implicitly revoke the access for that person from umpteen SQL Server instances. Unless you made the mistake of adding the person directly to the server/database. (Well, sometimes you may have to do this, because that person needs special permissions in a database.)