1

I'm trying to forbid database user to get SQL server internal information.

Script that creates DB user:

CREATE USER [testuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]

DENY SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: sys TO [testuser] 
DENY SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: INFORMATION_SCHEMA TO [testuser] 
GRANT SELECT on SCHEMA::dbo to [testuser]
DENY INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: dbo TO [testuser] 

The problem is that user is still able to run queries like these:

EXECUTE AS USER   = 'testuser';
GO

SELECT DB_NAME() , @@VERSION
SELECT ConnectionProperty('net_transport')
SELECT * FROM sys.login_token

Do you have any ideas?

1

I am afraid that there is no way to deny anyone permissions to see that kind of information. However, impersonating a database user is not enough to see the effects on server-scoped DMVs or functions such as sys.login_token: you need to impersonate a login instead.

CREATE LOGIN ZeroCool WITH PASSWORD = 'ICantSeeCoolInfo', CHECK_POLICY = OFF;
GO

EXECUTE AS LOGIN = 'ZeroCool';
GO

SELECT DB_NAME() , @@VERSION
SELECT ConnectionProperty('net_transport')
SELECT * FROM sys.login_token
GO

REVERT;

Results:

------------------------------------------------------------------------ -----------------------------------------------
master                                                                   Microsoft SQL Server 2012 - 11.0.5613.0 (X64) 
    May  4 2015 19:05:02 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )


(1 row(s) affected)


--------------
Shared memory

(1 row(s) affected)

principal_id sid                                  name         type          usage          
------------ ------------------------------------ ------------ ------------- ---------------
301          0x34FA109FE0F7694E866DF0F9E82BB99B   ZeroCool     SQL LOGIN     GRANT OR DENY
2            0x02                                 public       SERVER ROLE   GRANT OR DENY

(2 row(s) affected)

I don't see how preventing users from seeing those info will help you tighten security: just implement security best practices and you should be ok.

1
  • There is one place in application where we have to allow user defined SQL queries. All sensitive data is stored in separate DB so user cannot get data that doesn't belong to him. The only disappointing thing that user can inspect technical information from SQL server. Anyway, thanks for the answer. Maybe, I'll try to filter this on application layer. Nov 20 '15 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.