Validating sql privileges

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

Since they impersonated a sysadmin, we know they can also access any data.

Also note that this doesn't mean they can't access *other* objects in master, only that they've been explicitly denied access to that view (which should be uncommon).) Note that this tells us they can query .

For anything they can access that you don't think they should, you can go back through their server role membership, database ownership, database role membership and granted privileges to see why they can access that object in that database (and really you could change any example on this page to look at other tables or views).

This is a little convoluted, but basically we need dynamic SQL to run -- table for collecting data CREATE TABLE #x([login] SYSNAME, db SYSNAME, error INT); -- table of logins we care about today DECLARE @logins TABLE([login] SYSNAME); INSERT @logins([login]) VALUES(N'boss'),(N'peon1'),(N'peon2'); -- build SQL commands for read attempts to each online db DECLARE @sql NVARCHAR(MAX) = N'', @cmd NVARCHAR(MAX) = N''; SELECT @sql = N' BEGIN TRY INSERT #x SELECT TOP (1) SUSER_SNAME(), N''' REPLACE(name,'''','''''') N''',0 FROM ' QUOTENAME(name) N'all_objects; END TRY BEGIN CATCH INSERT #x SELECT SUSER_SNAME(), N''' REPLACE(name,'''','''''') ''', ERROR_NUMBER(); END CATCH;' FROM sys.databases WHERE [state] = 0; SELECT @cmd = N' EXECUTE AS LOGIN = N''' REPLACE([login], '''', '''''') N'''; ' @sql N' REVERT;' FROM @logins; EXEC [master]sp_executesql @cmd; -- report SELECT [login], [db], [Access?

] = CASE error WHEN 0 THEN 'Yes' WHEN 229 THEN 'Access denied on sys.all_objects' WHEN 916 THEN 'Cannot connect to database' ELSE 'No - ' CONVERT(VARCHAR(11), error) END FROM #x ORDER BY [login],db; GO --DROP TABLE #x;.

And there are very easy ways to limit the databases these logins can see.

Let's start by creating a handful of logins with different roles and database access (and I am going to constrain much of this discussion to SQL Authentication logins, since there are layers of additional complexity when we are talking about Windows Authentication, domain groups, and especially multiple domains): USE [master]; GO -- add "boss" to sysadmin: CREATE LOGIN boss WITH PASSWORD = 'x', CHECK_POLICY = OFF; ALTER SERVER ROLE sysadmin ADD MEMBER boss; -- add "dev1" to serveradmin: CREATE LOGIN dev1 WITH PASSWORD = 'x', CHECK_POLICY = OFF; ALTER SERVER ROLE serveradmin ADD MEMBER dev1; -- add "dev2" to dbcreator: CREATE LOGIN dev2 WITH PASSWORD = 'x', CHECK_POLICY = OFF; ALTER SERVER ROLE dbcreator ADD MEMBER dev2; -- "peon1" will only be in public CREATE LOGIN peon1 WITH PASSWORD = 'x', CHECK_POLICY = OFF; -- "peon2" will be in public *and* granted explicit access to AW2014: CREATE LOGIN peon2 WITH PASSWORD = 'x', CHECK_POLICY = OFF; GO USE Adventure Works2014; GO CREATE USER peon2 FROM LOGIN peon2; GO Seeing the name is one thing; being able to connect or access is another.

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

In the results of the query, you'll see execute listed but with no specific object type or object name!

Security is becoming more and more of a concern these days.

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

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.

Leave a Reply

  1. dating services and tampa and florida 16-Aug-2019 18:59

    is to provide readily accessible, quality mental health and substance abuse/addiction care for men, women, youth, and families in an atmosphere that promotes compassion, healing, respect and well-being for all.

  2. hrvatska azdg dating love 21-Apr-2019 06:05

    I also know young people who played the "dating game" and who are now in the ministry, serving the Lord with all their hearts.

  3. Free greek cam chat rooms no registration 29-Jun-2019 19:09

    That includes Mills, a veteran television and film actor, whose only encounter with Alcala was when both of them appeared on "The Dating Game." "That's when I became part of a nightmare, and I didn't realize it was a nightmare until 32 years later," Mills said.

  4. first contact dating sites examples 05-Jul-2019 23:03

    High tobacco use among lesbian, gay, and bisexual youth: Mounting evidence about a hidden population’s health risk behavior.