Geeks With Blogs
Geekette Mai Blog

 

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

-- *** Windows Logins ***

-- **********************

-- This code assumes that JoeAppdev and JaneAppdev already exist as a Windows login

-- Add a Windows login to SQL Server

-- Note that the brackets are required for a Windows login

CREATE

LOGIN [APPDEV\JaneAppDev] FROM WINDOWS;

GO

USE

GO

CREATE

WITH DEFAULT_SCHEMA = Production

GO

-- Or, rename the user in the database

DROP

GO

CREATE

GO

-- Alter the login to add a default schema

ALTER

WITH DEFAULT_SCHEMA = Production

GO

-- Add one of the built-in Windows groups

CREATE

LOGIN [BUILTIN\Users] FROM WINDOWS;

GO

-- *** SQL Server Logins ***

-- *************************

CREATE

LOGIN Topaz WITH PASSWORD = 'Bo3EXNWJ#N6ndg5';

GO

USE

GO

CREATE

WITH DEFAULT_SCHEMA = HumanResources

GO

-- Or, rename the user in the database

DROP

GO

CREATE

WITH DEFAULT_SCHEMA = HumanResources

GO

-- *** Password Policies ***

-- *************************

-- Requires that SQL Server be running on Windows Server 2003 or later

-- and that password policies be enabled in Local Security Settings.

USE

GO

CREATE

LOGIN SIMPLEPWD WITH PASSWORD = 'SIMPLEPWD'

GO

ALTER

LOGIN Topaz WITH PASSWORD = 'Bo3EXNWJ#N6ndg5',

CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF

ALTER

-- *** Authorization ***

==

*********************

-- *** Server Roles

-- Add login Topaz to the sysadmin role

sp_addsrvrolemember

'Topaz', 'sysadmin'

-- Get a list of all server roles

EXEC

sp_helpsrvrole

-- Get the description of a single server role

EXEC

-- Get list of members of the diskadmin role

EXEC

-- *** Database roles

 

 

 

 

 

/*** Execution Context

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

-- Execution Context Sample

-- *** Create the database for this demo ***

USE

GO

CREATE

LOGIN UserProc WITH password = 'Y&2!@37z#F!l1zB'

CREATE

LOGIN UserTable WITH password = 'Y&2!@37z#F!l1zB'

CREATE

LOGIN RealUser WITH password = 'Y&2!@37z#F!l1zB'

GO

CREATE

GO

USE

GO

-- Create the users

CREATE

CREATE

CREATE

GO

-- Create the schemas

CREATE

GO

CREATE

GO

-- Create a table and a proc in different schemas to ensure that

-- there is no ownerhship chaining.

CREATE

TABLE SchemaUserTable.Vendor (ID int, name varchar(50), state char(2), phno char(12))

GO

INSERT

INTO SchemaUserTable.Vendor VALUES (1,'Vendor1','AK','123-345-1232')

INSERT

INTO SchemaUserTable.Vendor VALUES (2,'Vendor2','WA','454-765-3233')

INSERT

INTO SchemaUserTable.Vendor VALUES (3,'Vendor3','OR','345-776-3433')

INSERT

INTO SchemaUserTable.Vendor VALUES (4,'Vendor4','AK','232-454-5654')

INSERT

INTO SchemaUserTable.Vendor VALUES (5,'Vendor5','OR','454-545-5654')

INSERT

INTO SchemaUserTable.Vendor VALUES (6,'Vendor6','HI','232-655-1232')

INSERT

INTO SchemaUserTable.Vendor VALUES (7,'Vendor7','HI','453-454-1232')

INSERT

INTO SchemaUserTable.Vendor VALUES (8,'Vendor8','WA','555-654-1232')

INSERT

INTO SchemaUserTable.Vendor VALUES (9,'Vendor9','AK','555-345-1232')

GO

-- Create the stored procedure in SchemaUserProc

CREATE

PROC SchemaUserProc.VendorAccessProc @state char(2)

AS

SELECT * FROM SchemaUserTable.Vendor WHERE state = @state

GO

-- Grant permissions on the stored procedure

GRANT

GO

-- *** Ready to test execution context ***

-- Now try and access the proc as RealUser

EXECUTE

AS user = 'RealUser'

EXEC

SchemaUserProc.VendorAccessProc 'AK'

-- The permission is denied on the underlying table.

-- But do not want to have to grant permissions to all callers on the underlying table.

-- Instead have the proc run as UserTable, which has SELECT permissions on the table,

-- since that user owns the schema it is in.

REVERT

-- Alter the procedure with UserTable as the execution context

-- ALTER preserves the permissions on the object

ALTER

PROC SchemaUserProc.VendorAccessProc @state char(2)

WITH

EXECUTE AS 'UserTable'

AS

SELECT * FROM SchemaUserTable.Vendor WHERE state = @state

GO

-- Now try and execute the proc as RealUser

EXECUTE

AS user = 'RealUser'

EXEC

SchemaUserProc.VendorAccessProc 'AK'

REVERT

-- Clean up

USE

GO

DROP

DROP

DROP

DROP

 

 

 

/*** Metadata Security

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

USE

CREATE

LOGIN User1 WITH password = 'mC%929N*!LkDvuJ'

-- Use the database of your choice

USE

CREATE

EXECUTE

AS LOGIN = 'User1'

SELECT

-- Because the user has no permissions in the database, no rows are returned

* FROM sys.objects

REVERT

-- Give User1 rights to a table and stored procedure

GRANT

GRANT

-- Execute again as User1

EXECUTE

AS LOGIN = 'User1'

SELECT

 -- Attempt to drop a table in the database

-- Throws an error that doesn't confirm the existence of the table

* FROM sys.objects

DROP

REVERT

-- Clean up

DROP

GO

USE

DROP

LOGIN User1
master
USER User1
TABLE Production.Product
EXECUTE ON dbo.uspGetBillOfMaterials TO User1
SELECT ON Person.Contact TO User1
USER User1
AdventureWorks
master
DATABASE ExecuteContextDB
LOGIN RealUser
LOGIN UserTable
LOGIN UserProc
master
EXECUTE ON SchemaUserProc.VendorAccessProc TO RealUser
SCHEMA SchemaUserTable AUTHORIZATION UserTable
SCHEMA SchemaUserProc AUTHORIZATION UserProc
USER RealUser
USER UserTable
USER UserProc
ExecuteContextDB
DATABASE ExecuteContextDB
master
sp_helpsrvrolemember securityadmin
sp_helpsrvrole securityadmin
LOGIN Topaz WITH PASSWORD = 'Bo3EXNWJ#N6ndg5' UNLOCK

 

-- Unlock after too many unsuccessful login attempts

master
USER TopazD FOR LOGIN Topaz
USER Topaz
USER Topaz FOR LOGIN Topaz
AdventureWorks
USER Jane
USER Jane FOR LOGIN [Home\Mhn002AppDev]
USER [Home\Mhn002AppDev]
USER [Home\Mhn002AppDev] FOR LOGIN [Home\Mhn002AppDev]
AdventureWorks Posted on Tuesday, June 27, 2006 4:54 PM | Back to top


Comments on this post: Security.sql in SQL Server 2005- All others SQL Server 2005 Script have moved to Article under SQL Server

# re: Security.sql in SQL Server 2005
Requesting Gravatar...
Security are our future computing...thanks!!
Left by Todd Dawnson on Jun 29, 2006 6:56 PM

Your comment:
 (will show your gravatar)


Copyright © Mai Nguyen | Powered by: GeeksWithBlogs.net