DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 127--------------------------------------------------
       Security Scripts Demo
       By: srinivasma_exceldbp Date: December 30, 2014, 5:41 am
       ---------------------------------------------------------
       /*Listing 1: Create a Login for a Windows group named SQL Users
       in a domain named TribalSQL*/
       CREATE LOGIN [TribalSQL\SQL Users] FROM WINDOWS
       WITH DEFAULT_DATABASE = [master];
       GO
       /*Listing 2: T-SQL for creating a SQL Login*/
       USE [master]
       GO
       CREATE LOGIN [SQLAuth1]
       WITH
       PASSWORD=N'Pa$$w0rd' MUST_CHANGE,
       DEFAULT_DATABASE=[master],
       CHECK_EXPIRATION=ON,
       CHECK_POLICY=ON;
       /*Listing 3: Granting and denying server-level permissions*/
       GRANT ALTER TRACE TO [SQLServer\SQL Jr Admins];
       DENY VIEW ANY DATABASE TO [SQLServer\SQL Users];
       GO
       /*Listing 4: T-SQL to create User [SQLUsers]*/
       CREATE USER [SQLUsers] FOR LOGIN [SQLServer\SQL Users];
       /*Listing 5: Scripts for determining SIDs*/
       -- logins (execute within any database)
       SELECT  name ,
       principal_id ,
       sid ,
       type ,
       type_desc
       FROM    sys.server_principals
       WHERE   name NOT LIKE '##%';
       -- users (execute within databas 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 Bob WITH LOGIN = Bob;
       /*Listing 7: Adding a User to a database role*/
       EXEC sp_addrolemember N'db_datareader', N'SQLUsers'; -- through
       SQL 2008 R2
       ALTER ROLE db_datareader ADD MEMBER SQLUsers; -- SQL Server 2012
       /*Listing 8: Granting SELECT on a database, schema, or object*/
       /*Database scope*/
       GRANT SELECT TO [SQLUsers];
       /*Schema Scope*/
       DENY SELECT ON SCHEMA::[HumanResources] TO [SQLUsers];
       /*Object level*/
       GRANT SELECT ON [HumanResources].[vEmployee] TO [SQLAuth1];
       *****************************************************