SQL - access rights

Below is an example of how to set access rights in SQL2005 server. A trigger is created in DBase B which will add/update record in a table of DBase A. Account "developer" has full control of DBase B but only allow to insert/update record in that particular table of DBase A.

Account "developer" is used to create a record in DBase B and the trigger will create related record in DBase A

Steps:
1) Create account "developer" and add it to DBase A. (without set role to db_owner, db_denyreader, db_denywriter, etc)

2) Login SSMS as SA, execute commands as below to grant "developer" to access particular table of DBaseA:

use [DBaseA]
GO
GRANT INSERT ON [dbo].[table_XXX] TO [developer]
GRANT UPDATE ON [dbo].[table_XXX] TO [developer]
GRANT SELECT ON [dbo].[table_XXX] TO [developer]
GO

3) Login SSMS as SA, check access rights by command as below:

USE DBaseA;
EXECUTE AS USER = 'developer';
SELECT *
FROM fn_my_permissions('dbo.table_XXX', 'Object')
ORDER BY subentity_name, permission_name ;
REVERT;
GO

4) Login SSMS as "developer", try to create a record in related table of DBaseB, the trigger will be activated to insert a record in "table_XXX" of DBaseA.

Remark:
**Commands in step-2 will be ignored (and become useless) by SQL Server if role of user is set to "db_denywriter" in step 1.

Reference:
http://www.mssqltips.com/tip.asp?tip=1440

SQL - trigger

[Sample of trigger for "inserted"]

Create trigger MMS_insert_dup on t_mms_mo_mms_send_history for insert
as
begin
declare @TxID bigint
declare @MMSTitle nvarchar(50)
select @TxID=TransactionID,
@MMSTitle = MMSTitle
from inserted
insert XXX..XXX(TransactionID, MMSTitle)
values (@TxID, @MMSTitle)
end

Sample of trigger for "updated" (There is no "Updated" table)
Create trigger MMS_update_dup on t_mms_mo_mms_send_history for update
as
begin
declare @TxID bigint
declare @MMSTitle nvarchar(50)
select @TxID=TransactionID,
@MMSTitle = MMSTitle
from inserted
update XXX..XXX
set
MMSTitle = @MMSTitle
where TransactionID = @TxID
end

SQL - Shrink file

Below is an example of reduce size of log file:

USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO

SQL - A simple example of using IN / NOT IN

Below is a simple example of using IN / NOT IN of SQL2005

select TID, MobileNum, create_timestamp, success from XXX..XXX where TID not like 'ZHA%' and mobilenum not in
( select mobile_num as mobilenum from XXX..XXXX
)
order by create_timestamp asc

C# - example of time string formatting

Below is two simple example of how to use C# to display date/time in different format:

[Present Time]
strNow = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

[TimeStamp of File]
FileInfo fi = new FileInfo(strPath);
fi.LastWriteTime.ToString("yyyyMMddHHmmss");

SQL 2005 - Usage of recursive query and Union

Below is an example which I implemented in this morning. Need to use the SQL to perform join among three tables

select c.CID, c.event, c.SID, c.TID, c.create_timestamp,

SUBSTRING(d.userid,1,4)+'XXXX'+SUBSTRING(d.userid,len(d.userid)-3,4) as mobile

from

(select a.CID, b.alias as event, a.SID, a.TID, a.create_timestamp

from XXXX..TxEvent as a join XXXX..campaigns as b

on a.success='0' and a.CID=b.campaignid) c

join XXX..MMS_Usage as d on c.SID=d.session_id and d.inbound='0'


union


select a.campaign_id as CID, b.alias as event, a.session_id as SID, a.TID, a.create_timestamp,

SUBSTRING(a.userid,1,4)+'XXXX'+SUBSTRING(a.userid,len(a.userid)-3,4) as mobile

from XXXX..Master_TxEvent as a join XXXX..campaigns as b

on a.state='0' and a.campaign_id=b.campaignid

SQL - create user account

1. Execute commands as below to create login account
CREATE LOGIN deve1
WITH PASSWORD = 'deve1';
USE dbase_xxx;
CREATE USER deve1 FOR LOGIN deve1;
GO

2. Execute commands as below to assign user account to each database
use dbase_xxyy
exec sp_adduser deve1

3. Execute commands as below change grant / deny access rights to individual account

DENY UPDATE ON dbase_XXX..table_xxx to develop