Sunday, July 26, 2009

List User permissions

Another common question I found in several user forums

WITH cte
             USER_NAME ( p.grantee_principal_id ) AS principal_name ,
             dp.principal_id ,
             AS principal_type_desc ,
             p.class_desc ,
             OBJECT_NAME ( p.major_id )
             AS object_name ,
             p.permission_name ,
             AS permission_state_desc
         FROM sys.database_permissions p
         INNER JOIN sys.database_principals dp
         ON p.grantee_principal_id = dp.principal_id )
        p.principal_name ,
        p.principal_type_desc ,
        p.class_desc ,
        p.[object_name] ,
        p.permission_name ,
        p.permission_state_desc ,
        CAST ( NULL AS sysname ) AS role_name
    FROM cte p
    WHERE principal_type_desc <> 'DATABASE_ROLE'
    UNION --role members
        rm.member_principal_name ,
        rm.principal_type_desc ,
        p.class_desc ,
        p.object_name ,
        p.permission_name ,
        p.permission_state_desc ,
    FROM cte p
                           role_principal_id ,
                           dp.type_desc AS principal_type_desc ,
                           member_principal_id ,
                           user_name ( member_principal_id ) AS member_principal_name ,
                           user_name ( role_principal_id ) AS role_name
                       sys.database_role_members rm
                       INNER JOIN sys.database_principals dp
                       ON rm.member_principal_id = dp.principal_id ) rm
    ON rm.role_principal_id = p.principal_id
    ORDER BY principal_name

Saturday, July 25, 2009

Step By Step Guide to Database Mirroring

Principal Server: SQL8

Mirrored Server: SQL 4


- Ensure that SQL Service /SQL Agent Services on all the machines start with the same domain account, otherwise mirroring wont work.

Step 1 : Install database on Mirrored Server

In order to get the database onto the mirrored server, we do a full backup of the ‘YourDatabase’ database on the Principal server, followed by a backup of the Transaction Log.

  • Perform a full backup of the database on the Principal server.
BACKUP DATABASE YourDatabase TO DISK = 'C:\YourDatabase_Full.Bak'
  • Perform a Transaction Log backup on the Principal server.

    BACKUP DATABASE YourDatabase TO DISK = 'C:\YourDatabase_Full.Bak'

  • Copy the backup files to the Mirror.

  • Do a restore of the full backup With NORECOVERY (if you use the visual tool, go to Options, then ensure you check the No Recovery option! This is vital! )

