Monday, March 6, 2017

SQL Rights Setup Read Only, Write, and/or Read/Write

Here is a query I use to lock down users hitting all my databases with Read Only rights:

DROP LOGIN [YOUR_USER]
GO

-- ADD USERS
CREATE LOGIN [YOUR_USER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
-- add the user to each database
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
CREATE USER [YOUR_USER] FOR LOGIN [YOUR_USER]
PRINT ''User Created in '' + DB_NAME()
END
ELSE
PRINT ''User Already Existed in '' + DB_NAME() '
go

-- add the user to each database db_datareader role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
exec sp_addrolemember ''db_datareader'',''YOUR_USER''
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go

-- grant view any definition to user
USE master
go
GRANT VIEW ANY DATABASE TO [YOUR_USER]
PRINT 'Server wide permission granted'

Go


If you want to add writer use this query

-- Add Write ability to user for each database

-- add the user to each database db_datawriter role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
exec sp_addrolemember ''db_datawriter'',''YOUR_USER''
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go


Sometimes users require more access than just read or write. Like to update stored procedures, etc..


-- Add the user to each database as db_owner role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
exec sp_addrolemember ''db_owner'',''YOUR_USER'' 
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go




1 comment:

Priya Kannan said...

This information really worth saying, i think you are master of the content and thank you so much sharing that valuable information and get new skills after refer that post.
SQL Server Training in Chennai