(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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]; --====================================================== *****************************************************