Created
November 25, 2019 15:49
-
-
Save mgibson91/be4e9682ac7b3cec0da205ded2da2275 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Configure test database | |
USE master; | |
CREATE DATABASE TestDBR; | |
USE TestDBR; | |
CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT) | |
INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154); | |
-- Grant public master access | |
USE master; | |
CREATE LOGIN reader1 WITH password='0plmNJI9'; | |
CREATE USER reader1 FROM LOGIN reader1; | |
-- Create user in database that should have read only access | |
USE TestDBR; | |
CREATE USER reader1 FROM LOGIN reader1; | |
EXEC sp_addrolemember 'db_datareader', 'reader1'; | |
-- Verification | |
USE TestDBR; | |
-- Should return new role | |
SELECT SUSER_NAME(), user_name(); | |
-- Should fail to insert value | |
INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154); | |
-- Should fail to delete | |
DELETE FROM Inventory WHERE name = 'banana'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment