SQL Server General DBA

How can I drop all the instances attached to one Database?

Question: How can I drop all the instances attached to one Database? Answer: Use KILL statement with the spid (server process IDs) to drop one instance of the database. However, this is not that easy when you have many instances. In addition, finding out spids of a given database is also a difficult process. The […]

In SQL Server 2005 how to determine the role memberships and the permissions associated with login yy?

Solution: Open the Query Editor by Connecting to the appropriate instance of SQL Server 2005. Execute the following statements under the context of ‘master’ database: select suser_name(role_principal_id), suser_name(member_principal_id) from sys.server_role_members where member_principal_id = suser_id(‘xyz’)select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id(‘xyz’) To check the context of login: select suser_name() To check information on […]

Using SQL Server 2005 how to find a user or login that has the db_owner role in any database?

You can replace the ‘db_owner’ with other fixed database roles such as db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter and db_securityadmin using the below instruction: select user_name(member_principal_id) from sys.database_role_members where role_principal_id = user_id(‘db_owner’)

IN SQL Server 2005, how do I backup the sys.asymmetric_keys and sys.symmetric_keys tables?

Question In SQL Server 2005, how do I backup the sys.asymmetric_keys and sys.symmetric_keys tables? Answer There is no way to directly backup these individual tables, and there is no need to do so. This is because SQL Server 2005 automatically backs up these tables when you back up a database. On the other hand, you […]

How to resolve SQL Server Management Studio error: ‘Could not load file or assembly ‘SqlManagerUI …’?

Problem: Sometimes when a user tries to restore a database in SQL Server Management Studio on a 2005 database engine, they might get a error message:Cannot show requested dialog. ——————————ADDITIONAL INFORMATION: Could not load file or assembly ‘SqlManagerUI, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. Strong name validation failed. (Exception from HRESULT: 0x8013141A) (mscorlib) —————————— […]

For optimum security, is it recommended to use a single domain account the SQL Server instances in a network?

Question Our team supports 150+ SQL Server 2000/2005 instances company-wide. In order to make it easier to manage the services accounts, we use the same domain account for all the SQL Services. This domain account has been granted both local machine and SA privileges.Recently, somebody raised a question about the wisdom of using the same domain account […]

What is the best way to test the upgrade process from SQL Server 7.0 or 2000 to SQL Server 2005, before I perform the actual upgrade?

As you indicate in your question, it is very important that you test the upgrade process before you upgrade your production server. Here’s what I recommend. Restore a backup of each of your production databases onto another (test) server, including the system databases running the same version of SQL Server as your production box. In […]

In SQL Server 2005, when I’m trying to open the Database Maintenance Wizard, I get an error message telling me that “Agent XPs” are not running. How can I resolve this problem, and what are “Agent XPs?”

If the SQL Server 2005 Surface Area Configuration tool is used to start the SQL Server Agent service, it also turns on the “Agent XPs” advanced option. This allows the Database Maintenance Wizard to run with no issues. This is a new SQL Server option found in SQL Server 2005. But if you don’t start the […]

What is the purpose of the Surface Area Configuration Tool in SQL Server 2005?

In recent times, Microsoft’s Trustworthy Computing initiative has strongly influenced how security is implemented out of the box for new products, such as SQL Server 2005. One of the main things Microsoft has done is to turn off, by default, potential areas of their applications that could be susceptible to security risks. In SQL Server […]

How can I enable xp-cmdshell on a SQL Server 2005 instance?

(Note: Please replace the – [hyphen] with an _ [underscore] wherever xp-cmdshell is mentioned in this FAQ.) As part of Microsoft’s desire to increase the out-of-the-box security of SQL Server 2005, it has turned off the xp-cmdshell extended stored procedure by default. If you try to use xp-cmdshell without manually enabling it, you will get an error message […]