Tag: SQLDBPool

Using PowerShell to Register All SQL Instances Into Central Management Server

Problem

Managing multiple SQL Servers has its challenges. One of the big challenges is having to collect data from each of the servers you manage and figure out which servers need attention. SQL Server has introduced a new feature called Central Management repository which can be used to manage multiple instances. One of the challenges to using CMS is that you have to register all the SQL Servers manually into CMS. The below article will guide you on how to register hundreds of servers in a second for SQL Server CMS.

Solution

http://www.mssqltips.com/sqlservertip/2658/using-powershell-to-register-all-sql-instances-into-central-management-server/

March 29, 2012
How to connect the SQL Server running on the different TCP/IP port?

In case you have configured SQL Instance to use the static TCP/IP port number. You can connect SQL Server as below using SSMS.

March 14, 2012
How to check waits in SQL Server 2000?

Today I got a comment, how to check the wait statistics in SQL Server 2000. You can query sysprocesses table and use the DBCC SQLPERF to get the wait statistics in SQL Server 2000.

select top 5* from sysprocesses
dbcc sqlperf(‘waitstats’)

March 10, 2012
Script to drop all SQL Logins
Recently I got a requirement to drop all SQL Logins from the SQL Server. I have created below script to drop all SQL Login.

Before getting into detail, please take a note that “A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped”

SA login account and the few policy certificates also come in SQL Login type, I have excluded them.
```
select *
from sys.server_principals
where name = 'sa' or substring(name, 1, 1) = '#' 
```
```
select 'DROP LOGIN ' + [name]
from sys.server_principals
where type_desc = 'SQL_LOGIN'
and sid <> 0x01 and substring(name, 1, 1) <> '#'
```
Next step is to execute the output of the above query.
March 2, 2012
Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

Problem
Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to drop the user and it failed with the below error messages.

Msg 15421, Level 16, State 1, Line 1 The database principal owns a database role and cannot be dropped. Msg 15138, Level 16, State 1, Line 1 The database principal owns a schema in the database, and cannot be dropped.</code
Solution http://www.mssqltips.com/sqlservertip/2620/steps-to-drop-an-orphan-sql-server-user-when-it-owns-a-schema-or-role/

March 2, 2012