Security Related Stored Procedures

1. Sp_addlogin – Create a new login in SQL Server Instance
Syntax : sp_addlogin login_name, password, [def db], [def language]
Ex : sp_addlogin ‘kalyan’, ‘kumar’, master

2. Sp_adduser – Creates a new user and mapped to login in the current database.
Syntax : sp_adduser ‘login_name’,’user_name_in_db’
Ex : use kalyandb;GO; sp_adduser ‘kalyan’,’kalyan’

3. Sp_addrolemember – Adds login to database role in the current database
Syntax : sp_addrolemember ‘role_name’,’login_name’
Ex : sp_addrolemember ‘db_owner’, ‘kalyan’

4. Sp_changedbowner – changes owner of the current database
Syntax : sp_changedbowner ‘login name’
Ex : sp_changedbowner ‘kalyan’

5. Sp_defaultdb – changes the default database for any login
Syntax : sp_defaultdb login_name, database
Ex : sp_defaultdb ‘kalyan’,’master’

6. Sp_droprolemember – Removes server role of the specified login
Syntax sp_droprolemember role_name, login_name
Ex : sp_droprolemember ‘db_owner’, ‘kalyan’

7. Sp_dropuser – Removes user from the current database
Syntax : sp_dropuser ‘user_name
Ex: sp_dropuser ‘kalyan’

8. Sp_droplogin / drop login – Removes login from SQL Server Instance
Syntax : sp_droplogin loginname
Ex: sp_droplogin ‘kalyan’ (or) drop login kalyan

Execute As in TSQL

Execute As

By default if you login into SQL Server a session gets started when login and session ends at logoff. What ever the transactions / operations we performed those are in specific to permissions against the login.

Using this EXECUTE AS option we can change the context of the session switched from current login to different login.

For Example you login into SQL Instance using “sa” login.

EXECUTE AS USER = 'TestUser'
SELECT USER_NAME()

SELECT * FROM category

SELECT * FROM fn_my_permissions (NULL, 'DATABASE'); -- List all the permissions for the TestUser has on the current database.
       
REVERT – Switch back to original login session ie., (“sa”) in this case.

SELECT USER_NAME(), SUSER_SNAME()

Note : Best practice is specify a login / user name which has least privileges.

Grant execute on all stored procedures in SQL Server

Prior SQL 2005 there is no specific methods to grant execute permission on all stored procedures at one step. We need to give permission one by one or need to a script to do it.

SQL 2005 has provision to grant execute permission at database level. That means if we issue the below statement, it will grant execute permission on all existing stored procedures and scalar functions and for newly created ones.

Grant Execute to ‘Domain\User’
Grant Execute to ‘SQL User’
Grant Execute to ‘Windows Group’

This can be done in several ways. We can create a separate role and we can assign that role to SQL Logins / Windows users / Windows Groups

Create Role DB_ProcsExecutor
Grant Execute to DB_ProcsExecutor
Using sp_addrolemember we can assign role to any login as below
sp_addrolemember 'DB_ProcsExecutor', 'TestUser'

Display AD Users information using TSQL

xp_logininfo returns information about windows users and windows groups.



EXEC xp_logininfo 'Group_Name', 'members'

EXEC xp_logininfo 'Login_Name', 'all'


EXEC xp_logininfo 'BUILTIN\Administrators' , 'members'

EXEC xp_logininfo 'Domain\ADGroup', 'members'

EXEC xp_logininfo 'kalyan','all'

Schema Change History Report -- SQL Server Standard Reports

The schema change history report displays the changes made using DDL Commands. It tracks changes using default trace. If default trace is enabled on the server then we can run this report and view what are all the objects altered, deleted, created using this report.

To verify default trace option
sp_configure 'default trace enabled'

If the run value is 1 then it is enabled. To enable this option
sp_configure 'default trace enabled', 1
Reconfigure with override

This report has 6 columns to display history information

Databae Name – Displays name of the database on which changes happened.
Object Name – Displays name of the object on which changes made.
Type – Display type of object (ex : user defined object )
DDL Operation – Displays which action performed(Create / Alter / Drop)
Time – Display when DDL action was performed
Login Name – Displays the account name used to make changes.

Screen Capture functionality in Word 2010

Screen Capture functionality in Word 2010

While preparing any document with screenshots typicall we use printscreen key to capture the active window and then we paste it on win-word and will do some kind of massaging. In Ms-Word 2010 there is a cool feature in Insert Menu – Insert Screenshot

When you select that option it shows all the active windows from where you can choose which one you want to capture into document.

ROBOCOPY - Examples

Robocopy is a command line file copy utility which comes with windows tool kit. It become part of operating system from windows vista. Unlike other commands like copy, Xcopy, Robocopy has various advantages, i) it is designed for reliable copy of entire folders of any size (large size) ii) it copies files over network and gives resume copy feature when there is any disruption in network.

Syntax
robocopy source destination [switches]

Example

C:\kalyan>robocopy "\\Softwares\OperatingSystems\WindowsXP" C:\temp" /E /Z /Purge
/log:C:\robocopy.log

Explanation

\\Softwares\OperatingSystems\WindowsXP -- Source folder
C:\Temp -- Destination Folder
/E  -- Copy folders even it is empty
/Z  -- Copy files in restartable mode
/PURGE -- Removes all folders or files in destination folder if those are not exists in source location
/log:filename -- Writes log in the specified location with complete details like what files copied, what files / folders removed, how much time taken, tranfer rate etc.,