DAY 5
--to create windows authenticated login
create login [praclab4 est1] from windows
--to check the login with command select * from sys.syslogins where name like '%test1%'  --sid is 128bit unique hexadecimal number
select * from sys.syslogins where name like '%sa'  select * from sys.syslogins
--to change the default db to slktestdb
alter login [praclab4 est1] with default_database = slktestdb
create login [test_sql] with password = N'password1$'
select * from sys.syslogins where name like '%test%'
alter login [test_sq] with default_language = [us_english]
--
alter login [test_sql] with default_database = slktestdb
sp_helplogins test_sql
--to map a db to login
alter login [test_sql] with default_database = master
alter login [test_sql] with default_database = slktestdb

use slktestdb
create user test_sql for login test_sql
sp_helplogins test_sql

--to enforce password policy,(it is only enabled only for windows server 2003 and abv server class machines)
create login [junk_sql] with password = 'pass123' must_change,
default_database = master, check_expiration = on,check_policy = on
--to unlock the login (always use this than GUI)because it resets password which is dangerous
alter login [test_sql] with check_policy = on
alter login [test_sql] with check_policy = off

xp_logininfo 'praclab4slk'
select * from sys.syslogins where name like '%test%'
create login [praclab4sqldba] from windows
select * from sys.sysprocesses

sp_helpsrvrolemember 'sysadmin'
sp_helpsrvrole
sp_addsrvrolemember @loginame = 'test_sql',@rolename = 'sysadmin'
sp_dropsrvrolemember @loginame = 'test_sql',@rolename = 'sysadmin'
--credential stored procedures.....
select * from sys.credentials
select * from sys.sysusers

create user winmap for login [praclab4 est1]
sp_helpuser winmap