Security is for sure one of the most important areas that we, as professional DBAs, must cover. Within SQL Server, there are many ramifications that can derive from the security topic itself. In this article, I want to focus on an entry-level security topic that covers something very basic.
To continue with my series of Stored Procedures for a professional DBA, I have created a new tool that can be of great help to any SQL Server DBA.
Initial Considerations
- Make sure that the account executing this Stored Procedure has enough privileges.
- The database objects (database table and stored procedure) will be created inside the database selected at the time that the script is executed, so choose carefully.
- The script is crafted in a way that it can be executed multiple times without getting an error thrown. For the Stored Procedure, I used the CREATE OR ALTER PROCEDURE statement, available since SQL Server 2016 SP1.
- Feel free to change the name of the created database objects.
- When you choose to persist data returned by the Stored Procedure, the target table will be first truncated so only the most recent result set will be stored. You can make the necessary adjustments.
How to Use the Stored Procedure?
- Copy & paste the TSQL Code (available within this article).
- Execute it as you would normally execute any given Stored Procedure. For this particular Stored Procedure, no parameters are required, as opposed to the other Stored Procedures I have previously shared, and you will see why.
Fields Presented and Their Meaning
- login: the name of the login.
- created_date: the datetime value when the login was created.
- is_disabled: 1 if the login is disabled and 0 if it’s enabled.
- login_type: SQL_Login or Windows_Login.
- sysadmin: 1 if the login has sysadmin role assigned and 0 if it doesn’t.
- serveradmin: 1 if the login has serveradmin role assigned and 0 if it doesn’t
- securityadmin: 1 if the login has securityadmin role assigned and 0 if it doesn’t.
- processadmin: 1 if the login has processadmin role assigned and 0 if it doesn’t.
- setupadmin: 1 if the login has setupadmin role assigned and 0 if it doesn’t.
- bulkadmin: 1 if the login has bulkadmin role assigned and 0 if it doesn’t.
- diskadmin: 1 if the login has diskadmin role assigned and 0 if it doesn’t.
- dbcreator: 1 if the login has dbcreator role assigned and 0 if it doesn’t.
- public: always 1 because it is assigned by SQL Server and it can’t be removed.
Additional Unspecified Fields
Since custom server roles can be created within a SQL Server instance, there will be the need to capture the logins that have those custom roles assigned. In this case, the Stored Procedure makes sure that the dynamically created table structure takes those server roles into account.
Execution Tests
I will demonstrate a few executions of the Stored Procedure so that you can get an idea of what to expect from it:
* You can find the complete TSQL code of the script in the tail of this article, so make sure to grab it and execute it prior to moving along with the following section.
* Since the result set is too wide to fit in one single screenshot, I will split them so that you can see the full picture.
Let me execute the Stored Procedure in my test instance that only has the default server roles.
EXEC GetLoginsData
SELECT * FROM DBA_Logins
To verify that the collected data is valid, let me take a look at the server roles assigned for login ‘testlogin2’ in SSMS:
Bingo, it has the same roles assigned.
Now let me create a couple of custom server roles, assign them to a couple of logins, and re-execute the Stored Procedure.
EXEC GetLoginsData
SELECT * FROM DBA_Logins
As you can see, ‘custom_role1’ and ‘custom_role2’ now magically show up without you having to modify a single line of code within the Stored Procedure.
Side Queries
I have prepared a few queries that can help you to obtain useful information from the data persisted in the table.
* Query to find logins with highly privileged server roles.
* Feel free to add any custom role that you consider pertinent.
SELECT * FROM DBA_Logins
WHERE sysadmin = 1 OR securityadmin = 1 OR serveradmin = 1;* Query to find all the logins that are currently disabled in your instance.
SELECT * FROM DBA_Logins
WHERE is_disabled = 1;* Query to find the logins that have the capability to create user databases in the instance.
SELECT * FROM DBA_Logins
WHERE dbcreator = 1 AND is_disabled = 0;Here’s the complete code of the Stored Procedure:
CREATE OR ALTER PROCEDURE GetLoginsData
AS
BEGIN
SET NOCOUNT ON;
DECLARE @role NVARCHAR(32)
DECLARE @login NVARCHAR(64)
DECLARE @sqlCommand NVARCHAR(MAX)
DROP TABLE IF EXISTS DBA_Logins
SET @sqlCommand = '
CREATE TABLE DBA_Logins(
[login] NVARCHAR(64) NOT NULL,
[created_date] DATETIME NOT NULL,
[is_disabled] BIT NOT NULL,
[login_type] NVARCHAR(13)
'
DECLARE roles_cursor CURSOR FOR
SELECT name
FROM sys.server_principals
WHERE type = 'R'
OPEN roles_cursor
FETCH NEXT FROM roles_cursor INTO @role
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand += ',['+@role+'] BIT DEFAULT 0 NOT NULL'
FETCH NEXT FROM roles_cursor INTO @role
END
SET @sqlCommand += ',[data_collection_timestamp] DATETIME NOT NULL)'
CLOSE roles_cursor
DEALLOCATE roles_cursor
EXEC(@sqlCommand)
INSERT INTO DBA_Logins ([login],[created_date],[is_disabled],[login_type],[public],[data_collection_timestamp])
SELECT [name],[create_date],[is_disabled],[type_desc],1,GETDATE()
FROM sys.server_principals
WHERE type IN ('S','U') AND ([name] NOT LIKE '%##MS_%' AND [name] NOT LIKE '%NT %')
DECLARE roles_cursor CURSOR FOR
SELECT sp.name,ISNULL(SUSER_NAME(srm.role_principal_id),'public') AS 'server_role'
FROM sys.server_principals sp
LEFT JOIN sys.server_role_members srm ON sp.name = SUSER_NAME(srm.member_principal_id)
WHERE sp.type IN ('S','U') AND (sp.[name] NOT LIKE '%##MS_%' AND sp.[name] NOT LIKE '%NT %')
OPEN roles_cursor
FETCH NEXT FROM roles_cursor INTO @login,@role
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = '
UPDATE DBA_Logins SET ['+@role+'] = 1 WHERE [login] = '+CHAR(39)+@login+CHAR(39)+'
'
EXEC(@sqlCommand)
FETCH NEXT FROM roles_cursor INTO @login,@role
END
CLOSE roles_cursor
DEALLOCATE roles_cursor
END
GO
Conclusion
- You can deploy this SP in every SQL Server instance under your support and implement an alerting mechanism across your entire stack of supported instances.
- If you implement an agent job that is querying this information relatively frequently, you can verify if the logins with high privileges in your instance are supposed to have such elevated rights.
- Ideally, you want to establish a frequent inspection routine to perform the cleanup of logins that are just sitting in your instance with no purpose at all. You can also enforce a policy to allow only the logins that serve a business purpose.
- An SP for database-specific roles might be on the pipeline, so stay tuned!
