Validating sql privileges
Validating sql privileges - are megan fox and shia dating
In this context, you can check their access to any database - while you may be tempted to loop through all of the databases and attempt to connect or select, you can use the has not been granted.
In fact, in many cases, it's not even a valid test you can perform for SQL Authentication logins, because you may not know their password (even a sysadmin can't read a login's password). Once you are impersonating a login, you can then test anything you want, because you will be running as them.This means that any user with access to your system can see the names of all databases, which can be an issue if database names include or imply sensitive information.Please vote up Erland's Connect item and comment on why this is a critical security issue for your business.Security is becoming more and more of a concern these days.In some shops, the path of least resistance is to give developers system admin access to instances of SQL Server.As an aside, is not foolproof; in some scenarios this permission is bypassed entirely.
For example, this script will allow even a user only in the public role to enumerate the databases on the system, without having to face any metadata validation: EXECUTE AS LOGIN = N'peon1'; GO ; WITH v(n) AS ( SELECT number FROM [master]spt_values ), n(n) AS ( SELECT TOP (32766) n = ROW_NUMBER() OVER (ORDER BY v.n) FROM v CROSS JOIN v AS v1 ORDER BY n ) SELECT db = DB_NAME(n) FROM n WHERE DB_NAME(n) IS NOT NULL ORDER BY db; GO REVERT; function does not bother with pesky security checks (and a Connect item by Erland Sommarskog, #755720, is currently marked as "Won't Fix").
You should be aware that this script only detects direct role membership; it does not perform recursive cycles to find nested roles (see this tip for more info on nested roles).
...we'll go a little deeper, and see how we can use similar logic to see what things a user can do inside a database.
You can grant this privilege to another login so that they can impersonate specific logins, or any login.
(Note that all logins also inherit the right automatically with the The second statement yields "Yes" - meaning they can see the objects inside that database.
logs information about privileges granted on various objects, including the grantor and grantee, in the V_CATALOG. The order of columns in the table corresponds to the order in which they appear in the GRANT command.