Disable Guest User in all databases in SQL Server by serverku

As a database security, we should disable guest account from databases, You can do it with revoke access from that user.

USE yourDataBaseName
GO 
REVOKE CONNECT FROM GUEST;
GO

Profile name is not valid - Error when sending an email using sp_send_dbmail in SQL Server by serverku

A week ago I shared some posts related to replication and scheduled jobs information and you may enjoy it. Hope you liked it too. While working with security, suddenly I started to receive an error when sending an email though script using sp_send_dbmail from msdb database specifically for one user and I clicked it was due to changes in access of that user. The analysis was going long and checked user access to msdb databases and it has db_datareader, and DatabaseMailUserRole and failed to send an email. Even it was not working, assigned db_owner to that user in msdb database.
Finally came to solution using sysmail_add_principalprofile_sp system object which grants permission for a database user or role to use a specified Database Mail profile,

USE [msdb]
GO
-- DatabaseMailUserRole database role should be assigned to user if user is not db_owner database role and sysadmin server role
EXEC sp_addrolemember N'DatabaseMailUserRole', N'UserName' -- Put user name here
GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'ProfileName', -- Put pfofile name here
    @principal_name = 'UserName', -- Put user name here
    @is_default = 1 ; 

YYou can also make the same changes from the user interface. Goto Database Mail, right click and goto Configure Database Mail , select an option Manage Profile Security, Go to Private Profiles Tab, Select User name and check the box of Access and make a default profile to Yes for the profile name using which we want to send an email.

After above change it was succeeded to send an email. Stay tuned for more.

Logon trigger in SQL Server by serverku

As a secure part, recently I worked with login details and the auditing for the same. I needed to capture each event for logging statistics like login name, the time when logon, the program through established connection, session and host or client IP. This event is raised when user sessions make connections with SQL Server instances where we configured LOGON trigger. This will helpful for us to make auditing the logon details for each connections. For the small demo let us create required objects and implement it.

USE DemoDB
GO
-- Creating audit table
CREATE TABLE LogonAuditing
(
 SessionId int,
 LogonTime datetime,
 HostName varchar(50),
 ProgramName varchar(500),
 LoginName varchar(50),
 ClientHost varchar(50)
)
GO

USE Master
GO
-- Creating DDL trigger for logon
CREATE TRIGGER LogonAuditTrigger
 ON ALL SERVER 
FOR LOGON
AS
BEGIN
       DECLARE @LogonTriggerData xml,
                     @EventTime datetime,
                     @LoginName varchar(50),
                     @ClientHost varchar(50),
                     @LoginType varchar(50),
                     @HostName varchar(50),
                     @AppName varchar(500)
                     
       SET @LogonTriggerData = eventdata()
  
    SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
       SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
       SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
       SET @HostName = HOST_NAME()
       SET @AppName = APP_NAME()--,program_name()
 
 INSERT INTO DemoDB.dbo.LogonAuditing
  (
  SessionId,
  LogonTime,
  HostName,
  ProgramName,
  LoginName,
  ClientHost
 )
 SELECT 
  @@spid,
  @EventTime,
  @HostName,
  @AppName,
  @LoginName,
  @ClientHost

END
GO

Audit table and the trigger to fill that table is created. Now it is time to evaluate, test it and review the audit table.

We have all details of the logging events from the table. Also one more interesting thing is as we can prevent unwanted user logins and connections to SQL Server. This prohibition can be for the login, program or host, for that we just need to add some code in logon trigger for conditions to make rollback at that time like following,

-- Preventing 'sa' login
IF @LoginName = 'sa'
BEGIN
ROLLBACK;
END
  
-- Preventing the connections from SSMS
IF @AppName = 'Microsoft SQL Server Management Studio'
BEGIN
ROLLBACK;
END

For first criteria to prevent login ‘SA’, if connection made for the same and the message fired at the time of event logging,

You can make your criteria as per requirement as I implemented for login and program connections. Are you using a Logon Trigger?

Alter failed for Login sa. Cannot set a credential for principal 'sa'. - Error encountered in SQL Server by serverku

Recently, when I worked with SQL Server security, I encountered with one error while trying to modify 'SA' account properties. The exception details looks following,

Alter failed for Login sa. Cannot set a credential for principal 'sa'.

The fix for the error is the option "Map to Credential" is checked in the "General" tab of the Login Properties Page as mentioned below,

Hope this help you.

Application Role in SQL Server by serverku

In the last post we saw custom database roles as how can we create it and assign required access to users. We also noticed that we can add multiple members with the same role. That was the security with database roles and members comes into the picture. Now here we will study of Application Role. This is the security for the application level and no such members comes into the picture.

Application Role :
As per msdn, An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application

Workaround:
We can implement application role and take into effect with the following steps, I am going to here with some of the examples, so like to create those required objects, so we can set them with application role.

1. Create required objects

USE demo
GO

CREATE TABLE SampleTable1
(
Id int,
Name varchar(10)
)
GO

CREATE TABLE SampleTable2
(
Id int,
Name varchar(10)
)
GO

CREATE PROCEDURE SampleSP1
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable1
End
GO

CREATE PROCEDURE SampleSP2
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable2
End
GO

Here we have created same required objects as we created in an earlier post for database role.  

2. Create an application role

