Look at the article from Erland:
http://www.sommarskog.se/share_data.html
HTH, jens Suessmeyer.
sql
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Wednesday, March 28, 2012
Persistence Of Temporary Tables
Is it possible to create a temporary table in a 'parent' stored procedure and then access it from a 'child' or nested stored procedure? Bearing in mind that the child proc will definitely be called by the parent proc.
Monday, March 26, 2012
Permit view and deny table?
This has probably been asked before, but is there a way to allow a user to
access a view but deny access to the underlying table? If so, how is this
done (even as a kluge)?
Thanks!For a user to be given select permissions on a view but not a table
referenced by the view, the view and underlying table must have the same
owner. This forms an ownership chain. As long as that is unbroken then the
permission check will be on the view and not the underlying table. By not
granting explict permissions on your tables and the user will not be able to
select from them.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil W." <neilw@.netlib.com> wrote in message
news:uflVC0shEHA.3320@.TK2MSFTNGP11.phx.gbl...
> This has probably been asked before, but is there a way to allow a user to
> access a view but deny access to the underlying table? If so, how is this
> done (even as a kluge)?
> Thanks!
>
access a view but deny access to the underlying table? If so, how is this
done (even as a kluge)?
Thanks!For a user to be given select permissions on a view but not a table
referenced by the view, the view and underlying table must have the same
owner. This forms an ownership chain. As long as that is unbroken then the
permission check will be on the view and not the underlying table. By not
granting explict permissions on your tables and the user will not be able to
select from them.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil W." <neilw@.netlib.com> wrote in message
news:uflVC0shEHA.3320@.TK2MSFTNGP11.phx.gbl...
> This has probably been asked before, but is there a way to allow a user to
> access a view but deny access to the underlying table? If so, how is this
> done (even as a kluge)?
> Thanks!
>
Permissons Question
Hi All
Is it possible to grant a user permissons so they are
only able to execute a SQL Job and not have access to
anything else in a database.
Thanks
Hi
Yes, altough not with job, but with stored procecdure.
Andras Jakus MCDBA
"anonymous@.discussions.microsoft.com" wrote:
> Hi All
> Is it possible to grant a user permissons so they are
> only able to execute a SQL Job and not have access to
> anything else in a database.
> Thanks
>
Is it possible to grant a user permissons so they are
only able to execute a SQL Job and not have access to
anything else in a database.
Thanks
Hi
Yes, altough not with job, but with stored procecdure.
Andras Jakus MCDBA
"anonymous@.discussions.microsoft.com" wrote:
> Hi All
> Is it possible to grant a user permissons so they are
> only able to execute a SQL Job and not have access to
> anything else in a database.
> Thanks
>
permissions..
How do I change permissions so I can access a linkedserver I create?If you created it you should already have permissions ??|||Permissions default to members of the sysadmin and setupadmin fixed server roles.
Permissions WTF
I have a intigrated log using a local group
the login does not have explicit access to any db's and no server roles
when i log on with query analyzer i can access any data (even though not a
server admin or anything else)
when i remove login...can log (as it should be)
why does this login have full access to all db's ?When i add the login and run IS_MEMBER and USER it says i am dbo of a user
database that i did not give access to what is going on ?
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uNI5pih6FHA.3684@.TK2MSFTNGP12.phx.gbl...
>I have a intigrated log using a local group
> the login does not have explicit access to any db's and no server roles
> when i log on with query analyzer i can access any data (even though not a
> server admin or anything else)
> when i remove login...can log (as it should be)
> why does this login have full access to all db's ?
>|||You are getting in with sysadmin priviliges, possibly by membership of
another group. What does the following query return for your windows
account?
exec master..xp_logininfo 'DOMAIN\username'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uNI5pih6FHA.3684@.TK2MSFTNGP12.phx.gbl...
>I have a intigrated log using a local group
> the login does not have explicit access to any db's and no server roles
> when i log on with query analyzer i can access any data (even though not a
> server admin or anything else)
> when i remove login...can log (as it should be)
> why does this login have full access to all db's ?
>|||Thanks for reply, but it was my stupidity...i had been logged in as the
user when i had created the user databases...then forgot it was a member of
the group used to create the login....should have realized it when it said
i was member of db_owner and not srv role sysadmin...but i am getting slow
at my old age.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:upEiioj6FHA.476@.TK2MSFTNGP15.phx.gbl...
> You are getting in with sysadmin priviliges, possibly by membership of
> another group. What does the following query return for your windows
> account?
> exec master..xp_logininfo 'DOMAIN\username'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:uNI5pih6FHA.3684@.TK2MSFTNGP12.phx.gbl...
>
the login does not have explicit access to any db's and no server roles
when i log on with query analyzer i can access any data (even though not a
server admin or anything else)
when i remove login...can log (as it should be)
why does this login have full access to all db's ?When i add the login and run IS_MEMBER and USER it says i am dbo of a user
database that i did not give access to what is going on ?
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uNI5pih6FHA.3684@.TK2MSFTNGP12.phx.gbl...
>I have a intigrated log using a local group
> the login does not have explicit access to any db's and no server roles
> when i log on with query analyzer i can access any data (even though not a
> server admin or anything else)
> when i remove login...can log (as it should be)
> why does this login have full access to all db's ?
>|||You are getting in with sysadmin priviliges, possibly by membership of
another group. What does the following query return for your windows
account?
exec master..xp_logininfo 'DOMAIN\username'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uNI5pih6FHA.3684@.TK2MSFTNGP12.phx.gbl...
>I have a intigrated log using a local group
> the login does not have explicit access to any db's and no server roles
> when i log on with query analyzer i can access any data (even though not a
> server admin or anything else)
> when i remove login...can log (as it should be)
> why does this login have full access to all db's ?
>|||Thanks for reply, but it was my stupidity...i had been logged in as the
user when i had created the user databases...then forgot it was a member of
the group used to create the login....should have realized it when it said
i was member of db_owner and not srv role sysadmin...but i am getting slow
at my old age.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:upEiioj6FHA.476@.TK2MSFTNGP15.phx.gbl...
> You are getting in with sysadmin priviliges, possibly by membership of
> another group. What does the following query return for your windows
> account?
> exec master..xp_logininfo 'DOMAIN\username'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:uNI5pih6FHA.3684@.TK2MSFTNGP12.phx.gbl...
>
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
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.
> >
Friday, March 23, 2012
Permissions set to Roles disappeared
We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.
We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.
Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)
Tim.
Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.
Thanks
Laurentiu
|||This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
use aspstate
go
GRANT EXEC ON TempGetStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;
GO
GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;
GO
GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempResetTimeout TO MyPeakASPState;
GO
GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;
GO
GRANT EXEC ON DropTempTables TO MyPeakASPState;
GO
GRANT EXEC ON GetMajorVersion TO MyPeakASPState;
GO
GRANT EXEC ON CreateTempTables TO MyPeakASPState;
GO
GRANT EXEC ON ResetData TO MyPeakASPState;
GO
GRANT EXEC ON TempGetAppID TO MyPeakASPState
|||I wish I could help you. I'm interested that it happened to someone else.
The only advice I can give you - becareful not to change logins when changing security.
My problem may have occurred because I was testing security on a user.
Tim.
|||I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.
I hope this information helps,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks, I didn't know that.
Permissions set to Roles disappeared
We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.
We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.
Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)
Tim.
Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.
Thanks
Laurentiu
|||This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
use aspstate
go
GRANT EXEC ON TempGetStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;
GO
GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;
GO
GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempResetTimeout TO MyPeakASPState;
GO
GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;
GO
GRANT EXEC ON DropTempTables TO MyPeakASPState;
GO
GRANT EXEC ON GetMajorVersion TO MyPeakASPState;
GO
GRANT EXEC ON CreateTempTables TO MyPeakASPState;
GO
GRANT EXEC ON ResetData TO MyPeakASPState;
GO
GRANT EXEC ON TempGetAppID TO MyPeakASPState
|||I wish I could help you. I'm interested that it happened to someone else.
The only advice I can give you - becareful not to change logins when changing security.
My problem may have occurred because I was testing security on a user.
Tim.
|||I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.
I hope this information helps,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks, I didn't know that.
Permissions set to Roles disappeared
We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.
We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.
Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)
Tim.
Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.
Thanks
Laurentiu
|||This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
use aspstate
go
GRANT EXEC ON TempGetStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;
GO
GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;
GO
GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;
GO
GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;
GO
GRANT EXEC ON TempResetTimeout TO MyPeakASPState;
GO
GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;
GO
GRANT EXEC ON DropTempTables TO MyPeakASPState;
GO
GRANT EXEC ON GetMajorVersion TO MyPeakASPState;
GO
GRANT EXEC ON CreateTempTables TO MyPeakASPState;
GO
GRANT EXEC ON ResetData TO MyPeakASPState;
GO
GRANT EXEC ON TempGetAppID TO MyPeakASPState
|||I wish I could help you. I'm interested that it happened to someone else.
The only advice I can give you - becareful not to change logins when changing security.
My problem may have occurred because I was testing security on a user.
Tim.
|||I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?
use tempdb;
go
sp_grantdbaccess MyPeakASPState;
GRANT SELECT on ASPStateTempApplications to MyPeakASPState;
GRANT INSERT on ASPStateTempApplications to MyPeakASPState;
GRANT SELECT on ASPStateTempSessions to MyPeakASPState;
GRANT INSERT on ASPStateTempSessions to MyPeakASPState;
GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;
GO
Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.
I hope this information helps,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks, I didn't know that.
sqlPermissions question on SQL Server 2000
I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:
>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell wha
t
>access the public role gives a user?
>Thanks
>Don
>
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:
>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell wha
t
>access the public role gives a user?
>Thanks
>Don
>
Permissions question on SQL Server 2000
I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
Don
Yes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:
>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell what
>access the public role gives a user?
>Thanks
>Don
>
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
Don
Yes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:
>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell what
>access the public role gives a user?
>Thanks
>Don
>
Permissions question on SQL Server 2000
I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:
>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell what
>access the public role gives a user?
>Thanks
>Don
>sql
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:
>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell what
>access the public role gives a user?
>Thanks
>Don
>sql
Permissions question
Hi,
I need to give access to a domain user group DOMAIN\Developers so that they
can ONLY start/stop SQL Jobs. Which is the best role to grant.
Thanks
Reddipermissions to run sp_stop_job and sp_start_job(msdb) plus permissions to
execute xp_sqlagent_enum_jobs(master) if accounts are not in sysadmins
and/or do not own the sqlagent jobs
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:2F34F361-C7B3-426B-AAC2-976E93039EC6@.microsoft.com...
> Hi,
> I need to give access to a domain user group DOMAIN\Developers so that
they
> can ONLY start/stop SQL Jobs. Which is the best role to grant.
> Thanks
> Reddi
I need to give access to a domain user group DOMAIN\Developers so that they
can ONLY start/stop SQL Jobs. Which is the best role to grant.
Thanks
Reddipermissions to run sp_stop_job and sp_start_job(msdb) plus permissions to
execute xp_sqlagent_enum_jobs(master) if accounts are not in sysadmins
and/or do not own the sqlagent jobs
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:2F34F361-C7B3-426B-AAC2-976E93039EC6@.microsoft.com...
> Hi,
> I need to give access to a domain user group DOMAIN\Developers so that
they
> can ONLY start/stop SQL Jobs. Which is the best role to grant.
> Thanks
> Reddi
Wednesday, March 21, 2012
Permissions problem (I think)
O.k. Here's the background on my situation. We've got an Access program
running against a SQL 2000 DB. The program was created using Access 2003.
The user that runs this program had Access 2000 on their PC, which was
upgraded to Access 2003. One of the first steps the program does is copy
data from 2 external files into 2 seperate tables(1 table for each file).
This is done with the TransferText command. When the user runs the program,
a new table with the same name as the original is created with the user id
shown as the owner. The data is then copied into this new table instead of
the one already in the DB. Thus, when the program tries to read the origina
l
table it fails b/c it is empty. This happens ONLY for this user. If anyone
else logs in on the users PC, the program works fine. I've tried changing
every permissions option available, including giving the user FULL rights an
d
even making them an enterprise admin. Nothing seems to work. All security
is the same as it was before we upgraded the user to Access 2003, and it was
all working before. Any ideas'If the following is the issue and only happens for that
user:
"When the user runs the program, a new table with the same
name as the original is created with the user id shown as
the owner"
then it sounds like the user is in different database roles
than the other user. You didn't say what authentication
method is used or how the access program is connecting, if
it's a project or an mdb, etc. You may want to try running
profiler, capture the Login, user info and compare it when
others run the same routine. Then look at the differences in
database role, server role membership.
-Sue
On Thu, 25 Aug 2005 14:26:09 -0700, "CD"
<CD@.discussions.microsoft.com> wrote:
>O.k. Here's the background on my situation. We've got an Access program
>running against a SQL 2000 DB. The program was created using Access 2003.
>The user that runs this program had Access 2000 on their PC, which was
>upgraded to Access 2003. One of the first steps the program does is copy
>data from 2 external files into 2 seperate tables(1 table for each file).
>This is done with the TransferText command. When the user runs the program
,
>a new table with the same name as the original is created with the user id
>shown as the owner. The data is then copied into this new table instead of
>the one already in the DB. Thus, when the program tries to read the origin
al
>table it fails b/c it is empty. This happens ONLY for this user. If anyon
e
>else logs in on the users PC, the program works fine. I've tried changing
>every permissions option available, including giving the user FULL rights a
nd
>even making them an enterprise admin. Nothing seems to work. All security
>is the same as it was before we upgraded the user to Access 2003, and it wa
s
>all working before. Any ideas'
running against a SQL 2000 DB. The program was created using Access 2003.
The user that runs this program had Access 2000 on their PC, which was
upgraded to Access 2003. One of the first steps the program does is copy
data from 2 external files into 2 seperate tables(1 table for each file).
This is done with the TransferText command. When the user runs the program,
a new table with the same name as the original is created with the user id
shown as the owner. The data is then copied into this new table instead of
the one already in the DB. Thus, when the program tries to read the origina
l
table it fails b/c it is empty. This happens ONLY for this user. If anyone
else logs in on the users PC, the program works fine. I've tried changing
every permissions option available, including giving the user FULL rights an
d
even making them an enterprise admin. Nothing seems to work. All security
is the same as it was before we upgraded the user to Access 2003, and it was
all working before. Any ideas'If the following is the issue and only happens for that
user:
"When the user runs the program, a new table with the same
name as the original is created with the user id shown as
the owner"
then it sounds like the user is in different database roles
than the other user. You didn't say what authentication
method is used or how the access program is connecting, if
it's a project or an mdb, etc. You may want to try running
profiler, capture the Login, user info and compare it when
others run the same routine. Then look at the differences in
database role, server role membership.
-Sue
On Thu, 25 Aug 2005 14:26:09 -0700, "CD"
<CD@.discussions.microsoft.com> wrote:
>O.k. Here's the background on my situation. We've got an Access program
>running against a SQL 2000 DB. The program was created using Access 2003.
>The user that runs this program had Access 2000 on their PC, which was
>upgraded to Access 2003. One of the first steps the program does is copy
>data from 2 external files into 2 seperate tables(1 table for each file).
>This is done with the TransferText command. When the user runs the program
,
>a new table with the same name as the original is created with the user id
>shown as the owner. The data is then copied into this new table instead of
>the one already in the DB. Thus, when the program tries to read the origin
al
>table it fails b/c it is empty. This happens ONLY for this user. If anyon
e
>else logs in on the users PC, the program works fine. I've tried changing
>every permissions option available, including giving the user FULL rights a
nd
>even making them an enterprise admin. Nothing seems to work. All security
>is the same as it was before we upgraded the user to Access 2003, and it wa
s
>all working before. Any ideas'
Permissions on Views vs. Tables
I am using an Access ADP (version 2003) as a front-end to a SQL Server 2000
database. Based on what I have read, I should be able to not let anybody see
the tables directly, and work everything through Views and SPs. To do this,
I
grant no permissions at all on the tables, and appropriate SELECT, INSERT,
UPDATE, and DELETE permissions on the views. However, the views in Access ar
e
still coming up as "Recordset not updatabel". Only by granting permissions o
n
the tables do the views become updatable. Worse yet, if I DENY permissions
for UPDATE etc on the views but grant them on the tables, the views are stil
l
updatable.
This seems very backwards. I thought it was supposed to take the permissions
on the View, regarless of the permissions on the table (except for DENY
permissions, of course).
--
ToddYou can specify the VIEW_METADATA option on the CREATE VIEW statement so
that APIs return metadata for the view rather than the underlying tables.
For example:
CREATE VIEW dbo.MyView
WITH VIEW_METADATA
AS
SELECT MyColumn FROM dbo.MyTable
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Todd Chittenden" <ToddChittenden@.discussions.microsoft.com> wrote in
message news:187A1C5F-5CA1-4FA2-825C-859FFCF0FC20@.microsoft.com...
>I am using an Access ADP (version 2003) as a front-end to a SQL Server 2000
> database. Based on what I have read, I should be able to not let anybody
> see
> the tables directly, and work everything through Views and SPs. To do
> this, I
> grant no permissions at all on the tables, and appropriate SELECT, INSERT,
> UPDATE, and DELETE permissions on the views. However, the views in Access
> are
> still coming up as "Recordset not updatabel". Only by granting permissions
> on
> the tables do the views become updatable. Worse yet, if I DENY permissions
> for UPDATE etc on the views but grant them on the tables, the views are
> still
> updatable.
> This seems very backwards. I thought it was supposed to take the
> permissions
> on the View, regarless of the permissions on the table (except for DENY
> permissions, of course).
> --
> Todd
database. Based on what I have read, I should be able to not let anybody see
the tables directly, and work everything through Views and SPs. To do this,
I
grant no permissions at all on the tables, and appropriate SELECT, INSERT,
UPDATE, and DELETE permissions on the views. However, the views in Access ar
e
still coming up as "Recordset not updatabel". Only by granting permissions o
n
the tables do the views become updatable. Worse yet, if I DENY permissions
for UPDATE etc on the views but grant them on the tables, the views are stil
l
updatable.
This seems very backwards. I thought it was supposed to take the permissions
on the View, regarless of the permissions on the table (except for DENY
permissions, of course).
--
ToddYou can specify the VIEW_METADATA option on the CREATE VIEW statement so
that APIs return metadata for the view rather than the underlying tables.
For example:
CREATE VIEW dbo.MyView
WITH VIEW_METADATA
AS
SELECT MyColumn FROM dbo.MyTable
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Todd Chittenden" <ToddChittenden@.discussions.microsoft.com> wrote in
message news:187A1C5F-5CA1-4FA2-825C-859FFCF0FC20@.microsoft.com...
>I am using an Access ADP (version 2003) as a front-end to a SQL Server 2000
> database. Based on what I have read, I should be able to not let anybody
> see
> the tables directly, and work everything through Views and SPs. To do
> this, I
> grant no permissions at all on the tables, and appropriate SELECT, INSERT,
> UPDATE, and DELETE permissions on the views. However, the views in Access
> are
> still coming up as "Recordset not updatabel". Only by granting permissions
> on
> the tables do the views become updatable. Worse yet, if I DENY permissions
> for UPDATE etc on the views but grant them on the tables, the views are
> still
> updatable.
> This seems very backwards. I thought it was supposed to take the
> permissions
> on the View, regarless of the permissions on the table (except for DENY
> permissions, of course).
> --
> Todd
Permissions on a database
We are setting up a database for an outside user to access. We only want
them to have access to this database and nothing else.
At the moment, we want them to have full access to the database (create
tables, delete tables, look at data, update data, create and modify stored
procedures etc.) and any objects they create. They are building an Web
Application for us and we don't want to restrict them while they are
building it.
But we don't want them granting access or revoking access to anyone
(especially not me - I don't think they can since I am the sysadmin). I
later want to be able to restrict their access and limit access to sensitive
information in the database.
I thought about initially setting them up with db_owner access.
Will this prevent me from restricting them later?
If they create the tables, will they still have full access to the tables
(since they were db_owner when they created them)?
Thanks,
Tom.
I would suggest giving them a Windows Authenticated Account and then mapping
that account as the dbo_owner in the database you specify.
If they are good, they will create their objects with the dbo.ObjectName
prefix. If they are not good, then you will end up with objects that look
like Rick.Table1.
You could do and sp_addalias and mark them as dbo in the database.
This should do what you want it to. By using the Windows account, you also
get the benefits of restricting everything else that they might wish to do
on your server as well as evaluate log in times, log outs, minimum password
lengths and so forth.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
>I would suggest giving them a Windows Authenticated Account and then
>mapping
> that account as the dbo_owner in the database you specify.
>
But then could I down grade them later, or would they always have dbo access
to objects they created?
> If they are good, they will create their objects with the dbo.ObjectName
> prefix. If they are not good, then you will end up with objects that
> look
> like Rick.Table1.
Would this be a problem for me down the line?
> You could do and sp_addalias and mark them as dbo in the database.
What does that do?
> This should do what you want it to. By using the Windows account, you
> also
> get the benefits of restricting everything else that they might wish to do
> on your server as well as evaluate log in times, log outs, minimum
> password
> lengths and so forth.
I didn't want to give them access to the Windows server at all. That was
why I was having them do Sql Server Authentication. Do I also have to give
them Windows Access also? If that is the case, I why would anyone use the
Sql Server Authentication anyway?
I am just trying to figure out the best way to do this.
Thanks,
Tom.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Can't you let them develop with full access now... and once they are done..
you can change the pwd for that login. That way they will not have complete
access. And for their limited acesss, create a new read only login for their
future use.
Thanks
GYK
"tshad" wrote:
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
> But then could I down grade them later, or would they always have dbo access
> to objects they created?
>
> Would this be a problem for me down the line?
>
> What does that do?
>
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
> I am just trying to figure out the best way to do this.
> Thanks,
> Tom.
>
>
|||
> Would this be a problem for me down the line?
Yes! dbo should own all of the objects in the database. Period.
> What does that do?
It maps them into the database as a dbo rather than as their UserName who is
a member of the db_owners role.
If they are mapped as the dbo, they can do things like: CREATE TABLE Frog
This will make it dbo.Frog by default.
If they are mapped in as a User who is a member of db_owners role, then the
same CREATE TABLE Frog would come back as
UserName.Frog rather than dbo.Frog.
[vbcol=seagreen]
do
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to
give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
If you don't give them access to the Windows computer how do you expect them
to connect to the SQL Server? The SQL Authentication is used to allow SQL
Server to authenticate access to SQL Server rather than Windows
authenticating access to SQL Server.
> I am just trying to figure out the best way to do this.
> Thanks,
=)
Rick
> Tom.
>
them to have access to this database and nothing else.
At the moment, we want them to have full access to the database (create
tables, delete tables, look at data, update data, create and modify stored
procedures etc.) and any objects they create. They are building an Web
Application for us and we don't want to restrict them while they are
building it.
But we don't want them granting access or revoking access to anyone
(especially not me - I don't think they can since I am the sysadmin). I
later want to be able to restrict their access and limit access to sensitive
information in the database.
I thought about initially setting them up with db_owner access.
Will this prevent me from restricting them later?
If they create the tables, will they still have full access to the tables
(since they were db_owner when they created them)?
Thanks,
Tom.
I would suggest giving them a Windows Authenticated Account and then mapping
that account as the dbo_owner in the database you specify.
If they are good, they will create their objects with the dbo.ObjectName
prefix. If they are not good, then you will end up with objects that look
like Rick.Table1.
You could do and sp_addalias and mark them as dbo in the database.
This should do what you want it to. By using the Windows account, you also
get the benefits of restricting everything else that they might wish to do
on your server as well as evaluate log in times, log outs, minimum password
lengths and so forth.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
>I would suggest giving them a Windows Authenticated Account and then
>mapping
> that account as the dbo_owner in the database you specify.
>
But then could I down grade them later, or would they always have dbo access
to objects they created?
> If they are good, they will create their objects with the dbo.ObjectName
> prefix. If they are not good, then you will end up with objects that
> look
> like Rick.Table1.
Would this be a problem for me down the line?
> You could do and sp_addalias and mark them as dbo in the database.
What does that do?
> This should do what you want it to. By using the Windows account, you
> also
> get the benefits of restricting everything else that they might wish to do
> on your server as well as evaluate log in times, log outs, minimum
> password
> lengths and so forth.
I didn't want to give them access to the Windows server at all. That was
why I was having them do Sql Server Authentication. Do I also have to give
them Windows Access also? If that is the case, I why would anyone use the
Sql Server Authentication anyway?
I am just trying to figure out the best way to do this.
Thanks,
Tom.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Can't you let them develop with full access now... and once they are done..
you can change the pwd for that login. That way they will not have complete
access. And for their limited acesss, create a new read only login for their
future use.
Thanks
GYK
"tshad" wrote:
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
> But then could I down grade them later, or would they always have dbo access
> to objects they created?
>
> Would this be a problem for me down the line?
>
> What does that do?
>
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
> I am just trying to figure out the best way to do this.
> Thanks,
> Tom.
>
>
|||
> Would this be a problem for me down the line?
Yes! dbo should own all of the objects in the database. Period.
> What does that do?
It maps them into the database as a dbo rather than as their UserName who is
a member of the db_owners role.
If they are mapped as the dbo, they can do things like: CREATE TABLE Frog
This will make it dbo.Frog by default.
If they are mapped in as a User who is a member of db_owners role, then the
same CREATE TABLE Frog would come back as
UserName.Frog rather than dbo.Frog.
[vbcol=seagreen]
do
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to
give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
If you don't give them access to the Windows computer how do you expect them
to connect to the SQL Server? The SQL Authentication is used to allow SQL
Server to authenticate access to SQL Server rather than Windows
authenticating access to SQL Server.
> I am just trying to figure out the best way to do this.
> Thanks,
=)
Rick
> Tom.
>
Permissions on a database
We are setting up a database for an outside user to access. We only want
them to have access to this database and nothing else.
At the moment, we want them to have full access to the database (create
tables, delete tables, look at data, update data, create and modify stored
procedures etc.) and any objects they create. They are building an Web
Application for us and we don't want to restrict them while they are
building it.
But we don't want them granting access or revoking access to anyone
(especially not me - I don't think they can since I am the sysadmin). I
later want to be able to restrict their access and limit access to sensitive
information in the database.
I thought about initially setting them up with db_owner access.
Will this prevent me from restricting them later?
If they create the tables, will they still have full access to the tables
(since they were db_owner when they created them)?
Thanks,
Tom.I would suggest giving them a Windows Authenticated Account and then mapping
that account as the dbo_owner in the database you specify.
If they are good, they will create their objects with the dbo.ObjectName
prefix. If they are not good, then you will end up with objects that look
like Rick.Table1.
You could do and sp_addalias and mark them as dbo in the database.
This should do what you want it to. By using the Windows account, you also
get the benefits of restricting everything else that they might wish to do
on your server as well as evaluate log in times, log outs, minimum password
lengths and so forth.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
>I would suggest giving them a Windows Authenticated Account and then
>mapping
> that account as the dbo_owner in the database you specify.
>
But then could I down grade them later, or would they always have dbo access
to objects they created?
> If they are good, they will create their objects with the dbo.ObjectName
> prefix. If they are not good, then you will end up with objects that
> look
> like Rick.Table1.
Would this be a problem for me down the line?
> You could do and sp_addalias and mark them as dbo in the database.
What does that do?
> This should do what you want it to. By using the Windows account, you
> also
> get the benefits of restricting everything else that they might wish to do
> on your server as well as evaluate log in times, log outs, minimum
> password
> lengths and so forth.
I didn't want to give them access to the Windows server at all. That was
why I was having them do Sql Server Authentication. Do I also have to give
them Windows Access also? If that is the case, I why would anyone use the
Sql Server Authentication anyway?
I am just trying to figure out the best way to do this.
Thanks,
Tom.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Can't you let them develop with full access now... and once they are done..
you can change the pwd for that login. That way they will not have complete
access. And for their limited acesss, create a new read only login for their
future use.
Thanks
GYK
"tshad" wrote:
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
> But then could I down grade them later, or would they always have dbo acce
ss
> to objects they created?
>
> Would this be a problem for me down the line?
>
> What does that do?
>
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to giv
e
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
> I am just trying to figure out the best way to do this.
> Thanks,
> Tom.
>
>|||
> Would this be a problem for me down the line?
Yes! dbo should own all of the objects in the database. Period.
> What does that do?
It maps them into the database as a dbo rather than as their UserName who is
a member of the db_owners role.
If they are mapped as the dbo, they can do things like: CREATE TABLE Frog
This will make it dbo.Frog by default.
If they are mapped in as a User who is a member of db_owners role, then the
same CREATE TABLE Frog would come back as
UserName.Frog rather than dbo.Frog.
do[vbcol=seagreen]
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to
give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
If you don't give them access to the Windows computer how do you expect them
to connect to the SQL Server? The SQL Authentication is used to allow SQL
Server to authenticate access to SQL Server rather than Windows
authenticating access to SQL Server.
> I am just trying to figure out the best way to do this.
> Thanks,
=)
Rick
> Tom.
>
them to have access to this database and nothing else.
At the moment, we want them to have full access to the database (create
tables, delete tables, look at data, update data, create and modify stored
procedures etc.) and any objects they create. They are building an Web
Application for us and we don't want to restrict them while they are
building it.
But we don't want them granting access or revoking access to anyone
(especially not me - I don't think they can since I am the sysadmin). I
later want to be able to restrict their access and limit access to sensitive
information in the database.
I thought about initially setting them up with db_owner access.
Will this prevent me from restricting them later?
If they create the tables, will they still have full access to the tables
(since they were db_owner when they created them)?
Thanks,
Tom.I would suggest giving them a Windows Authenticated Account and then mapping
that account as the dbo_owner in the database you specify.
If they are good, they will create their objects with the dbo.ObjectName
prefix. If they are not good, then you will end up with objects that look
like Rick.Table1.
You could do and sp_addalias and mark them as dbo in the database.
This should do what you want it to. By using the Windows account, you also
get the benefits of restricting everything else that they might wish to do
on your server as well as evaluate log in times, log outs, minimum password
lengths and so forth.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
>I would suggest giving them a Windows Authenticated Account and then
>mapping
> that account as the dbo_owner in the database you specify.
>
But then could I down grade them later, or would they always have dbo access
to objects they created?
> If they are good, they will create their objects with the dbo.ObjectName
> prefix. If they are not good, then you will end up with objects that
> look
> like Rick.Table1.
Would this be a problem for me down the line?
> You could do and sp_addalias and mark them as dbo in the database.
What does that do?
> This should do what you want it to. By using the Windows account, you
> also
> get the benefits of restricting everything else that they might wish to do
> on your server as well as evaluate log in times, log outs, minimum
> password
> lengths and so forth.
I didn't want to give them access to the Windows server at all. That was
why I was having them do Sql Server Authentication. Do I also have to give
them Windows Access also? If that is the case, I why would anyone use the
Sql Server Authentication anyway?
I am just trying to figure out the best way to do this.
Thanks,
Tom.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Can't you let them develop with full access now... and once they are done..
you can change the pwd for that login. That way they will not have complete
access. And for their limited acesss, create a new read only login for their
future use.
Thanks
GYK
"tshad" wrote:
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
> But then could I down grade them later, or would they always have dbo acce
ss
> to objects they created?
>
> Would this be a problem for me down the line?
>
> What does that do?
>
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to giv
e
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
> I am just trying to figure out the best way to do this.
> Thanks,
> Tom.
>
>|||
> Would this be a problem for me down the line?
Yes! dbo should own all of the objects in the database. Period.
> What does that do?
It maps them into the database as a dbo rather than as their UserName who is
a member of the db_owners role.
If they are mapped as the dbo, they can do things like: CREATE TABLE Frog
This will make it dbo.Frog by default.
If they are mapped in as a User who is a member of db_owners role, then the
same CREATE TABLE Frog would come back as
UserName.Frog rather than dbo.Frog.
do[vbcol=seagreen]
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to
give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
If you don't give them access to the Windows computer how do you expect them
to connect to the SQL Server? The SQL Authentication is used to allow SQL
Server to authenticate access to SQL Server rather than Windows
authenticating access to SQL Server.
> I am just trying to figure out the best way to do this.
> Thanks,
=)
Rick
> Tom.
>
Permissions on a database
We are setting up a database for an outside user to access. We only want
them to have access to this database and nothing else.
At the moment, we want them to have full access to the database (create
tables, delete tables, look at data, update data, create and modify stored
procedures etc.) and any objects they create. They are building an Web
Application for us and we don't want to restrict them while they are
building it.
But we don't want them granting access or revoking access to anyone
(especially not me - I don't think they can since I am the sysadmin). I
later want to be able to restrict their access and limit access to sensitive
information in the database.
I thought about initially setting them up with db_owner access.
Will this prevent me from restricting them later?
If they create the tables, will they still have full access to the tables
(since they were db_owner when they created them)?
Thanks,
Tom.I would suggest giving them a Windows Authenticated Account and then mapping
that account as the dbo_owner in the database you specify.
If they are good, they will create their objects with the dbo.ObjectName
prefix. If they are not good, then you will end up with objects that look
like Rick.Table1.
You could do and sp_addalias and mark them as dbo in the database.
This should do what you want it to. By using the Windows account, you also
get the benefits of restricting everything else that they might wish to do
on your server as well as evaluate log in times, log outs, minimum password
lengths and so forth.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
>I would suggest giving them a Windows Authenticated Account and then
>mapping
> that account as the dbo_owner in the database you specify.
>
But then could I down grade them later, or would they always have dbo access
to objects they created?
> If they are good, they will create their objects with the dbo.ObjectName
> prefix. If they are not good, then you will end up with objects that
> look
> like Rick.Table1.
Would this be a problem for me down the line?
> You could do and sp_addalias and mark them as dbo in the database.
What does that do?
> This should do what you want it to. By using the Windows account, you
> also
> get the benefits of restricting everything else that they might wish to do
> on your server as well as evaluate log in times, log outs, minimum
> password
> lengths and so forth.
I didn't want to give them access to the Windows server at all. That was
why I was having them do Sql Server Authentication. Do I also have to give
them Windows Access also? If that is the case, I why would anyone use the
Sql Server Authentication anyway?
I am just trying to figure out the best way to do this.
Thanks,
Tom.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Can't you let them develop with full access now... and once they are done..
you can change the pwd for that login. That way they will not have complete
access. And for their limited acesss, create a new read only login for their
future use.
Thanks
GYK
"tshad" wrote:
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
> >I would suggest giving them a Windows Authenticated Account and then
> >mapping
> > that account as the dbo_owner in the database you specify.
> >
> But then could I down grade them later, or would they always have dbo access
> to objects they created?
> > If they are good, they will create their objects with the dbo.ObjectName
> > prefix. If they are not good, then you will end up with objects that
> > look
> > like Rick.Table1.
> Would this be a problem for me down the line?
> >
> > You could do and sp_addalias and mark them as dbo in the database.
> What does that do?
> >
> > This should do what you want it to. By using the Windows account, you
> > also
> > get the benefits of restricting everything else that they might wish to do
> > on your server as well as evaluate log in times, log outs, minimum
> > password
> > lengths and so forth.
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
> I am just trying to figure out the best way to do this.
> Thanks,
> Tom.
> >
> >
> > HTH
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>
>|||> > If they are good, they will create their objects with the dbo.ObjectName
> > prefix. If they are not good, then you will end up with objects that
> > look
> > like Rick.Table1.
> Would this be a problem for me down the line?
Yes! dbo should own all of the objects in the database. Period.
> > You could do and sp_addalias and mark them as dbo in the database.
> What does that do?
It maps them into the database as a dbo rather than as their UserName who is
a member of the db_owners role.
If they are mapped as the dbo, they can do things like: CREATE TABLE Frog
This will make it dbo.Frog by default.
If they are mapped in as a User who is a member of db_owners role, then the
same CREATE TABLE Frog would come back as
UserName.Frog rather than dbo.Frog.
> > This should do what you want it to. By using the Windows account, you
> > also
> > get the benefits of restricting everything else that they might wish to
do
> > on your server as well as evaluate log in times, log outs, minimum
> > password
> > lengths and so forth.
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to
give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
If you don't give them access to the Windows computer how do you expect them
to connect to the SQL Server? The SQL Authentication is used to allow SQL
Server to authenticate access to SQL Server rather than Windows
authenticating access to SQL Server.
> I am just trying to figure out the best way to do this.
> Thanks,
=)
Rick
> Tom.
> >
> >
> > HTH
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>
them to have access to this database and nothing else.
At the moment, we want them to have full access to the database (create
tables, delete tables, look at data, update data, create and modify stored
procedures etc.) and any objects they create. They are building an Web
Application for us and we don't want to restrict them while they are
building it.
But we don't want them granting access or revoking access to anyone
(especially not me - I don't think they can since I am the sysadmin). I
later want to be able to restrict their access and limit access to sensitive
information in the database.
I thought about initially setting them up with db_owner access.
Will this prevent me from restricting them later?
If they create the tables, will they still have full access to the tables
(since they were db_owner when they created them)?
Thanks,
Tom.I would suggest giving them a Windows Authenticated Account and then mapping
that account as the dbo_owner in the database you specify.
If they are good, they will create their objects with the dbo.ObjectName
prefix. If they are not good, then you will end up with objects that look
like Rick.Table1.
You could do and sp_addalias and mark them as dbo in the database.
This should do what you want it to. By using the Windows account, you also
get the benefits of restricting everything else that they might wish to do
on your server as well as evaluate log in times, log outs, minimum password
lengths and so forth.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
>I would suggest giving them a Windows Authenticated Account and then
>mapping
> that account as the dbo_owner in the database you specify.
>
But then could I down grade them later, or would they always have dbo access
to objects they created?
> If they are good, they will create their objects with the dbo.ObjectName
> prefix. If they are not good, then you will end up with objects that
> look
> like Rick.Table1.
Would this be a problem for me down the line?
> You could do and sp_addalias and mark them as dbo in the database.
What does that do?
> This should do what you want it to. By using the Windows account, you
> also
> get the benefits of restricting everything else that they might wish to do
> on your server as well as evaluate log in times, log outs, minimum
> password
> lengths and so forth.
I didn't want to give them access to the Windows server at all. That was
why I was having them do Sql Server Authentication. Do I also have to give
them Windows Access also? If that is the case, I why would anyone use the
Sql Server Authentication anyway?
I am just trying to figure out the best way to do this.
Thanks,
Tom.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Can't you let them develop with full access now... and once they are done..
you can change the pwd for that login. That way they will not have complete
access. And for their limited acesss, create a new read only login for their
future use.
Thanks
GYK
"tshad" wrote:
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
> >I would suggest giving them a Windows Authenticated Account and then
> >mapping
> > that account as the dbo_owner in the database you specify.
> >
> But then could I down grade them later, or would they always have dbo access
> to objects they created?
> > If they are good, they will create their objects with the dbo.ObjectName
> > prefix. If they are not good, then you will end up with objects that
> > look
> > like Rick.Table1.
> Would this be a problem for me down the line?
> >
> > You could do and sp_addalias and mark them as dbo in the database.
> What does that do?
> >
> > This should do what you want it to. By using the Windows account, you
> > also
> > get the benefits of restricting everything else that they might wish to do
> > on your server as well as evaluate log in times, log outs, minimum
> > password
> > lengths and so forth.
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
> I am just trying to figure out the best way to do this.
> Thanks,
> Tom.
> >
> >
> > HTH
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>
>|||> > If they are good, they will create their objects with the dbo.ObjectName
> > prefix. If they are not good, then you will end up with objects that
> > look
> > like Rick.Table1.
> Would this be a problem for me down the line?
Yes! dbo should own all of the objects in the database. Period.
> > You could do and sp_addalias and mark them as dbo in the database.
> What does that do?
It maps them into the database as a dbo rather than as their UserName who is
a member of the db_owners role.
If they are mapped as the dbo, they can do things like: CREATE TABLE Frog
This will make it dbo.Frog by default.
If they are mapped in as a User who is a member of db_owners role, then the
same CREATE TABLE Frog would come back as
UserName.Frog rather than dbo.Frog.
> > This should do what you want it to. By using the Windows account, you
> > also
> > get the benefits of restricting everything else that they might wish to
do
> > on your server as well as evaluate log in times, log outs, minimum
> > password
> > lengths and so forth.
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to
give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
If you don't give them access to the Windows computer how do you expect them
to connect to the SQL Server? The SQL Authentication is used to allow SQL
Server to authenticate access to SQL Server rather than Windows
authenticating access to SQL Server.
> I am just trying to figure out the best way to do this.
> Thanks,
=)
Rick
> Tom.
> >
> >
> > HTH
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>
Permissions not enforced
I have a pc set up with a System DSN to access my Sql Server. It is set up t
o
use NT Authentication according to the Login Name.
I have discovered that the User has full permissions to Select, Insert,
Update and delete records in any table. In Enterprise Manager this User is
configured to have just Select permissions on a few tables. Some of my table
s
are set up with Update and Delete permissions revoked for all Users and
Roles. So it appears to me that the DSN is not using NT Authentication and
that the User is being authenticated with sa permissions.
Where am I going wrong?You can query master..sysprocesses to see how the user is
connecting, what login is used. If the user is connecting
using Windows authentication then the user is likely
inheriting permissions through membership in a group.
-Sue
On Tue, 28 Jun 2005 01:30:02 -0700, Hoof Hearted
<HoofHearted@.discussions.microsoft.com> wrote:
>I have a pc set up with a System DSN to access my Sql Server. It is set up
to
>use NT Authentication according to the Login Name.
>I have discovered that the User has full permissions to Select, Insert,
>Update and delete records in any table. In Enterprise Manager this User is
>configured to have just Select permissions on a few tables. Some of my tabl
es
>are set up with Update and Delete permissions revoked for all Users and
>Roles. So it appears to me that the DSN is not using NT Authentication and
>that the User is being authenticated with sa permissions.
>Where am I going wrong?|||Yes it turns out the account I was testing it with, is a member of the NT
administrators group. But I had explicity denied him certain permissions
within the database. So I am surprised that his membership of the
Administrators group overrides this.|||By default, the builtin\administrators group is added to SQL
Server as sysadmins. Members of sysadmins can do whatever
and bypass any denies.
-Sue
On Tue, 28 Jun 2005 13:42:02 -0700, Hoof Hearted
<HoofHearted@.discussions.microsoft.com> wrote:
>Yes it turns out the account I was testing it with, is a member of the NT
>administrators group. But I had explicity denied him certain permissions
>within the database. So I am surprised that his membership of the
>Administrators group overrides this.
o
use NT Authentication according to the Login Name.
I have discovered that the User has full permissions to Select, Insert,
Update and delete records in any table. In Enterprise Manager this User is
configured to have just Select permissions on a few tables. Some of my table
s
are set up with Update and Delete permissions revoked for all Users and
Roles. So it appears to me that the DSN is not using NT Authentication and
that the User is being authenticated with sa permissions.
Where am I going wrong?You can query master..sysprocesses to see how the user is
connecting, what login is used. If the user is connecting
using Windows authentication then the user is likely
inheriting permissions through membership in a group.
-Sue
On Tue, 28 Jun 2005 01:30:02 -0700, Hoof Hearted
<HoofHearted@.discussions.microsoft.com> wrote:
>I have a pc set up with a System DSN to access my Sql Server. It is set up
to
>use NT Authentication according to the Login Name.
>I have discovered that the User has full permissions to Select, Insert,
>Update and delete records in any table. In Enterprise Manager this User is
>configured to have just Select permissions on a few tables. Some of my tabl
es
>are set up with Update and Delete permissions revoked for all Users and
>Roles. So it appears to me that the DSN is not using NT Authentication and
>that the User is being authenticated with sa permissions.
>Where am I going wrong?|||Yes it turns out the account I was testing it with, is a member of the NT
administrators group. But I had explicity denied him certain permissions
within the database. So I am surprised that his membership of the
Administrators group overrides this.|||By default, the builtin\administrators group is added to SQL
Server as sysadmins. Members of sysadmins can do whatever
and bypass any denies.
-Sue
On Tue, 28 Jun 2005 13:42:02 -0700, Hoof Hearted
<HoofHearted@.discussions.microsoft.com> wrote:
>Yes it turns out the account I was testing it with, is a member of the NT
>administrators group. But I had explicity denied him certain permissions
>within the database. So I am surprised that his membership of the
>Administrators group overrides this.
Subscribe to:
Posts (Atom)