I will need to allow report writers on the Subscription database to be able to view the table, create alter and delete stored procedure and views. Since it is a transactional replication they cannot alter or delete table structure. Is this possible if so how to achieve any ideas ?
Sql-server – Database Reader Role with the ability to Create, View and execute stored procedure’s and view’s in sql server
permissionsroleSecuritysql servert-sql
Related Solutions
This will require really having fun with escaping and re-escaping single quotes, but I think the technique you're after is:
DECLARE
@DB NVARCHAR(255) = QUOTENAME(N'dbname'),
@SQL NVARCHAR(MAX);
SET @SQL = N'EXEC ' + @DB + '.sys.sp_executesql '
+ 'N''CREATE VIEW dbo.vWhatever
AS
SELECT x = 1, y = ''''x'''', z = GETDATE();''';
EXEC sys.sp_executesql @SQL;
So you're kind of nesting the dynamic SQL; the inner one ensures that the SQL is executed at the target database, not locally.
Another way that I've picked up since this original question appeared:
DECLARE
@DB NVARCHAR(255) = QUOTENAME('dbname'),
@SQL NVARCHAR(MAX),
@DBExec NVARCHAR(MAX);
SET @DBExec = @DB + N'.sys.sp_executesql';
SET @SQL = N'CREATE VIEW dbo.whatever
AS
SELECT x = 1, y = ''x'', z = GETDATE();';
EXEC @DBExec @SQL;
This is slightly tidier because you don't have to double-nest single quotes.
And here is a slightly tidier way to do it without cursors (well, without all the scaffolding of setting up a cursor). Note that the inner dynamic SQL will only be executed in databases where (a) a categories table exists (b) this view does not already exist and (c) it is not a system database (well, not one of the primary system databases, anyway).
DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL += NCHAR(13) + NCHAR(10)
+ N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
+ '.sys.views WHERE name = ''vNonhiddenCategories'')
AND EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
+ '.sys.tables WHERE name = ''categories'')
BEGIN
EXEC ' + QUOTENAME(name) + '.sys.sp_executesql N''
CREATE VIEW dbo.vNonhiddenCategories3
AS
SELECT x = 1, y = ''''x'''';''
END'
FROM sys.databases
WHERE database_id BETWEEN 5 AND 32766;
PRINT @sql;
-- EXEC sp_executesql @sql;
Of course "tidier" is in the eye of the beholder.
Instead of creating views, why not make an inline table-valued function - effectively a parameterized view. You basically get all the benefits of views AND parameters.
Another thing you can do in your TVF is something like this:
SELECT yourtable.*
FROM yourtable
INNER JOIN ranges
ON yourtable.date BETWEEN ranges.start AND ranges.end
WHERE ranges.name = @range_name
Now your ranges have names (in a table where they can be easily managed without changing the schema) - this also fairly seamlessly handles multiple ranges with the same name, although if ranges overlap you can have duplicates due to the join matching both.
Also, please consider avoiding BETWEEN:
I strongly suggest that you reconsider your requirements and when asking questions here be sure to include more about your motivations, because when you bring in a question with a preconceived notion of what the solution is, you are likely to get a suboptimal solution. (like the dynamic SQL solutions given aren't going to easily work for your OR case, but that information about the problem space is buried in a comment from you)
From: http://sqlfiddle.com/#!6/a628a/1
CREATE TABLE LogData (
Id INT IDENTITY NOT NULL
,Dt DATETIME NOT NULL
,Data VARCHAR(max) NOT NULL
);
CREATE TABLE Ranges (
Id INT IDENTITY NOT NULL
,Name VARCHAR(50) NOT NULL
,DtStart DATETIME NOT NULL
,DtEnd DATETIME NOT NULL
);
CREATE TABLE Groups (
Id INT IDENTITY NOT NULL
,Name VARCHAR(50) NOT NULL
);
CREATE TABLE RangeGroups (
RangeId INT NOT NULL
,GroupId INT NOT NULL
);
INSERT INTO LogData (Dt, Data)
VALUES ('2011-11-23 11:00', 'before thanksgiving day')
,('2011-11-24 11:00', 'on thanksgiving day')
,('2011-12-24 11:00', 'on christmas eve')
,('2011-12-25 11:00', 'on christmas day')
,('2012-11-21 11:00', 'before thanksgiving day')
,('2012-11-22 11:00', 'on thanksgiving day')
,('2012-12-24 11:00', 'on christmas eve')
,('2012-12-25 11:00', 'on christmas day');
INSERT INTO Ranges (Name, DtStart, DtEnd)
VALUES ('THANKSGIVING2011', '2011-11-24', '2011-11-25')
,('CHRISTMASEVE2011', '2011-12-24', '2011-12-25')
,('CHRISTMASDAY2011', '2011-12-25', '2011-12-26')
,('BOXINGDAY2011', '2011-12-26', '2011-12-27')
,('NEWYEARSEVE2011', '2011-12-31', '2012-01-01')
,('NEWYEARSDAY2012', '2012-01-01', '2012-01-02')
,('THANKSGIVING2012', '2012-11-22', '2012-11-23')
,('CHRISTMASEVE2012', '2012-12-24', '2012-12-25')
,('CHRISTMASDAY2012', '2012-12-25', '2012-12-26')
,('BOXINGDAY2012', '2012-12-26', '2012-12-27')
,('NEWYEARSEVE2012', '2012-12-31', '2013-01-01')
,('NEWYEARSDAY2013', '2013-01-01', '2013-01-02');
INSERT INTO Groups (Name)
VALUES ('HOLIDAYS2011')
,('HOLIDAYS2012')
,('ANYCHRISTMAS');
INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2011')
FROM Ranges WHERE Name LIKE '%2011';
INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2012')
FROM Ranges WHERE Name LIKE '%2012';
INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'ANYCHRISTMAS')
FROM Ranges WHERE Name LIKE 'CHRISTMAS%';
CREATE FUNCTION dbo.JustDoIt(@GroupName VARCHAR(50))
RETURNS TABLE
RETURN (
SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName
);
SELECT *
FROM dbo.JustDoIt('HOLIDAYS2011');
SELECT *
FROM dbo.JustDoIt('HOLIDAYS2012');
SELECT *
FROM dbo.JustDoIt('ANYCHRISTMAS');
This part:
SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName
Means that each log item will be joined to the ranges it fits in (by date), each range will join to the groups they are in, but only that group selected will be used.
Related Question
- Sql-server – SQL Server 2008R2 – How to prevent a role from creating or altering views within a database
- Creating Default Stored Procedures for New Databases in SQL Server
- Sql-server – How to create a role to create,update,delete stored procedures and views
- SQL Server – CREATE and DROP TABLE Permissions in Stored Procedures
- Sql-server – Grant non Sysadmin the ability to change max memory setting SQL Server
- SQL Server 2017 – How to Create a View with SNAPSHOT_MATERIALIZATION
- SQL Server 2017 – Create Table Permissions Issues
Best Answer
Create an explicit schema or schemas that are separate from any other schemas and objects that are replicated from your publishers.