> Tech > Listing 1 : Code qui montre l’importance des SID

Listing 1 : Code qui montre l’importance des SID

Tech - Par iTPro - Publié le 24 juin 2010
email

--Add logins for database ownership.
USE master
GO
EXEC sp_addlogin 'DatabaseOwner1'
EXEC sp_addlogin 'DatabaseOwner2'
GO

--Initialize Database1.
CREATE DATABASE Database1
GO
USE Database1
EXEC sp_changedbowner 'DatabaseOwner1'
EXEC sp_grantdbaccess guest
GO
CREATE TABLE [dbo].[MyTable] ([Column1] [int])
GO

--Initialize Database2.
CREATE DATABASE Database2
GO
USE Database2
EXEC sp_changedbowner 'DatabaseOwner2'
EXEC

sp_grantdbaccess guest
GO
CREATE VIEW [dbo].[MyView] AS
SELECT [Column1] FROM [Database1].[dbo].[MyTable]
GO
GRANT SELECT ON [Database2].[dbo].[MyView] TO PUBLIC
GO

–Illustrate behavior with different database owners.
PRINT ‘** Note different logins/SIDs for dbo users so the select
fails **’
EXEC Database1..sp_helpuser ‘dbo’
EXEC Database2..sp_helpuser ‘dbo’
SETUSER ‘guest’

–Select permission denied because of broken ownership chain.
SELECT * FROM [Database2].[dbo].[MyView]
SETUSER
GO

–Change Database2 ownership to match Database1.
EXEC sp_changedbowner ‘DatabaseOwner1’
GO

–Illustrate behavior with same database owner.
PRINT ‘** Note same login/SID for dbo users so the select now succeeds
**’
EXEC Database1..sp_helpuser ‘dbo’
EXEC Database2..sp_helpuser ‘dbo’
SETUSER ‘guest’

–Select succeeds because ownership chain is unbroken.
SELECT * FROM [Database2].[dbo].[MyView]
SETUSER
GO

–Clean up.
USE master
GO
DROP DATABASE Database1
DROP DATABASE Database2
EXEC sp_droplogin ‘DatabaseOwner1’
EXEC sp_droplogin ‘DatabaseOwner2’
GO

Téléchargez gratuitement cette ressource

Guide de Services Cloud Managés

Guide de Services Cloud Managés

Accélérer votre transformation digitale, protéger et sécuriser vos environnements Cloud avec les offres de support, d'accompagnement et de services managés. Découvrez le TOP 3 des Services Managés pour accompagner la transformation de vos environnements Cloud, gagner en agilité et en sécurité dans un monde d'incertitudes.

Tech - Par iTPro - Publié le 24 juin 2010