March 29, 2011 at 8:59 am
Hi,
Is there a way to create a Database Role to grant select option to Views not Tables? If possible I would like to use a script rather than the Database Role Properties GUI. I'm using the script below; let me know if I need to use something different or modify the script.
GRANT SELECT ON VIEW_NAME TO VIEW_ONLY_DATABASE_ROLE
GO
The issue I'm running into is that when I add a View to Database Role and assign the role to a user, the user also has a access to the tables used to create the Views. The reason we don't want the users to do a select on tables is that there are some information within the tables that we don't want users to have access.
This is for SQL Server 2005.
Thanks,
March 29, 2011 at 9:41 am
you are seeing multiple roles on a single user, and thinking only one of the roles is in place i think.
i just tested this script, and my user does not have access to underlying tables...therefor your user is either in multiple roles, and the other role is giving them permissions to the underlying tables, or maybe even the public role was granted access.
CREATE ROLE [ReadViewsOnly]
--cursor to generate all commands?
DECLARE
@isql VARCHAR(2000),
@objname VARCHAR(255)
DECLARE c1 CURSOR
FOR
SELECT
QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(name) AS tbl
FROM sys.objects
WHERE type_desc='VIEW'
OPEN c1
FETCH NEXT FROM c1 INTO @objname
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @isql = 'GRANT SELECT ON ' + @objname + ' TO [ReadViewsOnly]'
PRINT @isql
EXEC(@isql)
FETCH NEXT FROM c1 INTO @objname
END
CLOSE c1
DEALLOCATE c1
--CREATE A USER to text permissions
CREATE USER ViewTester WITHOUT LOGIN
--add the user to the role
EXEC sp_addrolemember N'ReadViewsOnly', N'ViewTester'
EXECUTE AS USER='ViewTester'
--who am i? i better be USER_NAME() = 'ViewTester'
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
--try to see the list of tables
select * from sys.tables(i see nothing)
--try to select from a known view(these were unique to me)
select * from [dbo].[VW_CITYCOUNTYSTATE]
--try to select from a known table that is part of that view(these were unique to me)
select * from [dbo].[TBCITY]
--turn back into myself
REVERT;
DROP USER ViewTester;
DROP ROLE ReadViewsOnly;
use this to see if it is the public role that is giving your user too many permissions:
--adapted from http://persistencevision.blogspot.com/2009/09/find-all-public-role-permissions.html
-- Find all permissions in all databases granted to the public role
-- Modified By Lowell to limit to just the current database instead.
--Find all permissions in THIS database granted to the public role
SET NOCOUNT ON
GO
IF OBJECT_ID('tempdb..#ObjectList') IS NOT NULL
DROP TABLE #ObjectList
CREATE TABLE #ObjectList (
EntryID int IDENTITY PRIMARY KEY,
DatabaseName nvarchar(128),
ObjectType nvarchar(60),
ObjectName sysname,
Permission nvarchar(128),
GrantState nvarchar(60)
)
DECLARE @dbCt int
DECLARE @objCt int
DECLARE @dbName nvarchar(128)
DECLARE @sql nvarchar(4000)
SET @dbName = db_name()
SET @sql =
N'USE ' + QUOTENAME(@dbName) + ';
INSERT INTO #ObjectList (
DatabaseName,
ObjectType,
ObjectName,
Permission,
GrantState
)
SELECT
DB_NAME(),
o.type_desc,
o.name,
dp.permission_name,
dp.state_desc
FROM sys.database_permissions AS dp
JOIN sys.objects AS o ON dp.major_id = o.[object_id]
WHERE 1 = 1
AND dp.grantee_principal_id = (
SELECT principal_id
FROM sys.database_principals
WHERE name = ''public''
)
AND dp.[state] IN (''G'', ''W'')
AND o.is_ms_shipped = 0
AND o.name NOT LIKE ''MSmerge%''
AND o.name NOT LIKE ''sysmerge%''
AND o.name NOT IN (
''MSsubscription_properties''
)
ORDER BY
o.type_desc,
o.name,
dp.permission_name'
EXEC sp_executesql
@stmt = @sql,
@params = N'@dbNameParam nvarchar(128)',
@dbNameParam = @dbName;
SELECT
ROW_NUMBER() OVER (
PARTITION BY DatabaseName
ORDER BY EntryID
) AS [DB Row],
DatabaseName,
ObjectType,
ObjectName,
Permission,
GrantState
FROM #ObjectList
ORDER BY
EntryID
GO
Lowell
March 29, 2011 at 9:43 am
You could try going with a slightly different approach. Set up the views under a different schema and then grant the user SELECT access to that schema.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply