Permissions on SQL Server

Permissions on SQL Server

                                                  User Permissions on SQL Server some times little bit pane full work if you need to manage lot's of users.(Ex: Domain Users + SQL Server Users) This article I'm going to explain about SQL Server user Permissions, create users ,grants and revoke.

Create User and SET GRANT to Connect only

--initially we have created two databases call MSDN and --Oracle
USE [MSDN]
GO
CREATE LOGIN DatabaseUserGroup WITH PASSWORD = 'DatabaseUserGroup';
CREATE USER DatabaseUserGroup WITH DEFAULT_SCHEMA = DatabaseUserGroup;
GO
GRANT CONNECT TO DatabaseUserGroup;
GO
CREATE SCHEMA DatabaseUserGroup;
GO

Well, now verify above user,

USE [master]
GO
USE MSDN
GO
SELECT * FROM [MSDN]..sysobjects;
GO
USE ORACLE
GO
SELECT * FROM [ORACLE]..sysobjects;
GO

(0 row(s) affected)
Msg 916, Level 14, State 1, Line 1
The server principal "DatabaseUserGroup" is not able to access the database "ORACLE" under the current security context.
Msg 916, Level 14, State 1, Line 1
The server principal "DatabaseUserGroup" is not able to access the database "ORACLE" under the current security context.

According to above error now you can notice, Database "Oracle" can not connect above user(User:DatabaseUserGroup) and Database "MSDN" can connect. For View User Permissions you can use following T-SQL Scripts,

SELECT    spr.[principal_id], spr.[name], spr.[type_desc],
        CASE spr.[is_disabled] WHEN 1 THEN 'Disabled' ELSE 'Enable' END [DisabledOREnable],
        smp.[state_desc], smp.[permission_name]
FROM    sys.server_principals AS spr INNER JOIN
        sys.server_permissions AS smp ON smp.[grantee_principal_id] = spr.[principal_id]
ORDER BY spr.[principal_id];

SELECT
        dp.[class_desc],
        dp.[permission_name],
        dp.[state_desc],
        ObjectName = OBJECT_NAME(dp.[major_id]),
        GranteeName = dpr.[name],
        dpr.[type_desc],
        dpr.[default_schema_name]
FROM sys.database_permissions dp INNER JOIN sys.database_principals dpr
ON dp.[grantee_principal_id] = dpr.[principal_id]
ORDER BY dp.[class_desc],OBJECT_NAME(dp.[major_id]);

SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER');
GO
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

"sp_helplogins" Procedure will help you to find information about logins and the users associated with them in each database. But you need to have Execute Grant for execute this system procedure,

EXEC [master]..sp_helplogins;
GO
EXEC [master]..sp_helpuser;
GO

   

SQL Server Indexes Monitoring

SQL Server Indexes Monitoring

                                                SQL Server Database monitoring is some times little bit boring task for you.But it will be interesting if you doing real investigation on it and Do some R&D or try to think as out of box.Any way today I'm going to post some T-SQL queries will be helpful for Database Administrators or Developers who is doing Index monitoring on their databases.

As a task you should monitor the usage of the standard indexes and consider dropping indexes that are rarely used or not used.You can use the following query to identify indexes that have not been used since the last time SQL Server was started,

USE <your database name>
GO
SELECT    object_name(i.[object_id]) AS [object_name],
        i.name AS [index_name]
FROM sys.indexes i INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE    (o.[type] = 'U') AND
        (NOT EXISTS (SELECT 1 FROM sys.dm_db_index_usage_stats s WHERE s.[index_id] = i.[index_id] AND s.[object_id] = i.[object_id] AND s.[database_id] = db_id()))
ORDER BY object_name(i.[object_id]) ASC;

It's not good to have duplicate indexes in each table. if you have duplicate indexes then you should have to drop them and need to have processes to apply new indexes to given table(Ex: Validate Duplicate index). Following T-SQL queries will help you to find duplicate indexes on give database,

USE <your database name>
GO

