Friday, March 1, 2013
Generate scripts to create DB Users and their membership from existing DB
-- Create script to create the DB Users
SELECT 'CREATE USER [' + name + '] for login [' + name + ']' + char(13) + 'GO'
from sys.database_principals
where Type = 'S' AND default_schema_name='dbo'
-- Create script to create the Users' membership
SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' + char(13) + 'GO'
from sys.database_principals users
inner join sys.database_role_members link
on link.member_principal_id = users.principal_id
inner join sys.database_principals roles
on roles.principal_id = link.role_principal_id
WHERE users.Type = 'S' AND users.default_schema_name='dbo'
Generate scripts to create DB role and its securables from existing DB
-- Create script to create DB Role
select 'CREATE ROLE [' + @RoleName + ']' + char(13) + 'GO'
-- Create script to grant pemission of securable of the DB Role
select 'GRANT ' + prm.permission_name + ' ON [' + OBJECT_NAME(major_id) + '] TO [' + rol.name + ']' + char(13) COLLATE Latin1_General_CI_AS + 'GO'
from sys.database_permissions prm
join sys.database_principals rol on
prm.grantee_principal_id = rol.principal_id
where rol.name = @RoleName
Thursday, June 7, 2012
MS SQL Server : Move TempDB to another location
First use the following codes to check the name of the files.
USE TempDB
GO
EXEC sp_helpfile
GO
By default, the names of the files are : tempdev and templog.
Then, you could run the following codes to move .mdf and .ldf files.
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:tempdb_data.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:tempdb_log.ldf')
GO
Tuesday, May 15, 2012
Grant EXECUTE permission to all stored procedures
FROM INFORMATION_SCHEMA.ROUTINES
Where @TO is the database role or user
Thursday, May 3, 2012
Remove Hypothetical Indexes Generated by SQL DTA
SELECT 'DROP INDEX [' + s.[name] + '] ON [' + object_name(s.[object_id]) +'] '
FROM sys.stats AS s
INNER JOIN sys.indexes AS i
ON s.name=i.name
WHERE i.is_hypothetical=1 and s.[name] LIKE '_dta_ind%'
SELECT 'DROP STATISTICS [' + object_name(s.[object_id]) + '].[' + s.[name] + '] '
FROM sys.stats AS s
WHERE s.name like '_dta_stat%'
Monday, November 21, 2011
IIS 6.0 : Web Farm Share Resources via UNC share
2) Share the folder and set the permission (R and W) to the "WebFarmUser" account.
3) Assign "WebFarmUser" user account as the account running the App Pool in IIS Server.
4) Assign the web application to the App Pool
5) Create a virtual directory to the UNC shared folder.
6) if you still get the access denied issue, explicitly set the "Connect AS" of the virtual directory to use "WebFarmUser" account.
7)
- ASP.NET 2.0 is configured to run with a user account.
- The SerializeAs attribute of the Profile property in ASP.NET 2.0 is set to Xml
error CS2001: Source file 'D:\WINDOWS\TEMP\d0lurtzx.0.cs' could not be found
error CS2008: No inputs specified
To resolve this issue, grant the user account the List Folder Contents and Read permissions on the %windir%\Temp folder.
Thursday, October 6, 2011
IIS 6.0 Compression
2) Create Compression Folder (optional)
The first thing I do is create a folder on the D drive where the static file compression will be cached. You can call it anything you want or leave the default of “%windir%\IIS Temporary Compressed Files” if that works for you. The IUSR_{machinename} will need write permission to the folder. If you use custom anonymous users, make sure to assign the proper user. IIS will still work even if the permissions are wrong but the compression won't work properly. Once running, it's worth double checking Event Viewer to see if any errors are occurring that keep IIS Compression from working.
3) Enable Compression in IIS
- From the IIS snap-in, right-click on the Web Sites node and click on Properties
- Select the Service tab - Enable Compress application files
- Enable Compress static files
- Change Temporary Directory to the folder that you created above, or leave it at it's default
- Set the max size of the temp folder to something that the hard drive can handle. i.e. 1000.
- Save and close the Web Site Properties dialog
Note: The temporary compress directory is only used for static pages. Dynamic pages aren't saved to disk and are recreated every time so there is some CPU overhead used on every page request for dynamic content.
4) Metabase changes
To enable metabase edit-while-running using IIS Manager- In IIS Manager, right-click the local computer, and then click Properties.
- Select the Enable Direct Metabase Edit check box.
- Search for
- First thing to do is add aspx, asmx, php and any other extension that you need to the list extensions in HcScriptFileExtensions.
HcDynamicCompressionLevel has a default value of 0. Basically this means at if you did everything else right, the compression for dynamic contact is at the lowest level. The valid range for this is from 0 to 10.
The compression level -vs- CPU usage which showed that the CPU needed for levels 0 - 9 is fairly low but for level 10 it hits the roof. Yet the compression for level 9 is nearly as good as level 10.
5) Restart IIS
Origianl Post : http://weblogs.asp.net/owscott/archive/2004/01/12/57916.aspx