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