Monday 3 December 2012

db_securityadmin Limitation


Recently I was working on a security audit where i was required to give a SQL Login permissions to run the following Query,
 
CREATE LOGIN Vinoth WITH PASSWORD='', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [Vinoth] FOR LOGIN [Vinoth]
sp_addrolemember 'db_datareader', Vinoth
sp_addrolemember 'db_datawriter', Vinoth
CREATE SCHEMA Vinoth AUTHORIZATION Vinoth
ALTER USER [Vinoth ] WITH DEFAULT_SCHEMA=[Vinoth]
 
The SQL Login was initially assigned Sysadmin by the third party which we obviously did not want to grant. So we gave the user
the following rights to run the task,
  1. Security Admin(Server Role)
  2. db_accessadmin
  3. db_securityadmin
  4. CREATE SCHEMA Rights
  5. ALTER USER Rights.
After which he was able to execute all the task successfully except for adding rolemember part,
 
sp_addrolemember 'db_datareader', Vinoth
sp_addrolemember 'db_datawriter', Vinoth
 
Msg 15247, Level 16, State 1, Procedure sp_addrolemember, Line 51
User does not have permission to perform this action.


Even though the user had db_securityadmin rights he was not able to run the following query. On further research I found the permissions required for sp_addrolemember  as below from MSDN,
 
"Only members of the sysadmin fixed server role and the db_owner fixed database role can execute sp_addrolememberto add a member to fixed database roles. Role owners can execute sp_addrolemember to add a member to any SQL Server role they own. Members of the db_securityadmin fixed database role can add users to any user-defined role."
 
Eventually no fixed db roles can be added so we ended up automating the process with a Store Proc EXECUTE AS clause to avoid giving user elevated rights.

Wednesday 14 November 2012

SQL Server TCP Port Number


Today I came accross an interesting TSQL in the blog spicifed below to find out the TCP Port number SQL Server instance was listening on,
 
 
SELECT      e.name,
            e.endpoint_id,
            e.principal_id,
            e.protocol,
            e.protocol_desc,
            ec.local_net_address,
            ec.local_tcp_port,
            e.[type],
            e.type_desc,
            e.[state],
            e.state_desc,
            e.is_admin_endpoint
FROM        sys.endpoints e 
            LEFT OUTER JOIN sys.dm_exec_connections ec
                ON ec.endpoint_id = e.endpoint_id
GROUP BY    e.name,
            e.endpoint_id,
            e.principal_id,
            e.protocol,
            e.protocol_desc,
            ec.local_net_address,
            ec.local_tcp_port,
            e.[type],
            e.type_desc,
            e.[state],
            e.state_desc,
            e.is_admin_endpoint
 
The Script works perfectly fine, I tried using DISTINCT instead of Group by that works fine too. Thanks to the blogger for the script.

Wednesday 17 October 2012

Exec Store Proc user defined Role


Several of my client request for a role which has execute permission to all store procedures in a database, most of the time I have seen DBA's giving GRANT EXECUTE permission to each and every store procedure which is also correct except for the fact that if any new store procedures get created after that we would need to remember to explicitly grant the Execute permission again. There is a much better way in which you can handle this in SQL Server as mentioned below,

--create a new role for EXEC SP
CREATE ROLE db_storeproc_executor
--grant Execute permission to the Role
GRANT EXECUTE TO db_storeproc_executor
--Assign the Role to the DB user
EXEC sp_addrolemember 'db_storeproc_executor', 'username'

By granting just EXECUTE permission to a role we make sure exec permissions are granted to any new Store Proc created.

Wednesday 10 October 2012

Behaviour of log chain with Full backup


Will the Log chain break when we take a full backup of a database?
 
