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]
GO
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
>
>
|||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...[vbcol=seagreen]
> 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:

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

Adding a Adam group to SQL Server

Hello all, I 'm not sure if this is the right forum for this, and I apologies if so...

But in a nut shell I'm attempting to grant SQL server access to a ADAM user group account within an ADAM instance.

I have set up the ADAM instance. Added the ADAM user to an ADAM security group. now I need to add that user group to a SQL instance that resides on our AD domain ( non-ADAM instance.) obviously when I attempt to view all available domains within SQL security manager, all I see is our standard domain, I can't seem to figure out how to make the ADAM domain visible to our AD domain.

Any Thoughts?

Thanks

Unfortunately I am not familiar with ADAM, and given that there hasn't been any response yet I would assume most people in this forum have limited or no experience on this topic either.

I would suggest asking in the Windows or AD forums and find out if there is a difference in ADAM authetntication with respect of regular ADs.

-Raul Garcia

SDE/T

SQL Server Engine

|||Will do. Thank you.|||

Not possible. ADAM is not a network OS like AD.... ADAM groups aren't available to SQL server ...

what you can do is: If all your users exist in AD and your ADAM is domain joined, create the groups in AD instead and use them in ADAM as well. ADAM can use AD security principals for Authentication and Authorization.

thanks

Adding a Adam group to SQL Server

Hello all, I 'm not sure if this is the right forum for this, and I apologies if so...

But in a nut shell I'm attempting to grant SQL server access to a ADAM user group account within an ADAM instance.

I have set up the ADAM instance. Added the ADAM user to an ADAM security group. now I need to add that user group to a SQL instance that resides on our AD domain ( non-ADAM instance.) obviously when I attempt to view all available domains within SQL security manager, all I see is our standard domain, I can't seem to figure out how to make the ADAM domain visible to our AD domain.

Any Thoughts?

Thanks

Unfortunately I am not familiar with ADAM, and given that there hasn't been any response yet I would assume most people in this forum have limited or no experience on this topic either.

I would suggest asking in the Windows or AD forums and find out if there is a difference in ADAM authetntication with respect of regular ADs.

-Raul Garcia

SDE/T

SQL Server Engine

|||Will do. Thank you.|||

Not possible. ADAM is not a network OS like AD.... ADAM groups aren't available to SQL server ...

what you can do is: If all your users exist in AD and your ADAM is domain joined, create the groups in AD instead and use them in ADAM as well. ADAM can use AD security principals for Authentication and Authorization.

thanks

sql

Adding a Adam group to SQL Server

Hello all, I 'm not sure if this is the right forum for this, and I apologies if so...

But in a nut shell I'm attempting to grant SQL server access to a ADAM user group account within an ADAM instance.

I have set up the ADAM instance. Added the ADAM user to an ADAM security group. now I need to add that user group to a SQL instance that resides on our AD domain ( non-ADAM instance.) obviously when I attempt to view all available domains within SQL security manager, all I see is our standard domain, I can't seem to figure out how to make the ADAM domain visible to our AD domain.

Any Thoughts?

Thanks

Unfortunately I am not familiar with ADAM, and given that there hasn't been any response yet I would assume most people in this forum have limited or no experience on this topic either.

I would suggest asking in the Windows or AD forums and find out if there is a difference in ADAM authetntication with respect of regular ADs.

-Raul Garcia

SDE/T

SQL Server Engine

|||Will do. Thank you.|||

Not possible. ADAM is not a network OS like AD.... ADAM groups aren't available to SQL server ...

what you can do is: If all your users exist in AD and your ADAM is domain joined, create the groups in AD instead and use them in ADAM as well. ADAM can use AD security principals for Authentication and Authorization.

thanks

Add Login

I want to add login in SQL Server 2005 through Query Analyzer and grant him
System Administrative Role through query analyzer, can any one help me out?
hi,
that is quite easy, according to SQl Server 2005 this is:
CREATE LOGIN SomeUser WITH PASSWORD = 'SomePassword'
sp_addsrvrolemember 'SomeUser','sysadmin'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de