DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 64--------------------------------------------------
       Security Related Scripts
       By: srinivasma_exceldbp Date: May 15, 2014, 12:35 am
       ---------------------------------------------------------
       --** Security Related Scripts
       --======================================================
       /*
       Listing 1: Create a Login for a Windows group named Developers
       in a domain named excelindia
       */
       CREATE LOGIN [excelindia\Developers]
       FROM WINDOWS
       WITH DEFAULT_DATABASE = [master];
       GO
       --======================================================
       /*Listing 2: T-SQL for creating a SQL Login named QA*/
       USE [master]
       GO
       CREATE LOGIN [QA]
       WITH
       PASSWORD
       DEFAULT_DATABASE
       CHECK_EXPIRATION
       CHECK_POLICY
       --======================================================
       /*
       Listing 3: Granting and denying server-level permissions
       */
       --** SQL Profiler Tracing access to Jr DBAs
       GRANT ALTER TRACE TO [excelindia\JrDBAs];
       --** Denying Database related objects to particular login
       Trainee
       DENY VIEW ANY DATABASE TO [excelindia\Trainee];
       GO
       --======================================================
       /*Listing 4: T-SQL to create User [Trainee]*/
       CREATE USER [Trainee] FOR LOGIN [excelindia\Trainee];
       /*Listing 5: Scripts for determining SIDs*/
       -- logins (execute within context of any database)
       SELECT  name ,
       principal_id ,
       sid ,
       type ,
       type_desc
       FROM    sys.server_principals
       WHERE   name NOT LIKE '##%';
       -- users (execute within database of interest)
       SELECT  name ,
       principal_id ,
       sid ,
       type ,
       type_desc
       FROM    sys.database_principals
       WHERE   name NOT LIKE 'db_%'
       --======================================================
       /*Listing 6: Synchronizing an orphaned user with a Login*/
       ALTER USER SARASUser WITH LOGIN = SARASUser;
       --======================================================
       /*Listing 7: Adding a User to a database role*/
       EXEC sp_addrolemember N'db_datareader', N'SARASUser'; -- through
       SQL 2008 R2
       ALTER ROLE db_datareader ADD MEMBER SARASUser; -- SQL Server
       2012
       --======================================================
       /*
       Listing 8: Granting SELECT on a database, schema, or object
       */
       /*Database scope*/
       GRANT SELECT TO [SARASUser];
       /* Schema Scope */
       --** DENY SELECT to Trainee for Corporate Schema
       DENY SELECT ON SCHEMA::[Corporate] TO [Trainee];
       -- Grant only execute on SPs for existing and New SPs in Schema
       mobile to Trainee
       GRANT EXECUTE ON SCHEMA::[mobile] TO [Trainee];
       /* Object level */
       --** Grant  Select on View in MIS Schema
       GRANT SELECT ON [MIS].[[NoOfParticipants] TO [Trainee];
       --======================================================
       *****************************************************