;WITH indexcols AS
  (SELECT [object_id] AS id,
    [index_id]        AS indid,
    [name],
    (SELECT
      CASE [keyno]
        WHEN 0
        THEN NULL
        ELSE [colid]
      END                 AS [data()]
    FROM sys.sysindexkeys AS k
    WHERE k.id  = i.[object_id]
    AND k.indid = i.index_id
    ORDER BY keyno,
      colid FOR XML PATH('')
    ) AS cols,
    (SELECT
      CASE keyno
        WHEN 0
        THEN colid
        ELSE NULL
      END                 AS [data()]
    FROM sys.sysindexkeys AS k
    WHERE k.[id]  = i.[object_id]
    AND k.[indid] = i.[index_id]
    ORDER BY colid FOR XML PATH('')
    ) AS inc
  FROM sys.indexes AS i
  )
SELECT    OBJECT_SCHEMA_NAME(ic1.id) + '.' + OBJECT_NAME(ic1.id)  AS [Table],
        ic1.[name]                                              AS [Index Name],
        ic2.[name]                                              AS [Duplicate Index Name]
FROM indexcols ic1 INNER JOIN indexcols ic2 ON ic1.id = ic2.id
    AND (ic1.indid < ic2.indid)
    AND (ic1.cols  = ic2.cols)
    AND (ic1.inc   = ic2.inc); 

 If you want to check index information regular basis then you can use the following query to monitor the index usage on a regular basis.

USE <your database name>
GO

SELECT    object_name(i.[object_id]) AS [object_name],
        i.[name] AS [index_name],
        s.[index_id],
        [user_seeks] + [user_scans] + [user_lookups] AS user_reads,
        [system_seeks] + [system_scans] + [system_lookups] AS system_reads,
        [user_updates],
        [system_updates]
FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i
    ON s.[index_id] = i.[index_id] AND s.[object_id] = i.[object_id]
WHERE    (s.[database_id] = db_id()) AND
        (i.[type] <> 0)
ORDER BY [user_reads] DESC;

If you need to find missing index details in given SQL Server instance then following SQL statement will help you to find it, 

USE <your database name>
GO
SELECT mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,
migs.last_user_seek,
'CREATE NONCLUSTERED INDEX [IX_'+OBJECT_NAME(mid.[object_id])+'_'+REPLACE(REPLACE(REPLACE(REPLACE(mid.equality_columns,'[',''),']',''),',',''),' ','_') +'] ON '+mid.[statement]+' ('+mid.equality_columns+')'+CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE ('+mid.included_columns+') ' ELSE '' END+';' ssql
FROM    sys.dm_db_missing_index_group_stats migs INNER JOIN
        sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle INNER JOIN
        sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY user_seeks DESC;

Hope above SQL statement will help you to find some helpful information about indexes.

Cheers...  

SQL Server XML EDIT(Modify)/REMOVE(Delete)/ADD(Insert)

SQL Server XML values/node Amendments

                                                      My previous post I was discuss about XML data type and validations.This post I'm going to discuss about XML Modify(Insert/Update/Delete). Before start this, may be you all have some confusion which method is good(best practice or performance wise) modify XML data or node, I mean using T-SQL syntax like Replace or XML.Modify. to be honest I haven't good answer for that(I hope T-SQL replace much faster than XML modify) but will will discuss that in later post. 

Edit or Replace(Update) XML text values         

Following SQL script will show you how to edit(modify) XML text values contains in XML,

  DECLARE @xml xml='<root>
  <child id="01" name="Database">
    <Location>Colombo</Location>
    </child>
  <child id="02" name="Database User">
    <Location>Colombo Sri Lanka</Location>
    </child>
</root>';