FROM DISK = 'C:\YourDatabase_Full.Bak' WITH NORECOVERY,
MOVE 'YourDatabase_Data' TO 'D:\Data\YourDatabase.mdf',
MOVE 'YourDatabase_Log' TO 'C:\Data\YourDatabase.ldf'
  • Perform another restore of the Transaction Log, also with the NORECOVERY option. (This is important; otherwise you'll get an error when starting the mirror).

    RESTORE LOG YourDatabase FROM DISK ='C:\YourDatabase_Full.Bak' WITH NORECOVERY

You'll notice that the database on the Mirror server now is marked as "Restoring..." and can't be accessed. This is normal and expected! The Mirror is always in a permanent Restoring state to prevent users accessing the database, but will be receiving synchronization data. If the database fails over to the Mirror, then it will become an active database and the old Principal will go into the Recovering state.


Step 2: Setting up SQL Server Service impersonation

Make sure that the sql/ sql Agent services on both the principal and the mirrored servers are running under the same local user account and not Local system account.

  • Create a local user on both the Principal and the Mirror server with the same username and password. For example, "sqluser".
  • Edit the SQL Server Service and change the Logon to this user.
  • Do the same for the SQL Server Agent service.
  • Change the SQL Server Agent service to be Automatic.
  • Re-start the SQL Server Service
  • Do this on both the Principal and the Mirror

***Create a SQL Login on both SQL Servers for this user you created.

Step 3: Setting up the Mirror

Now it is time to setup Mirroring.

· Right click on YourDatabase database and select properties

· Click "Configure Security"

· Click Next on the wizard

· Choose whether you want a Witness server or not, choose “no” and click Next

· In the Principal Server Instance stage, leave everything as its default (you can't change anything anyway)




In the Mirror Server Instance stage, choose your Mirror server from the dropdown and click Connect to provide the credentials. Click Next.


  • In the next dialog about Service Accounts, leave these blank (you only need to fill them in if the servers are in a domain or in trusted domains)


  • Click Next and Finish
  • Click "Do not start mirroring"


  • Enter in the FQDN of the servers if you want, but this is not necessary (as long as it will resolve)
  • Change the operating mode to “High performance (asynchronous)”, otherwise the principal database will become slower.
  • Click Start Mirroring (if you do not have a FQDN entered, then a warning will appear, but you can ignore it)
  • The mirror should then start, and within moments, the Status should be "synchronized: the databases are fully synchronized"


Doing a forced failover

--Run on mirror if principal isn't available


Within 10 seconds the Mirrored database will come online, if you need to change the default failover time, run this on the principal server

ALTER DATABASE YourDatabase SET PARTNER TIMEOUT <Value in Secs, should be greater than 5>

that’s it, do some testing :)

Thursday, July 23, 2009

How to kill a profiler trace

Let's say the DBA ran a trace on a server and left for a long lunch, and the client server where the profiler is running is suffering badly with poor performance on the applications running on it and someone in the admin team want to kill that trace even in the absence of the DBA.


Running the following will give you the status of running traces. You may see one running, usually traceid of 1, this is a system trace which needs to stay running:


SELECT * FROM ::fn_trace_getinfo(0) ;



Once you've found your trace, you need to stop it: 20 being the TraceID and 0 being the command to stop it.


EXEC sp_trace_setstatus 20, 0

Wednesday, July 8, 2009

Forgot/Lost ‘sa’ password

You may have faced the issue of losing the SQL Server SA password. Perhaps you followed the security best-practice of removing the  builtin\Administrators from the sysadmin server role,  and no one  you can find is in the sysadmin role.   At this point you may think that your only options are to reinstall SQL Server and attach the databases, or to  directly access the master database files, which may potentially damage the data.


SQL Server 2005 provides a better disaster recovery option for this scenario that is non-intrusive for master DB and that will help you preserve any objects and data stored in master DB (such as logins, certificates, Service Master Key, etc.) intact. Members of the Windows Administrators group now have access to SQL Server when SQL Server is in started in single-user mode, also known as "maintenance mode ".


Using the single-user mode, SQL Server 2005 prevents a  Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches.


In order to start SQL Server in single-user mode, you can add the parameter "-m" at the command line. You can also use the SQL Server Configuration Manager tool, which provides proper controls for the file access and other privileges. To use the Configuration Manager tool to recover your system, use the following steps:


1. Open the Configuration Manager tool from the "SQL Server 2005| Configuration" menu

2. Stop the SQL Server Instance you need to recover

3. Navigate to the "Advanced" tab, and in the Properties text box add ";–m" to the end of the list in the "Startup parameters" option

4. Click the "OK" button and restart the SQL Server Instance


NOTE: make sure there is no space between ";" and "-m", the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says "SQL Server started in single-user mode."


5. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login (or a newly created one) to the sysadmin server role.

The following example adds the account "Buck" in the "DOMAIN" domain to the SQL Server "sysadmin" role:


EXEC sp_addsrvrolemember 'DOMAIN\Aneesh', 'sysadmin';



6. Once the sysadmin access has been recovered, remove the ";-m" from the startup parameters using the Configuration Manager and restart the SQL Server Instance



Important Security Notes:

This process should only be used for disaster recovery when no other method to access the system with a privileged (i.e. sysadmin or equivalent) is available.


This process allows a Windows Administrator account to override their privileges within SQL Server. It requires explicit and intrusive actions that can be monitored and detected, including:

         Stop SQL Server and restart it in single use mode

         Connecting to SQL Server using Windows credentials

Friday, July 3, 2009

Rename Schema / Transfer schema

   1: CREATE SCHEMA new_schema 
   2: GO 
   3: -- Now run these queries one by one, copy and paste the result of this in a new window and run it 
   4: SELECT 'ALTER SCHEMA new_schema TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name 
   5: FROM sys.tables WHERE schema_id = SCHEMA_ID('old_schema'); 
   7: SELECT 'ALTER SCHEMA new_schema TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name 
   8: FROM sys.views 
   9: WHERE schema_id = SCHEMA_ID('old_schema'); 
  11: SELECT 'ALTER SCHEMA new_schema TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name 
  12: FROM sys.procedures 
  13: WHERE schema_id = SCHEMA_ID('old_schema');

Thursday, July 2, 2009

Maintenance Plans in SQL 2005 Express

I found lot of questions on the missing in ability of SQL Server Express to automatically perform database backups/integrity checks/reindexing regularly with a Maintenance Plan. Since SQL Express doesn't have Maintenance Plans, it takes a bit of extra effort to set that up. Here's how I do it:

A Windows Scheduled Task runs daily, kicking off sqlexpressmaintplan.cmd:

sqlcmd -S SQLSERVER\INSTANCE -i d:\scripts\sqlexpressmaintplan.sql
gzip -fNr e:\SQLBackups

(The second line simply compresses (using gzip) the .BAK files to conserve space.)

This executes sqlexpressmaintplan.sql, which has the configuration for my "maintenance
plan". At a glance, it looks like a lot, but I've got it broken down into separate sections/tasks, so it's easy to comprehend and maintain.

-- integrity check on system databases; save reports for 7 days

exec expressmaint
@database = 'ALL_SYSTEM',
@optype = 'CHECKDB',
@reportfldr = 'e:\SQLReports',
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- integrity check on user databases; save reports for 7 days

exec expressmaint
@database = 'ALL_USER',
@optype = 'CHECKDB',
@reportfldr = 'e:\SQLReports',
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- reindex my database; save reports for 7 days

exec expressmaint
@database = 'MyDatabase',
@optype = 'REINDEX',
@reportfldr = 'e:\SQLReports',
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- backup system databases; verify; save backups for 2 weeks; save reports for 7 days

exec expressmaint
@database = 'ALL_SYSTEM',
@optype = 'DB',
@backupfldr = 'e:\SQLBackups',
@reportfldr = 'e:\SQLReports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 2,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- backup user databases; verify; save backups for 4 weeks; save reports for 7 days

exec expressmaint
@database = 'ALL_USER',
@optype = 'DB',
@backupfldr = 'e:\SQLBackups',
@reportfldr = 'e:\SQLReports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 4,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

The real magic happens in the expressmaint stored procedure, created by SQL Server MVP Jasper Smith. (Also available as a standalone executable.) You can read more about this great tool on in two articles covering the stored procedure and the standalone app.

It's been a really long time since I originally set this up, and I'm having to revisit it now (and write about it) because I just rebuilt my primary workstation and server at home. Now that I'm reading back over this article, I can't remember why I chose to use the stored procedure instead of the executable version -- the exe would be a bit easier to drop into place, along with the batch file (the SQL script would no longer be necessary), next time I have to set this up on a fresh SQL
Express install. I'm going to try out the executable version this time around to see if it's any easier to set up and maintain (it looks like it will be).