Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

Prevent Db_DataWrier ,Db_DataReader to script Create Table

Hello
I have a developer belong to db_dataReader,Db_Datawriter
I want to Deny him from generate script to create existing Tables
Actually he cant do that from SSMS but if he use a third party like "Navicat data Modeler " he can import all table structures and save it as DDL Script
How can i prevent him to do that?
0
ali_alannah
Asked:
ali_alannah
1 Solution
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
What you need to do is to deny view definition on the database för this user. I attach a script that demos the procedure in full.

Regards Marten

/*
In this exercise, create a database, create a table and a user:
PREREQ  (you can use my code if you wish)

USE MASTER
GO

CREATE DATABASE TESTPermission
GO

USE TESTPermission
GO

CREATE TABLE [tblTraceLogin_2011](
	[LoginSpid] [int] NULL,
	[loginname] [varchar](40) NULL,
	[dbname] [varchar](100) NULL,
	[hostname] [varchar](100) NULL,
	[programname] [varchar](100) NULL,
	[login_time] [datetime] NULL,
	[current_datetime] [datetime] NULL,
	[Total_loginTime] [int] NULL
) 
GO

-- POPULATE A COUPLE OF ROWS
INSERT [dbo].[tblTraceLogin_2011] ([LoginSpid], [loginname], [dbname], [hostname], [programname], [login_time], [current_datetime], [Total_loginTime]) VALUES (51, N'NT AUTHORITY\SYSTEM', N'master', N'N6022                                                                                               ', N'Microsoft® Windows® Operating System                                                                ', CAST(0x00009FAA0099CF0F AS DateTime), CAST(0x00009FAA00D99B9D AS DateTime), 232)
INSERT [dbo].[tblTraceLogin_2011] ([LoginSpid], [loginname], [dbname], [hostname], [programname], [login_time], [current_datetime], [Total_loginTime]) VALUES (51, N'NT AUTHORITY\SYSTEM', N'master', N'N6022                                                                                               ', N'Microsoft® Windows® Operating System                                                                ', CAST(0x00009FAA01195C2C AS DateTime), CAST(0x00009FAA011C46B2 AS DateTime), 11)
INSERT [dbo].[tblTraceLogin_2011] ([LoginSpid], [loginname], [dbname], [hostname], [programname], [login_time], [current_datetime], [Total_loginTime]) VALUES (7, N'sa', N'master', N'                                                                                                    ', N'                                                                                                    ', CAST(0x00009FAA01171E74 AS DateTime), CAST(0x00009FAA01182826 AS DateTime), 4)
INSERT [dbo].[tblTraceLogin_2011] ([LoginSpid], [loginname], [dbname], [hostname], [programname], [login_time], [current_datetime], [Total_loginTime]) VALUES (8, N'sa', N'master', N'                                                                                                    ', N'                                                                                                    ', CAST(0x00009FAA01171E74 AS DateTime), CAST(0x00009FAA01182826 AS DateTime), 4)
INSERT [dbo].[tblTraceLogin_2011] ([LoginSpid], [loginname], [dbname], [hostname], [programname], [login_time], [current_datetime], [Total_loginTime]) VALUES (9, N'sa', N'master', N'                                                                                                    ', N'                                                                                                    ', CAST(0x00009FAA01171E74 AS DateTime), CAST(0x00009FAA01182826 AS DateTime), 4)
INSERT [dbo].[tblTraceLogin_2011] ([LoginSpid], [loginname], [dbname], [hostname], [programname], [login_time], [current_datetime], [Total_loginTime]) VALUES (10, N'sa', N'master', N'                                                                                                    ', N'                                                                                                    ', CAST(0x00009FAA01171E74 AS DateTime), CAST(0x00009FAA01182826 AS DateTime), 4)
 
-- Create the USER so you can execute as this user and see what implications deny view definition has!
USE [master]
GO

CREATE LOGIN [BLOCKDeveloperFromScriptingDBDefinitions] 
WITH PASSWORD=N'SomeStrongPasswordNeverNeededInThisExercise', 
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

USE [TESTPermission]
GO
CREATE USER [BLOCKDeveloperFromScriptingDBDefinitions] FOR LOGIN [BLOCKDeveloperFromScriptingDBDefinitions]
GO
USE [TESTPermission]
GO
EXEC sp_addrolemember N'db_datawriter', N'BLOCKDeveloperFromScriptingDBDefinitions'
GO
USE [TESTPermission]
GO
EXEC sp_addrolemember N'db_datareader', N'BLOCKDeveloperFromScriptingDBDefinitions'
GO
 
USE master
GO
-- Now you have the PREREQ for this exercise!!!
*/

USE master
GO

-- Change context to user BLOCKDeveloperFromScriptingDBDefinitions
execute as login='BLOCKDeveloperFromScriptingDBDefinitions'

-- Login to DB
USE [TESTPermission]
GO

-- Check Select rights (I know * isn't optimal, but in this small testcase Ill let it pass
SELECT * FROM tblTraceLogin_2011

-- Can he see tablemetadata
sp_help tblTraceLogin_2011
-- Yes he can see whats needed to script the tables

-- Go back to normal user (DBO/Sysadmin)
USE master
GO

REVERT
GO

-- Now lets block him/her
USE [TESTPermission]
GO
DENY VIEW DEFINITION TO BLOCKDeveloperFromScriptingDBDefinitions 
GO

-- Change context to user BLOCKDeveloperFromScriptingDBDefinitions
USE master
GO
execute as login='BLOCKDeveloperFromScriptingDBDefinitions'
GO
USE [TESTPermission]
GO

-- Can he see tablemetadata
sp_help tblTraceLogin_2011
-- Now hes blocked but he can still insert/edit/delete data in tables

select * from tblTraceLogin_2011
-- Success, select works as expected

-- This concludes the demo of DENY VIEW DEFINITION

USE master
GO

REVERT
GO

/*
-- CLEANUP CODE
USE [master]
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TESTPermission')
DROP DATABASE [TESTPermission]
GO

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BLOCKDeveloperFromScriptingDBDefinitions')
DROP LOGIN [BLOCKDeveloperFromScriptingDBDefinitions]
GO
*/

Open in new window

0
 
ali_alannahAuthor Commented:
Nice illustration
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now