ch 27 - securing databases.sql

《SQLServer2000宝典》—包括本书中的所有代码

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Wiley Publishing 
-- Paul Nielsen

-- Chapter 27 - Securing Databases

-----------------------------------------------------------
-----------------------------------------------------------

-- Check the authentication mode
EXEC xp_loginconfig 'login mode'

-- Managing Windows Users
EXEC sp_grantlogin 'Noli\Joe'

EXEC sp_revokelogin 'Noli\Lauren'

EXEC sp_denylogin 'Noli\Lauren'

EXEC sp_validatelogins

-- Managing SQL Server users
EXEC sp_addlogin 'Joe', 'myoldpassword', 'OBXKites'

EXEC sp_helplogins

EXEC sp_droplogin 'Joe'

-- Setting the default db
EXEC sp_defaultdb 'Joe', 'OBXKites'

-- Modifying the  password 

EXEC sp_password 'myoldpassword', 'mynewpassword', 'Joe'


-- Granting Access to a database
USE Family

EXEC sp_grantdbaccess 'Noli\Lauren', 'LRN'

EXEC sp_revokedbaccess 'LRN'

-- Adding a login to a Server Fixed Role

EXEC sp_addsrvrolemember  'Noli\Lauren', 'sysadmin'

-- Droppign a user from a server fixed role

EXEC sp_dropsrvrolemember  'Noli\Lauren', 'sysadmin'




-- Adding the guest user

EXEC sp_adduser 'Guest'


-- Testing conflicting database roles 

EXEC sp_addlogin 'Joe'
USE Family
EXEC sp_grantdbaccess 'Joe'




-- Setting Object Permissions
USE Family
GRANT Select ON Person TO Joe

GRANT All ON marriage TO Public

GRANT Select ON Person to Joe WITH GRANT OPTION

GRANT Select, Update ON Person to Guest, LRN

REVOKE All ON Marriage FROM Public

DENY Select ON Person TO Joe, Public

-- Managing Standard Roles

EXEC sp_addrole 'Manager'

EXEC sp_droprole 'Manager'

EXEC sp_addrolemember 'Manager', Joe

EXEC sp_dropRoleMember  'Manager', Joe

-- Ownership

EXEC sp_changeobjectowner Person, Joe