permissions with sql server tables and access

Hello,
I need some help with implenting the following:
I recently migrated from access to sql server and i now i want to use
maintainable permissions on my tables, views, etc. The access database will
serve as a front-end.
I've created for testing purposes an testaccount with only a public role to
access to my database.
Now the hard part is when i want users to select and manipulate the data
through views and stored procedures.I want only permissions set on views and
stored procedures. The reason for this is because i don't want users to get
the data directly from tables by means of linking or importing them to
access
or other databases. Only views and stored procedures can be used.
Unfortunelately it doesn't work how i wanted to. When i open a view which is
linked in access as a table, i'm getting a message that the underlying table
has not the appropiate permissions.
Now there should be a way to apply a maintainable security, so if i could
have some advice and maybe an example on this matter i would be very
thankful.Create the view with the WITH VIEW_METADATA option, which will allow
users to use the view to update data. Without it, permissions on the
base tables are required. See the CREATE VIEW topic in SQL BooksOnline
for more information. If you put a Profiler trace on the Access-SQLS
app, you can see the exact calls that are being made. This will help
you troubleshoot future issues.
--Mary
On Fri, 13 Aug 2004 20:19:28 +0200, "Ezekil" <ezekil@.lycios.nl>
wrote:

>Hello,
>I need some help with implenting the following:
>I recently migrated from access to sql server and i now i want to use
>maintainable permissions on my tables, views, etc. The access database will
>serve as a front-end.
>I've created for testing purposes an testaccount with only a public role to
>access to my database.
>Now the hard part is when i want users to select and manipulate the data
>through views and stored procedures.I want only permissions set on views an
d
>stored procedures. The reason for this is because i don't want users to get
>the data directly from tables by means of linking or importing them to
>access
>or other databases. Only views and stored procedures can be used.
>Unfortunelately it doesn't work how i wanted to. When i open a view which i
s
>linked in access as a table, i'm getting a message that the underlying tabl
e
>has not the appropiate permissions.
>Now there should be a way to apply a maintainable security, so if i could
>have some advice and maybe an example on this matter i would be very
>thankful.
>|||Do you have an example? BOL is not very clear to me.
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:k37sh0hnmit3dfkgj0hk7tn7pi8cdem6g9@.
4ax.com...
> Create the view with the WITH VIEW_METADATA option, which will allow
> users to use the view to update data. Without it, permissions on the
> base tables are required. See the CREATE VIEW topic in SQL BooksOnline
> for more information. If you put a Profiler trace on the Access-SQLS
> app, you can see the exact calls that are being made. This will help
> you troubleshoot future issues.
> --Mary
> On Fri, 13 Aug 2004 20:19:28 +0200, "Ezekil" <ezekil@.lycios.nl>
> wrote:
>
will[vbcol=seagreen]
to[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
is[vbcol=seagreen]
table[vbcol=seagreen]
>|||You've asked this same question in comp.databases.ms-sqlserver. Please
don't post the same question independently to multiple groups as this causes
duplication of effort.
Here's the example I posted to that thread:
CREATE TABLE dbo.MyTable
(
Col1 int NOT NULL,
Col2 int NOT NULL
)
GO
CREATE VIEW dbo.MyView
WITH VIEW_METADATA
AS
SELECT Col1
FROM dbo.MyTable
GO
GRANT SELECT ON MyView TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP

permissions with sql server tables

Hello,

I need some help with implenting the following:

I recently migrated from access to sql server and i now i want to use
maintainable permissions on my tables, views, etc. The access database will
serve as a front-end.

I've created for testing purposes an testaccount with only a public role to
access to my database.

Now the hard part is when i want users to select and manipulate the data
through views and stored procedures.I want only permissions set on views and
stored procedures. The reason for this is because i don't want users to get
the data directly from tables by means of linking or importing them to
access
or other databases. Only views and stored procedures can be used.

Unfortunelately it doesn't work how i wanted to. When i open a view which is
linked in access as a table, i'm getting a message that the underlying table
has not the appropiate permissions.

Now there should be a way to apply a maintainable security, so if i could
have some advice and maybe an example on this matter i would be very
thankful.Try creating the view with the VIEW_METADATA option. This way, Access will
use view meta data instead of meta data from the underlying base tables.
See CREATE VIEW in the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ezekil" <ezekil@.lycos.com> wrote in message
news:411d05d7$0$195$cd19a363@.news.wanadoo.nl...
> Hello,
> I need some help with implenting the following:
> I recently migrated from access to sql server and i now i want to use
> maintainable permissions on my tables, views, etc. The access database
will
> serve as a front-end.
> I've created for testing purposes an testaccount with only a public role
to
> access to my database.
> Now the hard part is when i want users to select and manipulate the data
> through views and stored procedures.I want only permissions set on views
and
> stored procedures. The reason for this is because i don't want users to
get
> the data directly from tables by means of linking or importing them to
> access
> or other databases. Only views and stored procedures can be used.
> Unfortunelately it doesn't work how i wanted to. When i open a view which
is
> linked in access as a table, i'm getting a message that the underlying
table
> has not the appropiate permissions.
> Now there should be a way to apply a maintainable security, so if i could
> have some advice and maybe an example on this matter i would be very
> thankful.|||Hi Dan,

I've looked it up in BOL but it is not very clear. Could you provide me an
example?

Thnx
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:wxfTc.20463$9Y6.12982@.newsread1.news.pas.eart hlink.net...
> Try creating the view with the VIEW_METADATA option. This way, Access
will
> use view meta data instead of meta data from the underlying base tables.
> See CREATE VIEW in the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ezekil" <ezekil@.lycos.com> wrote in message
> news:411d05d7$0$195$cd19a363@.news.wanadoo.nl...
> > Hello,
> > I need some help with implenting the following:
> > I recently migrated from access to sql server and i now i want to use
> > maintainable permissions on my tables, views, etc. The access database
> will
> > serve as a front-end.
> > I've created for testing purposes an testaccount with only a public role
> to
> > access to my database.
> > Now the hard part is when i want users to select and manipulate the data
> > through views and stored procedures.I want only permissions set on views
> and
> > stored procedures. The reason for this is because i don't want users to
> get
> > the data directly from tables by means of linking or importing them to
> > access
> > or other databases. Only views and stored procedures can be used.
> > Unfortunelately it doesn't work how i wanted to. When i open a view
which
> is
> > linked in access as a table, i'm getting a message that the underlying
> table
> > has not the appropiate permissions.
> > Now there should be a way to apply a maintainable security, so if i
could
> > have some advice and maybe an example on this matter i would be very
> > thankful.|||Here's a simple example:

CREATE TABLE dbo.MyTable
(
Col1 int NOT NULL,
Col2 int NOT NULL
)
GO

CREATE VIEW dbo.MyView
WITH VIEW_METADATA
AS
SELECT Col1
FROM dbo.MyTable
GO

GRANT SELECT ON MyView TO MyRole
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ezekil" <ezekil@.lycos.com> wrote in message
news:411df325$0$80325$a344fe98@.news.wanadoo.nl...
> Hi Dan,
> I've looked it up in BOL but it is not very clear. Could you provide me
an
> example?
> Thnx
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:wxfTc.20463$9Y6.12982@.newsread1.news.pas.eart hlink.net...
> > Try creating the view with the VIEW_METADATA option. This way, Access
> will
> > use view meta data instead of meta data from the underlying base tables.
> > See CREATE VIEW in the Books Online for more information.
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Ezekil" <ezekil@.lycos.com> wrote in message
> > news:411d05d7$0$195$cd19a363@.news.wanadoo.nl...
> > > Hello,
> > > > I need some help with implenting the following:
> > > > I recently migrated from access to sql server and i now i want to use
> > > maintainable permissions on my tables, views, etc. The access database
> > will
> > > serve as a front-end.
> > > > I've created for testing purposes an testaccount with only a public
role
> > to
> > > access to my database.
> > > > Now the hard part is when i want users to select and manipulate the
data
> > > through views and stored procedures.I want only permissions set on
views
> > and
> > > stored procedures. The reason for this is because i don't want users
to
> > get
> > > the data directly from tables by means of linking or importing them to
> > > access
> > > or other databases. Only views and stored procedures can be used.
> > > > Unfortunelately it doesn't work how i wanted to. When i open a view
> which
> > is
> > > linked in access as a table, i'm getting a message that the underlying
> > table
> > > has not the appropiate permissions.
> > > > Now there should be a way to apply a maintainable security, so if i
> could
> > > have some advice and maybe an example on this matter i would be very
> > > thankful.
> >

permissions not scripting in 2000

We have several databases that were migrated to SQL2000 from 7.0 on the same
server running Windows 2000 Server. Only a couple of the databases are having
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch for
this particular database or something? Any help on this matter would be
greatly appreciated.
Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.
|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the other
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object in
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:

> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>
>
|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...[vbcol=seagreen]
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:

permissions not scripting in 2000

We have several databases that were migrated to SQL2000 from 7.0 on the same
server running Windows 2000 Server. Only a couple of the databases are havin
g
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch fo
r
this particular database or something? Any help on this matter would be
greatly appreciated.Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the othe
r
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object i
n
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:

> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>
>|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...[vbcol=seagreen]
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
>

permissions not scripting in 2000

We have several databases that were migrated to SQL2000 from 7.0 on the same
server running Windows 2000 Server. Only a couple of the databases are having
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch for
this particular database or something? Any help on this matter would be
greatly appreciated.Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the other
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object in
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:
> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> > We have several databases that were migrated to SQL2000 from 7.0 on the
> > same
> > server running Windows 2000 Server. Only a couple of the databases are
> > having
> > a problem that when we script out the stored procedures or any object for
> > that matter (with the script permissions box selected) the object or SP is
> > scripted out but the GRANT permissions portion of the script is omitted.
> > We
> > also cannot see the permissions for the object in the GUI of Enterprise
> > Manager. The other databases work fine on the same server. Is it a switch
> > for
> > this particular database or something? Any help on this matter would be
> > greatly appreciated.
>
>|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
--
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
>> Did you check "Script object-level permissions" on the Options tab.
>> Also, you can generate scripts via Query Analyzer. It has the options for
>> scripting permissions, too.
>>
>> --
>> -oj
>>
>> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
>> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>> > We have several databases that were migrated to SQL2000 from 7.0 on the
>> > same
>> > server running Windows 2000 Server. Only a couple of the databases are
>> > having
>> > a problem that when we script out the stored procedures or any object
>> > for
>> > that matter (with the script permissions box selected) the object or SP
>> > is
>> > scripted out but the GRANT permissions portion of the script is
>> > omitted.
>> > We
>> > also cannot see the permissions for the object in the GUI of Enterprise
>> > Manager. The other databases work fine on the same server. Is it a
>> > switch
>> > for
>> > this particular database or something? Any help on this matter would be
>> > greatly appreciated.
>>sql

Permissions BUILTIN/Administrator

We recently migrated from an NT4 domain to a Windows 2003 domain.
I was using the login olddomain\paulc and I now use newdomain\paulc
My pc and the SQL server have been moved to the new domain.
Our nt admins have created a new group for the sql admins/developers on the
new domain called "Sql Admins". I was an NT Domain Admin on the old domain.
I have added this new group to sql server however I am being validated as
BUILTIN\Administrators.
exec master..xp_logininfo 'DOMAIN\username'
Any idea why this is? I think this is an NT issue. I believe there is an NT
trust between the old and
new domains.
Regards
Paul CahillPaul,
Not sure if I understand your situation right, but basically the
BUILTIN\Admins is the same as you local administrators group on the
server. If your new account is a member of this local administrators
group, either direct or through group membership SQL Server will
validate you as BUILTIN\Administrators
Markus|||Cheers Markus.
I'll have a look or words with our IS admins.
<m.bohse@.quest-consultants.com> wrote in message
news:1133356379.166090.122130@.g47g2000cwa.googlegroups.com...
> Paul,
> Not sure if I understand your situation right, but basically the
> BUILTIN\Admins is the same as you local administrators group on the
> server. If your new account is a member of this local administrators
> group, either direct or through group membership SQL Server will
> validate you as BUILTIN\Administrators
> Markus
>|||The IS guys had made all us Sql dba/developers domain admins and domain
admins has been added to the local administator group.
All is cool.
Thanks again Markus.
Paul
<m.bohse@.quest-consultants.com> wrote in message
news:1133356379.166090.122130@.g47g2000cwa.googlegroups.com...
> Paul,
> Not sure if I understand your situation right, but basically the
> BUILTIN\Admins is the same as you local administrators group on the
> server. If your new account is a member of this local administrators
> group, either direct or through group membership SQL Server will
> validate you as BUILTIN\Administrators
> Markus
>

Permission Denied to object

We recently brought up SQL Server and migrated our Access 2000 back end DB t
o
it. The SQL Server security seemed pretty straight forward to me...I set up
the users and the roles and assigned the roles rights to my tables. The
problem is that my users get a Permission Denied error when ever the Access
2000 front end tries to access any SQL Server table unless I add them to the
System Administrator server role. I've gone over everything and I can't see
m
to figure this out. The only thing I can find that seems strange is that on
that database, my login name is associated with the "dbo" name. I think tha
t
makes me the owner of the db and I'm wondering if that is what is causing my
issue. I'd greatly appreciate any insight on how to get this issue taken
care of.
Thanks in advance!
JimNevermind, I found the answer to my problem in another post. Don't assign
the db_denydatareader or db_denydatawriter role to the user. That cleared u
p
my problem.
"Jim" wrote:

> We recently brought up SQL Server and migrated our Access 2000 back end DB
to
> it. The SQL Server security seemed pretty straight forward to me...I set
up
> the users and the roles and assigned the roles rights to my tables. The
> problem is that my users get a Permission Denied error when ever the Acces
s
> 2000 front end tries to access any SQL Server table unless I add them to t
he
> System Administrator server role. I've gone over everything and I can't s
eem
> to figure this out. The only thing I can find that seems strange is that
on
> that database, my login name is associated with the "dbo" name. I think t
hat
> makes me the owner of the db and I'm wondering if that is what is causing
my
> issue. I'd greatly appreciate any insight on how to get this issue taken
> care of.
> Thanks in advance!
> Jim