/************************************
Chapter 9  Scripts
For MCITP SQL Server 2005 Database Administration All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.

*************************************/

CREATE LOGIN loginName WITH PASSWORD = 'password'

/************************************/

CREATE LOGIN Sally WITH PASSWORD = 'password', 
CHECK_POLICY = OFF

/************************************/

CREATE LOGIN [yourComputerName\Maria] FROM WINDOWS

/************************************/

CREATE LOGIN TSQL_LoginWeakPW
WITH PASSWORD = 'password', CHECK_POLICY = OFF

/************************************/

CREATE LOGIN TSQL_LoginStrongPW2
WITH PASSWORD = 'P@ssw0rd'

/************************************/

SELECT * FROM sys.server_role_members

/************************************/

SELECT * FROM sys.server_role_members
SELECT * FROM sys.server_principals

/************************************/

SELECT p.name, m.role_principal_id, m.member_principal_id,  
p.principal_id, p.type_desc
FROM sys.server_principals p
Full Join sys.server_role_members m
on p.principal_id = m.member_principal_id
Order By m.role_principal_id

/************************************/

SELECT p.name, m.role_principal_id, m.member_principal_id, 
p.principal_id, p.type_desc
FROM sys.server_principals p
Full Join sys.server_role_members m
on p.principal_id = m.member_principal_id
WHERE m.role_principal_id = 3

/************************************/

SELECT has_perms_by_name(db_name(), 'DATABASE', 'ANY')

/************************************/

SELECT has_perms_by_name(name, 'OBJECT', 'SELECT') AS have_select, 
    * FROM sys.tables;

/************************************/

IF IS_SRVROLEMEMBER ('sysadmin') = 1
Print 'You are in the sysadmin role'
ELSE
IF IS_SRVROLEMEMBER ('sysadmin') = 0
Print 'You are NOT in the sysadmin role'

/************************************/

IF IS_SRVROLEMEMBER ('sysadmin') = 1
Print 'You are in the sysadmin role'
ELSE
IF IS_SRVROLEMEMBER ('sysadmin') = 0
Print 'You are NOT in the sysadmin role'

/************************************/

IF IS_SRVROLEMEMBER ('serveradmin') = 1
Print 'You are in the serveradmin role'
ELSE
IF IS_SRVROLEMEMBER ('serveradmin') = 0
Print 'You are NOT in the serveradmin role'

/************************************/

USE Master;
GO
SELECT has_perms_by_name(name, 'OBJECT', 'DELETE') AS have_select, 
    * FROM sys.tables;

/************************************/

USE Master;
GO
EXECUTE AS user = 'yourComputerName\maria'
GO
SELECT has_perms_by_name(name, 'OBJECT', 'DELETE') AS have_select, 
    * FROM sys.tables;
REVERT

/************************************/

SELECT SUSER_SNAME();

/************************************/

EXECUTE AS LOGIN = 'yourComputerName\Nimfa';
SELECT SUSER_SNAME();
IF IS_SRVROLEMEMBER ('sysadmin') = 1
Print 'You are in the sysadmin role'
REVERT

/************************************/

IF IS_SRVROLEMEMBER ('sysadmin') = 1
Print 'You are in the sysadmin role'

/************************************/

rem This can be saved as a batch file
Secedit /configure /db Secure.sdb

/************************************/

rem This can be saved as a batch file
Secedit /configure /db SecureSQL.sdb