As per Microsoft documents, A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery. (http://msdn.microsoft.com/en-us/library/ms190440(v=sql.105).aspx)
 
Let see some exercise to verify this,
 
Exercise 1:
 
--Create Database
CREATE DATABASE PowerShellTest
 
--Now i create a Full Backup 1, This the intial backup after DB creation
BACKUP DATABASE PowerShellTest TO DISK = 'F:\PowerShellTest1.bak'
WITH NAME = 'Full Backup 1',STATS = 1
 
--Lets Query MSDB backupset to see the backup details
SELECT * from msdb..backupset where database_name = 'PowerShellTest'
 
--create logged operation
CREATE TABLE TEST
( col1 int)
 
--Now start Log chain , Log Backup 1
BACKUP LOG PowerShellTest TO DISK = 'F:\PowerShellTest_log1.trn'
WITH NAME = 'Log Backup 1',STATS = 1
 
--Query MSDB backupset again to see the backup details
SELECT * from msdb..backupset where database_name = 'PowerShellTest'
 
--continue logged operation
CREATE TABLE TEST1
( col2 int)
 
--continue Log chain,Log Backup 2
BACKUP LOG PowerShellTest TO DISK = 'F:\PowerShellTest_log2.trn'
WITH NAME = 'Log Backup 2',STATS = 1
 
--Now I am taking a Full backup Full Backup 2, This is the 2nd Full backup after DB creation and if full backup breaks the log chain it should happen here.
BACKUP DATABASE PowerShellTest TO DISK = 'F:\PowerShellTest2.bak'
WITH NAME = 'Full Backup 2', STATS = 1
 
--continue logged operation
CREATE TABLE TEST2
( col3 int)
 
--continue Log chain, as per MSDN records this should still point to Full Backup 1:Log Backup 3
BACKUP LOG PowerShellTest TO DISK = 'F:\PowerShellTest_log3.trn'
WITH NAME = 'Log Backup 3',STATS = 1
 
--Query DB
SELECT * FROM sys.objects where type = 'U' -- 3 Rows showing Tbls TEST,TEST1 and TEST2
 
--drop Database to recover
DROP DATABASE PowerShellTest
 
--Now I am Restoring the DB using Backup 1 in NORECOVERY mode
RESTORE DATABASE PowerShellTest FROM DISK = 'F:\PowerShellTest1.bak'
WITH NORECOVERY,STATS = 1
 
--If the Log backup chanin is not broken by the FULL Backup 2 then we should be able to Restore Log 1,2,3 to prove Backup 2 did not break Log chain, Let's try
RESTORE LOG PowerShellTest FROM DISK = 'F:\PowerShellTest_log1.trn' WITH NORECOVERY,STATS = 1
RESTORE LOG PowerShellTest FROM DISK = 'F:\PowerShellTest_log2.trn' WITH NORECOVERY,STATS = 1
RESTORE LOG PowerShellTest FROM DISK = 'F:\PowerShellTest_log3.trn' WITH RECOVERY,STATS = 1
 
And Yes I was able to recover the database successfully from Full backup 1 and Log 1,2 and 3 proving the Log Chain was not broken by the subsequent FUll backups!!!!
 
--Query DB
SELECT * FROM sys.objects where type = 'U' 3 Rows showing Tbls TEST,TEST1 and TEST2 proving the DB was successfully restored.
 
As per MSDN: To identify the backup that starts the log chain in a set of backups, query the begins_log_chain column of the backupset table, or run RESTORE HEADERONLY on the backup device to see the BeginsLogChain column in the results set. So I ran the below TSQL,
 
SELECT name,database_name,begins_log_chain,type from msdb..backupset where database_name = 'PowerShellTest'
 

namedatabase_namebegins_log_chaintype
Full Backup 1PowerShellTest0D
Log Backup 1PowerShellTest1L
Log Backup 2PowerShellTest0L
Full Backup 2PowerShellTest0D
Log Backup 3PowerShellTest0L

 
As expected Log backup chain starts from Log Backup 1.
 
Exercise 2:
 
Lets see how log chains are broken,
 
I follow the same steps as in Exercise 1 till Log backup 1 is taken,
 
--Create Database
CREATE DATABASE PowerShellTest1
--Full Backup 1
BACKUP DATABASE PowerShellTest1 TO DISK = 'F:\PowerShellTest1_FULL1.bak'
WITH NAME = 'Full Backup X1',STATS = 1
 
--backup set1
SELECT * from msdb..backupset where database_name = 'PowerShellTest1'
 
--create logged operation
CREATE TABLE TEST
( col1 int)
 
--Log chain start, Log Backup 1
BACKUP LOG PowerShellTest1 TO DISK = 'F:\PowerShellTest1_log1.trn'
WITH NAME = 'Log Backup X1',STATS = 1
 
At this point i change the recovery model of the database,
 
--Change Recovery Model
ALTER DATABASE PowerShellTest1 SET RECOVERY SIMPLE
ALTER DATABASE PowerShellTest1 SET RECOVERY FULL
 
--Now Again I take a Full Backup 2
BACKUP DATABASE PowerShellTest1 TO DISK = 'F:\PowerShellTest1_FULL2.bak'
WITH NAME = 'Full Backup X2',STATS = 1
 
--continue logged operation
CREATE TABLE TEST1
( col2 int)
 
--Now I take a Log backup 2, As per MSDN the log chain should have been broken with Recovery model change and this log backup should be start of new log chain
BACKUP LOG PowerShellTest1 TO DISK = 'F:\PowerShellTest1_log2.trn'
WITH NAME = 'Log Backup X2',STATS = 1
 
--Now Lets verify the backupset to see in which backups Log chain begins,
SELECT name,database_name,begins_log_chain,type from msdb..backupset where database_name = 'PowerShellTest1'
 

namedatabase_namebegins_log_chaintype
Full Backup X1PowerShellTest10D
Log Backup X1PowerShellTest11L
Full Backup X2PowerShellTest10D
Log Backup X2PowerShellTest11L

 
There You GO ! The Log backup taken after recovery model change shows the Log chain is reinitialized again, hope the blog was useful.

Thursday 4 October 2012

How to reset stats in sys.dm_os_wait_stats view


One of main DMV's used in SQL Server for performance tuning is sys.dm_os_wait_statsthe view returns all the waits executed by a SQL server process.one of the main problem with the this DMV is all the data are cumulative since the last time the statistics were reset or the server was started, so whenever you want to analyse a performance issue its good to reset the counter in the DMV to 0 before you want baseline the performance.
 
Run the below TSQL to reset the counters in sys.dm_os_wait_stats,
 
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
 

Differential backup and differential_base_lsn

In this blog I would like to explain where the Differential backup metadata(LSN details) are stored with an example, Recently I took a Full backup of a very large database DB1 on a SQL instance SQLServerA and restored it to different server instance called SQLServerB,

--Exec on SQLServerA
BACKUP DATABASE DB1 TO DISK = '<backup_path_1>' WITH STATS = 1
--Exec on SQL ServerB
RESTORE DATABASE DB2 FROM DISK = '<backup_path_1>' WITH RECOVERY,STATS = 1
Since we had a differenential backup scheduled on the SQLServerB I was not sure if I need to take a full backup of the database DB2 before the differential backup kicks, but to my surprise the differential backup completed successfully for DB2 database even though there is no full backup of the database in SQLServerB instance.
 
To check the consistency i used the full backup from SQLServerA and differential backup from SQLServerB to see i can restore the database
--Exec on SQLServerB
BACKUP DATABASE DB2 TO DISK = '<diff_backup_path>' WITH DIFFERENTIAL,STATS = 1
--Exec on SQL ServerB using the full backup of SQLServerA.DB1
RESTORE DATABASE DB3 FROM DISK = '<full_backup_path_1>' WITH NORECOVERY,STATS = 1
--Exec on SQL ServerB using the differential backup of SQLServerB.DB2
RESTORE DATABASE DB3 FROM DISK = '<diff_backup_path>' WITH RECOVERY,STATS = 1
I was able to recover DB3 from full backup of DB1 and Diff backup of DB2, which made me think where does the restore task looks for the LSN detail on a differential restore, I wanted to take Log file out of the equation so i recreated the log file for the DB2 database.
 
EXEC sp_detach_db 'DB2', 'true';
CREATE DATABASE DB2
ON (FILENAME = '<db_path>')
FOR ATTACH_REBUILD_LOG
 
Now after the new log was built I tried to take a differential backup again,
 
--Exec on SQLServerB
BACKUP DATABASE DB2 TO DISK = '<diff_backup_path>' WITH DIFFERENTIAL,STATS = 1
 
And it WORKED!!!!
 
After referring MSDN site I found the metadata that is required by a differential backup, such as the log sequence number at which the differential backup begins (the differential base LSN) is stored in the master database. The data is stored in system tables sys.master_files for read-only database and sys.database_files for Read/Write database using the following columns,

differential_base_lsn
numeric(25,0)
Base for differential backups. Data extents changed after this LSN will be included in a differential backup.
differential_base_guid
uniqueidentifier
Unique identifier of the base backup on which a differential backup will be based.
differential_base_time
datetime
Time corresponding to differential_base_lsn.
 
So when I did the first restore of DB2 these columns were updated on master database and it was referencing these columns after that. So you can use differential backup on different servers without any reference any other metadata.

Hope this exercise helps,More details on differential backup can be found @ http://msdn.microsoft.com/en-us/library/ms175526.aspx

Tuesday 25 September 2012

How to Rename SQL Server

With the release of SQL 2012 most of the companies might have started migration of SQL server to new version, In case of server migrations most of the time we will end up in a situation to rename the physical server hosting SQL Server, Renaming SQL Server to host the new physical server name is pretty simple as done below

MSDN Reference for Add Server http://msdn.microsoft.com/en-us/library/ms174411.aspx
As said in the article, This procedure must be executed on all instances of the Database Engine hosted on the computer. The instance name of the Database Engine cannot be changed.
 
On the SQL Server instance, run the following query.
sp_dropserver '<oldservername>'
go

sp_addserver '<newservername>', local
go

Now restart the SQL Server related services. Then, run @@servername to verify if the new name has been updated.
select @@servername

Monday 3 September 2012

Process Explorer


As a DBA you land in situation where you need to find which process is using a File on the disk,
 
For Eg:- Your Tape backup process may be holding a old backup from Archiving or using it of Restore or a Application process may be holfing a File used in ETL making the ETL process to fail.
 
The best tool to find which WIN process is holding Flat files is "Process Explorer"
 
The exe is found in Windows Resource tool kit and does not need any install, all you need to do is copy the exe and start using it.

I have been using this for nearly 10 years now and works perfect every time. All you need to do is enter the file name are part of filename in the search box and you will get all the information you require. You can Also Kill the WIN Process or process tree using Process Explorer.

File:Process Explorer Screenshot.png

Wednesday 29 August 2012

How to use Row_number() to insert consecutive numbers (Identity Values) on a Non-Identity column


Scenario:-

We have database on a vendor supported application in which a Table TestIdt has a integer column col1 which is not specified as identity as below,
 
--Create Table
CREATE TABLE [dbo].[TestIdt]
(
[col1] int NOT NULL,
[col2] [varchar](60) NULL,
[col3] [varchar](60) NULL,
[col4] [varchar](50) NULL,
CONSTRAINT [PK_TestIdt] PRIMARY KEY CLUSTERED
(
[col1] ASC
)WITH (PAD_INDEX = OFFSTATISTICS_NORECOMPUTE = OFFIGNORE_DUP_KEY = OFFALLOW_ROW_LOCKS =ONALLOW_PAGE_LOCKS = ONFILLFACTOR = 80) ON [PRIMARY]
ON [PRIMARY]
 
--Insert Values to the Table
INSERT INTO TestIdt VALUES (1,'Britan','London','Football')
INSERT INTO TestIdt VALUES (2,'Wales','Cardiff','Football')
INSERT INTO TestIdt VALUES (3,'Scotland','Edinburgh','Football')
INSERT INTO TestIdt VALUES (4,'France','Paris','Football')
INSERT INTO TestIdt VALUES (5,'Ireland','Dublin','Football')
 
Now we need to insert values of another table(subsetTbl as below) to TestIdt with col1 on TestIdt need to be increaed by 1 with each row in the subset, In short Col1 should be populated like an identity column.
 
CREATE TABLE [dbo].[subsetTbl]
(
[subcol2] [varchar](60) NULL,
[subcol3] [varchar](60) NULL,
[subcol4] [varchar](50) NULL,
ON [PRIMARY]
 
--Insert Values to the Table
INSERT INTO subsetTbl VALUES ('Germany','Berlin','Tennis')
INSERT INTO subsetTbl VALUES ('Swiss','Bern','Tennis')
INSERT INTO subsetTbl VALUES ('Italy','Rome','Tennis')
INSERT INTO subsetTbl VALUES ('Belgium','Brussels','Tennis')
INSERT INTO subsetTbl VALUES ('Spain','Madrid','Tennis')
 
Let us see how we can Accomplish this task, One way of doing it is using SQL cursors which looks fine till we have small tables but with large tables and inserts cursor has its own disadvantages of using more resources and
more locking on the table. With the introduction of OVER clause on SQL 2005 we can accomplish this task pretty easily using rownumber function as below,
 
DECLARE @cnt int
SELECT @cnt = MAX(col1) from TestIdt
    INSERT INTO TestIdt
    select @cnt + (row_number() OVER (ORDER BY subcol2 ASC)) As Col1,subcol2,subcol3,subcol4 from subsetTbl
 
col1col2col3col4
1BritanLondonFootball
2WalesCardiffFootball
3ScotlandEdinburghFootball
4FranceParisFootball
5IrelandDublinFootball
6BelgiumBrusselsTennis
7GermanyBerlinTennis
8ItalyRomeTennis
9SpainMadridTennis
10SwissBernTennis
 
Now that we have inserted subsetTbl values to TestIdt with Col1 values incremented by 1, We can specify the start value as we wish, Let say I want to start with 100 for next insert We can do it as follows,
 
DECLARE @cnt int
SET @cnt = 99
    INSERT INTO TestIdt
    select @cnt + (row_number() OVER (ORDER BY subcol2 ASC)) As Col1,subcol2,subcol3,subcol4 from subsetTbl
 
col1col2col3col4
1BritanLondonFootball
2WalesCardiffFootball
3ScotlandEdinburghFootball
4FranceParisFootball
5IrelandDublinFootball
6BelgiumBrusselsTennis
7GermanyBerlinTennis
8ItalyRomeTennis
9SpainMadridTennis
10SwissBernTennis
100BelgiumBrusselsTennis
101GermanyBerlinTennis
102ItalyRomeTennis
103SpainMadridTennis
104SwissBernTennis
 
We have eventually created a Identity Insert for a Non-Identity Column.

Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.