Sql-server – Audit Admin rights use on SQL Server 2019

sql server

Being security conscious, I want to audit the use of admin rights. I would like to assign specific permissions to a user (following the concept of least privilege) that current has the SysAdmin role and I want to know what rights they are using above DBO to the database. Is there a good way to audit these actions so that I can grant those specific rights?

Best Answer

From the nature of the question, it sounds like you're observing the user discreetly before opting to revoke permissions. Definitely get an understanding of what the business is willing to allow the user to perform on the instance, especially if they aren't wearing the DBA hat in any capacity. If you're the DBA of the instance and you're responsible for its uptime, security and integrity, convey your security expectations with the business and ensure your users' logins are in compliance.

That being said, you may want to use a combination of Audits with both Server level and Database level specifications. The groups of actions that can be audited are vast but this will allow you the most precision if you want to ignore particular action groups. As for the indicating the principal (user/login), you can indicate the login you want to track in particular but it may be advisable to track the public role so you can monitor overall use of the high-level actions.

Another approach you can try is to start by looking at the default trace. It may not capture all events and its retention depends on disk space and the amount of qualifying events being generated but can be checked on the fly.

Both the above actions are passive with output checked periodically using SSMS and T-SQL. You can also interactively monitor with Extended Events sessions.

Related Solutions

Sql-server – How to enable a SQL Server 2012 login to create, alter and drop the created databases

First you have to

Create Login with create database permission. Note that the login cannot drop any databases.

USE [master]
GO

CREATE LOGIN [Kin] WITH PASSWORD=N'somePassw0rd', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master]
GO
-- Grant create any database, but not drop or alter any database
 GRANT CREATE ANY DATABASE TO [Kin]
GO

Map the login to the database that you want to have permission to alter or drop.

-- for drop database, you just need db_owner
USE [test_drop]
GO
CREATE USER [Kin] FOR LOGIN [Kin] 
 GO

Add the user to the db_owner role.

  EXEC sp_addrolemember 'db_owner', 'Kin'

Now test it for create / alter and drop :

EXECUTE AS LOGIN = 'Kin'

create database test_drop


EXECUTE AS LOGIN = 'Kin'
use test_drop

create table test (fname char(1))


EXECUTE AS LOGIN = 'Kin'
select * from test

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set offline

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set online

EXECUTE AS LOGIN = 'Kin'
drop database [test_drop]

Try to drop some other db :

EXECUTE AS LOGIN = 'Kin'
alter database [test_kin] 
set online

Below is the error :

Msg 5011, Level 14, State 9, Line 2 User does not have permission to alter database 'test_kin', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed.

SQL Server – Change Login for dbo User

The group or login that is member of the sysadmin role is indepentent of who owns the database. The login that is mapped to the dbo of the database is basically the owner. You can change this via SSMS or with the query below:

ALTER AUTHORIZATION ON DATABASE::[ReportServer] TO [sa];
ALTER AUTHORIZATION ON DATABASE::[ReportServerTempDB] TO [sa];

You can find more information on the specifics about the database owner (dbo) from this question: What is the purpose of the database 'owner'?

Best Answer

Related Solutions

Sql-server – How to enable a SQL Server 2012 login to create, alter and drop the created databases

SQL Server – Change Login for dbo User

Related Question