Script to Get Database Level,Server Level and Object Level Information with Permissions :
In my working environment I’ve used the script to find security information which should return Server Logins, Database Logins and object level permissions. I’ve used it and thought to sharing this to you since this could be helpful to you.
SQL Server 2012 codenamed SQL Server Denali is on the way to market by 2012. As you all know SQL Server 2012 has new features related to security, we have contained database where you are allowed to create contained users and the next feature is server level roles, other security features are not related to this article, we will cover those later. The above link scripts earlier to retrieve permissions from SQL 2005 and SQL 2008\R2 (link above) however these scripts are no longer useful for SQL Server 2012 as it won’t return the new contained user and server level role permissions.
New columns & DMV’s are added in SQL Server 2012 which will be helpful to retrieve those information’s . In addition to this I’ve added two more scripts which will return server level and database level permissions which is not given in SQL Server 2005 scripts
I came into a situation where the monitoring tool was continually indicating “SSPI Handshake problems,” and the SQL DBA team asked me for help in resolving these issues. The SQL Server Error log folder is monitored by the monitoring programme, and alerts are reported. In this blog, I’ll discuss the root of the problem and the steps we took to resolve it:
Below errors were reported frequently in SQL Error log:
2016-02-07 12:44:22.81 Logon Error: 17806, Severity: 20, State: 14. 2016-02-07 12:44:22.81 Logon SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error tells us the cause of failure. The logon attempt failed [CLIENT: 10.x.x.x] 2016-02-07 12:44:22.81 Logon Error: 18452, Severity: 14, State: 1. 2016-02-07 12:44:22.81 Logon Cannot be used with Windows authentication as Login failed. The login is from an untrusted domain. [CLIENT: 10.x.x.x]
To provide some background on SSPI: The Security Support Provider Interface (SSPI) connects transport-level applications like Microsoft Remote Procedure Call (RPC) with security providers like Windows Distributed Security. SSPI allows a transport application to request an authenticated connection from one of multiple security providers.
For Windows authentication with trustworthy connection, the following argument is typically used in connection strings: Integrated Security=SSPI
There are two types of SSPI errors: “Cannot generate SSPI context “ and “SSPI Handshake Failed”
Cannot generate SSPI context: This issue usually occurs when a client attempts Kerberos authentication and fails, but does not fall back to NTLM authentication. SSPI handshake failed: When the user is not authenticated, we get this error. The error “SSPI Handshake Failed” was seen in the issue we were working on, indicating that the SQL Server was unable to authenticate the user.
To further investigate the error, we examined the security logs in Event Viewer on the SQL Server box at the time of the problem:
An account failed to log on. Subject: Security ID: NULL SID Account Name: – Account Domain: – Logon ID: 0x0
Logon Type: 3
Account For Which Logon Failed: Security ID: NULL SID Account Name: sqlaccount Account Domain: contoso
Failure Information: Failure Reason: The user has not been granted the requested logon type at this machine. Status: 0xC000015B Sub Status: 0x0
Process Information: Caller Process ID: 0x0 Caller Process Name: –
When a logon request fails, this event is triggered. It’s created on the machine where the attempted access was made. The account on the local system that requested the logon is indicated in the Subject columns. A service, such as the Server service, or a local process, such as Winlogon.exe or Services.exe, are frequent examples.
The Logon Type parameter specifies the type of logon requested. The two most popular categories are 2 (interactive) and 3 (network). The account and process on the system that requested the logon are listed in the Process Information fields. The Network Information fields show the origin of the remote logon request. The name of the workstation is not always known, and in some circumstances it may be left blank.
The authentication information fields include precise details about this logon request.
Transited services show which intermediate services were involved in the login request.
The package name identifies which NTLM protocol sub-protocol was utilized.
The length of the created session key is indicated by key length. If no session key was requested, this value will be 0.
We had a lot of data to process from the Security log. Close examination of the logs: The Login Account “Contoso\sqlaccount” is attempting to connect to the SQLServer box from the workstation “SQLClient” with logon type 3: Network (A user or computer signed on to this computer from the network), and receives the error: The user has not been granted the requested login type at this machine.
To isolate the problem, we used the user “contoso\sqlaccount” to log in to the SQLClient box and launched the udl file to connect to the SQL instance:
We got the same error reported in SQL Error log loos like below : The login attempt was unsuccessful. Because the login is from an untrusted domain, Windows authentication will not work.
To resolve the issue:
we added the account “contososqlaccount” to the SQL Server box’s “Access this machine from the network” local security policy (secpol.msc), after which we were able to successfully connect to the instance from the application. Always inspect the security logs after enabling Audit Logon events to resolve SSPI Handshake failure problems.
Another situation of SSPI Handshake Error code 0x8009030c Scenario::
I was working in SSMS the other day and connected to an instance. There’s nothing unusual about that. I later opened a window (I’m not sure what it was–maybe a report or a database option) and was greeted with this error message. I couldn’t do anything in the database after that.
While establishing a connection with integrated security, the SSPI handshake failed with error number 0x8009030c, state 14; the connection has been closed. AcceptSecurityContext failed for some reason. The cause of failure is indicated by the Windows error code. [CLIENT: 10.1.1.1]
The SQL Server log included the same problem with the same error message.
Error: 17806, Severity: 20, State: 14.
My Windows password had expired between the time I started the instance and the time I was looking at something in the database, it turned out. I was good to proceed after I reset my password.