USE demo
GO

-- sp_addapprole [@rolename = ] 'rolename', [@password = ] 'password'

EXEC sp_addapprole 'AppRole', 'AppPwd'
GO

3. Add permission to this application role

USE demo
GO

GRANT SELECT ON SampleTable1 to AppRole;
GRANT SELECT, INSERT,UPDATE ON SampleTable2 to AppRole;
GRANT EXEC ON SampleSP1 to AppRole;
GO

You can see we have assigned same access to the application role as we did in an earlier post for the custom database role. Application role is created here now, You can also create/view with expanding Roles inside security tab for a particular database and inside it you can find the Application Role tab.

4. Connecting database and activating application role

Application roles are enabled/activated by sp_settapprole system stored procedure and it has required a password. So an application can be connected to SQL Server with this application role with scope of the particular session and required a password to authenticate it to connect SQL Server. To authenticate application roles and activate it it should be required to use in.Net connection code or other application database connection method code. You can refer the link here to use application role in application code.

sp_settapprole system stored procedure activate the application role for the specific connection while connecting with the application and the syntax is as follows,

USE demo
GO

-- sp_setapprole [@rolename = ] 'rolename', [@password = ] 'password'

EXEC sp_setapprole 'AppRole', 'AppPwd'
GO

We can change the password for the application role with following,

USE demo
GO

-- sp_approlepassword [@rolename = ] 'rolename', [@password = ] 'new password'

EXEC sp_approlepassword 'AppRole', 'AppChangedPwd'
GO

Hope these all the steps are enough to implement application role. Stay tuned for more.

Custom Database Role in SQL Server by serverku

Recently, while working with database security, I learned database roles as how the each rule used. Apart from the server level roles if we need to require to assign access/rights to the particular database level, then we need to go through database level roles.

Following are the fixed database level roles as per MSDN,
db_owner :Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin :Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin :Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator :Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin :Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter :Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader : Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter :Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader : Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

You can see the image in all above fixed database roles. Now we will see how can we use the roles and bind with users. Let's create a small demo with examples. Here I am creating required objects used for demos, So let's do that.

USE demo
GO

CREATE TABLE SampleTable1
(
Id int,
Name varchar(10)
)
GO

CREATE TABLE SampleTable2
(
Id int,
Name varchar(10)
)
GO

CREATE PROCEDURE SampleSP1
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable1
End
GO

CREATE PROCEDURE SampleSP2
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable2
End
GO

Now here I want to assign the access to user as follows,
1. User can see the data from SampleTable2 table.
2. User can perform select/insert/update operation on SampleTable1 table, not delete operation.
3. Can execute stored procedure SampleSP1.
4. Can not execute stored procedure SampleSP2.

Workaround 1:
If we assigned fixed database roles to User like db_datareader, and db_datawriter for a particular database, then user can perform all DML operations on all tables, views in the database. Even if we assigned EXECUTE permission to the user then the user can execute all the stored procedures. After all these permissions we need to deny permission from the user for some of the tables and stored procedures which are not required to be accessed.

Workaround 2:
Instead of doing above such stuffs, We will create a new custom database level role and assigned requited access to use for the objects.

USE demo
GO

CREATE LOGIN [SupportUser] 
WITH PASSWORD=N'SupportUser', 
 DEFAULT_DATABASE=[master], 
 DEFAULT_LANGUAGE=[us_english], 
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

CREATE USER [SupportUser] FOR LOGIN [SupportUser]
GO

CREATE ROLE [SupportRole] 
Go

GRANT SELECT ON SampleTable2 TO [SupportRole];
GRANT SELECT,INSERT,UPDATE ON SampleTable1 to [SupportRole];
GRANT EXEC ON SampleSP1 to [SupportRole]
GO

EXEC sp_addrolemember N'SupportRole', N'SupportUser'
GO

Let's connect the SQL Server instance with this newly created user and see the access rights,

USE demo
GO

PRINT 'Inserting in SampleTable1'
GO
INSERT INTO SampleTable1
 (
 Id, 
 Name
 )
SELECT 
 1,
 'Sample1'
GO

PRINT 'Inserting in SampleTable2'
GO
INSERT INTO SampleTable2
 (
 Id, 
 Name
 )
SELECT 
 1,
 'Sample2'
GO

PRINT 'Deleting from SampleTable1'
GO
DELETE FROM SampleTable1
GO

PRINT 'Viewing from SampleTable1'
GO
SELECT * FROM SampleTable2
GO
SELECT * FROM SampleTable1
GO


PRINT 'Executing SampleSP11'
GO
EXEC SampleSP1
GO
PRINT 'Executing SampleSP2'
GO
EXEC SampleSP2
GO

You can see the below image to see the access by running user,

The main benefit of the custom database role is role can be assigned to multiple users. You can see below script where I have assigned the same role to different users. So once role created it can be assigned to multiple users.

USE demo
GO

CREATE LOGIN [DBAUser] 
WITH PASSWORD=N'DBAUser', 
 DEFAULT_DATABASE=[master], 
 DEFAULT_LANGUAGE=[us_english], 
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

CREATE USER [DBAUser] FOR LOGIN [DBAUser]
GO

EXEC sp_addrolemember N'SupportRole', N'DBAUser'
GO

Hope you like this post.