Is there a way to test security permissions in tSQLt?
Currently I am trying to test an accounts security and the results return from tSQLt are not what I am expecting. I am expecting on error that the account does not have permissions, but it appears that the "test" is using my account permissions and succeeding.
EXEC tSQLt.NewTestClass @ClassName = N'TestSecurity' -- nvarchar(max)
go
CREATE PROCEDURE TestSecurity.[test execute as security]
AS
BEGIN
EXECUTE AS USER = 'account3'
SELECT *
FROM dbo.User AS u
REVERT
END
GO
CREATE PROCEDURE TestSecurity.[test security on WebUser]
AS
BEGIN
EXECUTE AS USER = 'account3'
SELECT *
FROM dbo.WebUser AS wu
REVERT
END
GO
EXEC tSQLt.Run
@TestName = N'TestSecurity'
--,@TestResultFormatter = N'' -- nvarchar(max)
Results are two table results printed and 2 successful tests:
(1 row affected)
+----------------------+
|Test Execution Summary|
+----------------------+ '
|No|Test Case Name |Dur(ms)|Result |
+--+-----------------------------------------+-------+-------+
|1 |[TestSecurity].[test execute as security]| 257|Success|
|2 |[TestSecurity].[test security on SecUser]| 27|Success|
-----------------------------------------------------------------------------
Test Case Summary: 2 test case(s) executed, 2 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------
When is reality the dbo.User table should have failed and the WebUser table should succeed? I expect an error but I get results back?
The error I expect to get back should look like
-- run
EXECUTE AS USER = 'account3'
SELECT * FROM dbo.WebUser AS wu
-- returns
Msg 229, Level 14, State 5, Line 4
The SELECT permission was denied on the object 'WebUser', database '...', schema '...'.
I understand I should add tSQLt.ExpectException, but it is not even throwing an exception there are results coming back. I was trying to figure out how it is executing right now and then add the tSQLt Exception check. There is no security to return results so I am confused.
Update 1 Executing
CREATE PROCEDURE TestSecurity.[test execute as security]
AS
BEGIN
EXECUTE AS USER = 'account3'
-- EDIT
SELECT *
FROM sys.user_token AS ut
SELECT *
FROM dbo.User AS u
REVERT
END
GO
Returns
name type usage
account3 SQL USER GRANT OR DENY
public ROLE GRANT OR DENY
db_securitycheck ROLE GRANT OR DENY
db_executor ROLE GRANT OR DENY
db_webexecutor ROLE GRANT OR DENY
dbo SQL USER AUTHENTICATOR
db_owner ROLE AUTHENTICATOR
Which kinda of makes sense except db_owner which are my permissions not the account3 permissions.