SELECT @xml [Before Change];
--update value text in the first child
SET @xml.modify('
  replace value of (/root/child/Location/text())[1]
  with     "Colombo, Western Province"
')
SELECT @xml [After Change 01];

-- update attribute value 01
SET @xml.modify('
  replace value of (/root/child/@name)[1]
  with     "Database User Group(www.DatabaseUserGroup.com)"
')
SELECT @xml [After Change 02];

-- update attribute value 02
SET @xml.modify('
  replace value of (/root/child/@name)[2]
  with     "http://www.DatabaseUserGroup.com"
')
SELECT @xml [After Change 03];

 Add (Insert) XML Tags  

Hope you have clear idea about change text values in XML.Following SQL script will explain how to Add XML Tags,

 DECLARE @xml xml='<root>
  <child id="01" name="Database">
    <Location>Colombo</Location>
    </child>
  <child id="02" name="Database User">
    <Location>Colombo Sri Lanka</Location>
    </child>
</root>';

SELECT @xml [Before Change];

-- insert Date attribute          
SET @xml.modify('          
insert attribute Category {"Information technology (IT)"}          
into (/root/child[@id=01])[1] ')          
SELECT @xml [After Change 01];  
       
-- insert Date attribute but its value is retrived from a --sql variable @Date          
DECLARE @Date varchar(25);          
SET @Date =CONVERT(varchar(25),GETDATE(),20);
SET @xml.modify('          
insert attribute CurrentDate {sql:variable("@Date") }          
into   (/root/child[@id=01])[1] ')          
SELECT @xml [After Change 02];
         
-- insert sequence of attribute nodes (note the use of ',' --and ()           
-- around the attributes.  
DECLARE @month varchar(25), @weekday varchar(25), @year varchar(4);          
SET @month        = DATENAME(month, GETDATE());
SET @weekday    = DATENAME(weekday, GETDATE());
SET @year        = DATENAME(year, GETDATE());
       
SET @xml.modify('          
insert (  
            attribute Year {sql:variable("@year") },          
            attribute Month {sql:variable("@month") },          
            attribute Weekday {sql:variable("@weekday") }          
        )          
into (/root/child[@id=01])[1] ')          
SELECT @xml [After Change 03];

DECLARE @country xml;
SET @country = N'<Country>Sri Lanka</Country><PostalCode>00000</PostalCode>';        
-- insert new Tags from specified variable         
SET @xml.modify('          
insert sql:variable("@country")          
into (/root/child)[1] ')          
SELECT @xml [After Change 04];

-- insert comment          
SET @xml.modify('          
insert <!-- some comment -->          
before (/root)[1] ')          
SELECT @xml [After Change 05];

-- insert Program after root
SET @xml.modify('
insert <?Program = "SQLServer.exe" ?>
after (/root)[1] ')
SELECT @xml [After Change 06];

-- insert as First new Tags from specified variable
DECLARE @First xml;
SET @First = N'<First>Start Tag</First>';                  
SET @xml.modify('          
insert sql:variable("@First") as first          
into (/root/child)[1] ')          
SELECT @xml [After Change 07];
GO

Deadlocked on SQL Server

Deadlocked on SQL Server

                                                                                              This week we have face Deadlocked issue on our SQL Server database environment.This is very interesting scenario, But before start topic I want to share Microsoft Tech net  document with you all,      

I have attached print screen of above MS Tech-net document. This is what Microsoft says about IDENTITY columns. Personally I was very interested about the way Microsoft handle auto increment number in SQL Server table compare to Oracle table (Oracle sequence) because its(SQL Server)  very easy. Remember easy way always doubtful :).

Well, Microsoft saying they can not guarantee above condition on IDENTITY column. Let me explain why they says like that,

First you need to create following table,

USE [tempdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblIDENTITY]') AND type in (N'U'))
    DROP TABLE [dbo].[tblIDENTITY];
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblIDENTITY]') AND type in (N'U'))

BEGIN

    CREATE TABLE [dbo].[tblIDENTITY](
        [primaryKey] [int] IDENTITY(1,1) NOT NULL,
        [Comments] [varchar](100) NULL
    ) ON [PRIMARY]

END

GO

After create above table you need to execute following SQL statement with multiple sessions(different SPID's(Ex: 3 sessions or more)),

DECLARE @status int=1;
DECLARE @primaryKey TABLE([primaryKey] int);

WHILE  @status>0 BEGIN
    BEGIN TRAN;

        INSERT INTO tblIDENTITY(Comments) OUTPUT INSERTED.primaryKey INTO @primaryKey VALUES(NULL);
       
        UPDATE tblIDENTITY SET Comments=GETDATE() WHERE ([primaryKey] IN(SELECT [primaryKey] FROM @primaryKey));

        DELETE FROM @primaryKey;

    COMMIT TRAN;

    SELECT @status=@status+1;

    IF @status=10000 BEGIN
        SELECT @status=0;
    END

END

GO

   Hope you will face following Deadlocked error on two or many sessions,

Msg 1205, Level 13, State 45, Line 16
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 Ok... now you will realize why Microsoft purposely added above not guarantee conditions on IDENTITY column. Personally I'm not happy the way Microsoft treated IDENTITY column. 
 And if you face this problem then you need to make-sure IDENTITY column is UNIQUE( PRIMARY KEY or UNIQUE constraint or UNIQUE index).    

Cheers...