How To Fix Orphaned SQL Server User

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

SQL: String Function - Reverse

To get the filename of a fully-qualified path

declare @filename varchar(255) = '\\data\share\IT\yes\Myfile.csv'

select
substring(reverse(@filename),1,charindex('\',reverse(@filename)) ) 'Reverse',
reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) ) ) 'Filename with slash',
reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) -1 ) ) 'Filename',
substring(@filename,1,charindex(reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) ) ),@filename)) 'Path',
SUBSTRING(@filename,1,LEN(@filename)-(CHARINDEX('\',REVERSE(@filename))-1)) AS SamePath

SQL: Query to get Database Role Members

Script used to get the roles in the database and their members.

create table ##RolesMembers
(
    [Database] sysname,
    RoleName sysname,
    MemberName sysname
)

exec dbo.sp_MSforeachdb 'insert into ##RolesMembers select ''?'', '''' + r.name + '''', '''' + m.name + ''''
from [?].sys.database_role_members rm
inner join [?].sys.database_principals r on rm.role_principal_id = r.principal_id
inner join [?].sys.database_principals m on rm.member_principal_id = m.principal_id
-- where r.name = ''db_owner'' and m.name != ''dbo'' -- you may want to uncomment this line';

select distinct * from ##RolesMembers
order by [Database], [RoleName]


----------------------

select
[Login Type]=
case sp.type
when 'u' then 'WIN'
when 's' then 'SQL'
when 'g' then 'GRP'
end,
convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from
sys.server_principals as sp join
sys.database_principals as dbp on sp.sid=dbp.sid join
sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join
sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id

SSRS: Refreshing fields not taking effect after changing the stored procedure on the DataSet

I am modifying one RDL with a dataset calling a stored procedure. I changed the stored procedure for the dataset. Refreshing fields does not take any effect.

Solution:

a.
BIDS uses a data cache when designing reports to speed up the development and improve design-preview iterations.

To turn off the feature, modify the config file that controls the Report Designer in BIDS.

Location: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSReportDesigner.config

Change CacheDataForPreview to “false” , if you want to turn it on, just change CacheDataForPreview to “True”


b. Change the Query Type to Text and provide the parameter values on the EXEC call to the stored procedure.

Web: How to get the exact error on an IIS site

You will get the above error on windows 2008 even if you have set CustomErrors=Off in the web.config file .
To get the actual error on the remote machine or remote browsers
Double click on the website in IIS 7.x.
Double click asp.
Double click debugging properties option and set send Errors to browser equal to True and click apply.
Double click on the website.
Double click error pages.
Click Edit feature settings.
Check the radio button “Detailed Errors” and click ok.

SQL: How to recreate a SQL login without you knowing the password

Issue:

No one in the office remembers the password of a SQL login. I needed to create the SQL login for an application that uses it. The password is not stored on any of the web config files.


Resolution:

On the old DB server, select the "password hash" of the login.

select LOGINPROPERTY('sqluser', 'PasswordHash' )
Take the PasswordHash value.
ex. 0x0100A6A58029BE36C0C0F9AFC6EDE0BC420B609143B01CCF8DFC

On the new DB server, create the login:
CREATE LOGIN [sqluser] WITH PASSWORD=0x0100A6A58029BE36C0C0F9AFC6EDE0BC420B609143B01CCF8DFC Hashed;

SQL: DMVs

 Got this from the web:

To identify the most costly queries.


SELECT TOP 20  qs.sql_handle,
 qs.execution_count,
 qs.total_worker_time AS Total_CPU,
 total_CPU_inSeconds = --Converted from microseconds
 qs.total_worker_time/1000000,
 average_CPU_inSeconds = --Converted from microseconds
 (qs.total_worker_time/1000000) / qs.execution_count,
 qs.total_elapsed_time,
 total_elapsed_time_inSeconds = --Converted from microseconds
 qs.total_elapsed_time/1000000,
 st.text,
 qp.query_plan
from
 sys.dm_exec_query_stats as qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
 cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
ORDER BY qs.total_worker_time desc



http://forums.iis.net/t/1146821.aspx?w3wp+exe+high+cpu+usage