View Only Database Role

  • 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,

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply