SQL Server General DBA

How to Configure Multiple SQL Server Reporting Service Instances on the Same Computer

When a second instance of SQL Server Reporting Services is installed the second instance will connect to the default instance.  This is because the default behavior of a Reporting Services instance is to redirect its requests to the localhost. To reconfigure this behaviour you need to change the settings in the RSReportServer.config, RSWebApplication.config config files […]

Error When Configuring SSRS Database

When trying to configure the SQL Server Reporting Services (SSRS) Database Setup from the Reporting Service Configuration Manager you may receive the following error: The database version (C.0.8.40) does not match your reporting services installation. You must upgrade your reporting services database. Couldn’t generate the upgrade script. There is no upgrade script available for this […]

How to Access a Map Drive from SSIS ?

I have an SSIS package which accesses a map drive. It works fine when I run SSIS from Visual Studio. However, when I run this from a SQL Server job, it gives an error. What is the reason for this? You should assign the necessary permission to the map drive for the user account which […]

How can I force SQL Server 2005 logins to change their passwords periodically?

As you might be aware, SQL Server 2005 includes a new way to enforce password policy & expirations for SQL Server login IDs. This was not the case in previous SQL Server versions. In the past, the only option you had was to use Windows Authentication if you wanted to enforce a password policy, but […]

What permission does the user need to run PROFILER in SQL Server 2005?

Question: I have a team that would like to use the PROFILER to monitor the underlying TSQL statements to fine tune the database. How to enable this for the users without granting any Administrative privileges such as DBOon that database. Solution: Running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures […]

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’)

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) —————————— […]

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 […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |