Adding a restricted role to SQL Server 2005

I have a database and I want to grant an external user access to only one
view in my database. Using SQL Studio I generated these scripts and ran
them.
Once I did that I logged into the database as [loginExternalApp] and was
happy that I could not see any of the raw tables. But I found that I could
SELECT from all of the views in my database, not just the one I granted the
SELECT to.
What did I miss? I thought by default that the new role would have no
permissions for anything that I did not grant. It doesn't seem right that I
would need to specifically deny access to all of the views.
Any thoughts would be welcome.
Richard Speiss
Here is the script that I ran
USE [master]
GO
CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
GO
USE [TestDB]
GO
CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
GO
CREATE ROLE [roleExternalApp]
GO
EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
GO
GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
GOHi Richard,
Verify the permissions granted to the public role. Every user is a member of
the public role and has access to the objects granted to it.
Right-click any of these views in Management Studio, select Properties and
Permissions. See if permissions have been granted to public.
Also, you are not talking about catalog views, right?
Hope this helps,
Ben Nevarez
"Richard Speiss" wrote:
> I have a database and I want to grant an external user access to only one
> view in my database. Using SQL Studio I generated these scripts and ran
> them.
> Once I did that I logged into the database as [loginExternalApp] and was
> happy that I could not see any of the raw tables. But I found that I could
> SELECT from all of the views in my database, not just the one I granted the
> SELECT to.
> What did I miss? I thought by default that the new role would have no
> permissions for anything that I did not grant. It doesn't seem right that I
> would need to specifically deny access to all of the views.
> Any thoughts would be welcome.
> Richard Speiss
>
> Here is the script that I ran
> USE [master]
> GO
> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
> GO
> USE [TestDB]
> GO
> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
> GO
> CREATE ROLE [roleExternalApp]
> GO
> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
> GO
> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
> GO
>
>|||Thanks, those views are granting 'public' accessibility (user views, not
catalog views)
I don't see an easy way to disable the 'public' role though or to get my
[userExternalApp] to not be part of 'public'.
Is there any way to disable the public role or do I need to explicitly
revoke permissions to public on each view (and/or table, etc)?
Thanks again
Richard
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:449C85C5-A8D8-49F5-8480-80584D1E91CD@.microsoft.com...
> Hi Richard,
> Verify the permissions granted to the public role. Every user is a member
> of
> the public role and has access to the objects granted to it.
> Right-click any of these views in Management Studio, select Properties and
> Permissions. See if permissions have been granted to public.
> Also, you are not talking about catalog views, right?
> Hope this helps,
> Ben Nevarez
>
>
> "Richard Speiss" wrote:
>> I have a database and I want to grant an external user access to only one
>> view in my database. Using SQL Studio I generated these scripts and ran
>> them.
>> Once I did that I logged into the database as [loginExternalApp] and was
>> happy that I could not see any of the raw tables. But I found that I
>> could
>> SELECT from all of the views in my database, not just the one I granted
>> the
>> SELECT to.
>> What did I miss? I thought by default that the new role would have no
>> permissions for anything that I did not grant. It doesn't seem right
>> that I
>> would need to specifically deny access to all of the views.
>> Any thoughts would be welcome.
>> Richard Speiss
>>
>> Here is the script that I ran
>> USE [master]
>> GO
>> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
>> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
>> GO
>> USE [TestDB]
>> GO
>> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
>> GO
>> CREATE ROLE [roleExternalApp]
>> GO
>> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
>> GO
>> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
>> GO
>>