The SQL Ideas

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.

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

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_ddladmi

During working with one logic, I got a chance to work with PIVOT operation. Sometime we need do require row data as a column in our custom logic, then we can use some temp table and then populate aggregate data in a temp table . But With PIVOT we can do it very easily. Let me prepare small example and explain as how how can we use PIVOT and get row data as a column . Before going ahead to run the script of Pivot, we will create a database and table objects. CREATE DATABASE DEMO GO USE DEMO GO -- Creating table for demo IF (object_id('TblPivot','U') > 0) DROP TABLE TblPivot CREATE TABLE TblPivot ( ItemCode int, ItemName varchar(100), ItemColour varchar(50) ) GO -- Inerting some sample records INSERT INTO TblPivot SELECT 1,'Samsung Mobile','Red' UNION ALL SELECT 2,'Nokia Mobile','Blue' UNION ALL SELECT 3,'Nokia Mobile','Green' UNION ALL SELECT 4,'Motorola Mobile','Red' UNION ALL SELECT 5,&