User Security

Handling user security is an exercise in the practice of obfuscation by design.

schema permissions

select *
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where class_desc = 'schema'

SQL Server Installation information

SELECT SERVERPROPERTY('productversion')
, SERVERPROPERTY ('productlevel')
, SERVERPROPERTY ('edition')
, SERVERPROPERTY ('InstanceName')
, SERVERPROPERTY ('LCID')
, SERVERPROPERTY ('MachineName')
, SERVERPROPERTY ('LicenseType')
, SERVERPROPERTY ('NumLicenses')

Specialized User / Role / Schema setups

Specialized User / Role / Schema setups

GRANT VIEW DEFINITION ON SCHEMA :: Usr TO [ABC\SmithJ]
GRANT CREATE TABLE TO [ABC\SmithJ]

OR ...

UserA + UserB + UserC >> Role1 >> Schema1 (ownership no difference)

for Role1 assign ddl_admin

Remove role rights on other schemas

Can still create objects in other schemas but not modify them

New SQL Mail

DECLARE @body1 VARCHAR (100)
SET @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='foo@foo.org',
@subject = 'My Mail Test msdb.dbo.sp_send_dbmail',
@body = @body1,
@body_format = 'HTML' ;

Leap Year

http://en.wikipedia.org/wiki/Leap_years

if year modulo 400 is 0
then is_leap_year
else if year modulo 100 is 0
then not_leap_year
else if year modulo 4 is 0
then is_leap_year
else
not_leap_year