SQL Server | How to create a Read Only User/Role

I have number of SQL server databases and users . I am in a need to create read only access to users who wants to look at data.

• Create read only role within single database

CREATE ROLE [readOnlyAccess] AUTHORIZATION dbo
GO
GRANT  SELECT ON SCHEMA ::dbo TO readOnlyAccess

• Create read only role within All databases in an Instance

CREATE ROLE [readOnlyAccess] AUTHORIZATION dbo
GO
exec sp_MSforeachdb 'USE [?]; drop role readOnlyAccess; CREATE ROLE [readOnlyAccess] AUTHORIZATION dbo; GRANT SELECT ON SCHEMA ::dbo TO readOnlyAccess'

• Create a readonlyuser within single database

USE [master]
GO
CREATE LOGIN [sagarreadonly] WITH PASSWORD=N'sagarreadonly', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DataEncryptDemo]
GO
CREATE USER [sagarreadonly] FOR LOGIN [sagarreadonly]
GO
USE [DataEncryptDemo]
GO
EXEC sp_addrolemember N'readOnlyAccess', N'sagarreadonly'
GO

• Create a readonlyuser within ALL databases

USE [master]
GO
CREATE LOGIN [sagarreadonly] WITH PASSWORD=N'sagarreadonly', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use master
exec sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'' ) begin
USE [?]; CREATE USER [sagarreadonly] FOR LOGIN [sagarreadonly]; end'
go
USE [master]
GO
exec sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'' ) begin
USE [?]; EXEC sp_addrolemember N''readOnlyAccess'', N''sagarreadonly''; end'
GO
USE [master] 
GO
exec sp_MSForEachDb 'USE ? GRANT VIEW DEFINITION TO schemareader'
go

 

 

 

 

Above SQL will pass access to database tables but you won’t see any procedures,triggers,functions or TSQL objects. Use following TSQL procedure to grant access on all objects within schema.

USE DataEncryptDemo
GO 
CREATE PROCEDURE usp_ExecGrantViewDefinition 
(@login VARCHAR(30)) 
AS 
/*
Included Object Types are: 
P - Stored Procedure 
V - View 
FN - SQL scalar-function
TR - Trigger 
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/ 
SET NOCOUNT ON 

CREATE TABLE #runSQL
(runSQL VARCHAR(2000) NOT NULL) 

--Declare @execSQL varchar(2000), @login varchar(30), @space char (1), @TO char (2) 
DECLARE @execSQL VARCHAR(2000), @space CHAR (1), @TO CHAR (2) 

SET @to = 'TO'
SET @execSQL = 'Grant View Definition ON ' 
SET @login = REPLACE(REPLACE (@login, '[', ''), ']', '')
SET @login = '[' + @login + ']'
SET @space = ' '

INSERT INTO #runSQL 
SELECT @execSQL + schema_name(schema_id) + '.' + [name] + @space + @TO + @space + @login 
FROM sys.all_objects s 
WHERE type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U') 
AND is_ms_shipped = 0 
ORDER BY s.type, s.name 

SET @execSQL = '' 

Execute_SQL: 
SET ROWCOUNT 1 
SELECT @execSQL = runSQL FROM #runSQL
PRINT @execSQL --Comment out if you don't want to see the output
EXEC (@execSQL)
DELETE FROM #runSQL WHERE runSQL = @execSQL
IF EXISTS (SELECT * FROM #runSQL) 
  GOTO Execute_SQL 
SET ROWCOUNT 0
DROP TABLE #runSQL 
GO

Grant privs to all databases except master,model,msdb,tempdb

exec sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'' ) begin USE [?]; exec usp_ExecGrantViewDefinition @login=''sagarreadonly''; end'

 

You should now see TSQL procedures and other objects.