I’m trying to lock down an audit table in our database. As a test, I opene
d the table’s ‘manage permissions’ dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database
role membership and saw that she was a member of the db_owner role, so I rev
oked that. I then ran a DENY statement: "deny delete on Histories to edenr".
I removed her memberships in the db_accessadmin and db_securityadmin roles,
and had her close and reop
en Enterprise Manager. After all that, she was still able to delete records.
The manage permissions dialog for this table shows that she is denied delete
permissions. She is still a member of the public, db_datareader, and db_dat
awriter groups, but that shouldn’t override explicitly denied permissions.
I’m the dbo of the datab
ase, so I certainly should have sufficient rights to issue a denial.
What does it TAKE to block a programmer from having permission to delete rec
ords?Yes but what Login is Enterprise Manager using? It is probably not hers.
Andrew J. Kelly SQL MVP
"eachus" <eachus@.discussions.microsoft.com> wrote in message
news:A4C20AFD-E526-4EFD-BAE0-42125FE1641F@.microsoft.com...
> I'm trying to lock down an audit table in our database. As a test, I
opened the table's 'manage permissions' dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database role membership and saw that she was a member of
the db_owner role, so I revoked that. I then ran a DENY statement: "deny
delete on Histories to edenr". I removed her memberships in the
db_accessadmin and db_securityadmin roles, and had her close and reopen
Enterprise Manager. After all that, she was still able to delete records.
> The manage permissions dialog for this table shows that she is denied
delete permissions. She is still a member of the public, db_datareader, and
db_datawriter groups, but that shouldn't override explicitly denied
permissions. I'm the dbo of the database, so I certainly should have
sufficient rights to issue a denial.
> What does it TAKE to block a programmer from having permission to delete
records?
>|||Hi,
Check the role associated for the user first by executing below command:-
sp_helplogins <Login_name_for that _user'
If you have any roles apart from db_datareader and db_datawriter revoke
that.
After this Execute the below command
use <dbname>
go
deny delete on <table_name> to <user_name>
After that login to query analyzer using that user and run the command:-
select suser_sname()
Now execute the delete statatement on that table.
Thanks
Hari
MCDBA
"eachus" <eachus@.discussions.microsoft.com> wrote in message
news:A4C20AFD-E526-4EFD-BAE0-42125FE1641F@.microsoft.com...
> I'm trying to lock down an audit table in our database. As a test, I
opened the table's 'manage permissions' dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database role membership and saw that she was a member of
the db_owner role, so I revoked that. I then ran a DENY statement: "deny
delete on Histories to edenr". I removed her memberships in the
db_accessadmin and db_securityadmin roles, and had her close and reopen
Enterprise Manager. After all that, she was still able to delete records.
> The manage permissions dialog for this table shows that she is denied
delete permissions. She is still a member of the public, db_datareader, and
db_datawriter groups, but that shouldn't override explicitly denied
permissions. I'm the dbo of the database, so I certainly should have
sufficient rights to issue a denial.
> What does it TAKE to block a programmer from having permission to delete
records?
>|||Thanks for the suggestions. I tried this, and got the same result. It did ha
ve the effect of re-confirming that the deletions were being run under the p
ermissions of the user in question, which was useful.
The goal here is to be able to block anybody, including programming team mem
bers, from being able to delete records in the production database's audit t
able.
Got any other suggestions where she might be getting delete permissions that
override the explicit denial?
"Hari" wrote:
> Hi,
> Check the role associated for the user first by executing below command:-
> sp_helplogins <Login_name_for that _user'
> If you have any roles apart from db_datareader and db_datawriter revoke
> that.
> After this Execute the below command
> use <dbname>
> go
> deny delete on <table_name> to <user_name>
> After that login to query analyzer using that user and run the command:-
> select suser_sname()
> Now execute the delete statatement on that table.
>|||Check server roles as well. Maybe she is a member of
sysadmins either directly or through windows group
membership
-Sue
On Fri, 2 Jul 2004 09:07:02 -0700, Eachus
<Eachus@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the suggestions. I tried this, and got the same result. It did h
ave the effect of re-confirming that the deletions were being run under the
permissions of the user in question, which was useful.
>The goal here is to be able to block anybody, including programming team me
mbers, from being able to delete records in the production database's audit
table.
>Got any other suggestions where she might be getting delete permissions tha
t override the explicit denial?
>"Hari" wrote:
>|||Thanks--it looks like that was it. Most of our programmers, including the on
e I'm using as a test case, are members of the System Adminstrators role, an
d the System Adminstrators role has delete permissions on any object in any
database.
All domain admins are automatically members of the sysadmins role, so anyone
who is a domain admin can't be removed from the group even if I decided tha
t was the best solution.
It looks like permissions granted due to membership in the sysadmins role ca
n't be overridden by a denial? Is there any way to override these permission
s in a particular database?
"Sue Hoegemeier" wrote:
> Check server roles as well. Maybe she is a member of
> sysadmins either directly or through windows group
> membership|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.sqlserver.security
NNTP-Posting-Host: 0-1pool76-99.nas29.thornton1.co.us.da.qwest.net 67.4.76.9
9
Path: TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Lines: 1
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.security:21673
Good - glad to hear that helped you track it down.
On the sysadmins, someone who is a member of the role can do
everything. Members of this role bypass any denies you set
up for them. You can't override this on any level, not by
database or anything else. They can do whatever.
Regarding domain admins, they get their access through the
BUILTIN\Administrators group in SQL Server that is by
default a member of sysadmins. You can remove the
BUILTIN\Administrators but doing this can cause some
problems. Whether you get problems or not depends. The
following article has an more information section with links
to some issues that could come up:
INF: How to impede Windows NT administrators from
administering a clustered instance of SQL Server
http://support.microsoft.com/?id=263712
-Sue
On Tue, 6 Jul 2004 11:38:02 -0700, Eachus
<Eachus@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks--it looks like that was it. Most of our programmers, including the o
ne I'm using as a test case, are members of the System Adminstrators role, a
nd the System Adminstrators role has delete permissions on any object in any
database.
>All domain admins are automatically members of the sysadmins role, so anyon
e who is a domain admin can't be removed from the group even if I decided th
at was the best solution.
>It looks like permissions granted due to membership in the sysadmins role c
an't be overridden by a denial? Is there any way to override these permissio
ns in a particular database?
>"Sue Hoegemeier" wrote:
>
Showing posts with label denied. Show all posts
Showing posts with label denied. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
Cannot use the reserved user or role name db_datareader.
Hi, I have got a problem. When I try to access my database table Users, I get the following error:
SELECT permission denied on object 'Users', database 'Users', owner 'dbo'.
SoI tried to grand this select command in MS Web Data Administration, butit doesnt work. When I try to grand db_datareader role to dbo, I getthe following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the reserved user or role name 'db_datareader'.
Does someone have an idea where could be a problem?
You don't need to be granting db_datareader to dbo, you need to beputting the user that you're accessing the database with in that role,or at least giving it SELECT permissions on that table, or better yet,giving it EXECUTE permissions on a stored proc that selects from thattable.
SELECT permission denied on object 'Users', database 'Users', owner 'dbo'.
SoI tried to grand this select command in MS Web Data Administration, butit doesnt work. When I try to grand db_datareader role to dbo, I getthe following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the reserved user or role name 'db_datareader'.
Does someone have an idea where could be a problem?
You don't need to be granting db_datareader to dbo, you need to beputting the user that you're accessing the database with in that role,or at least giving it SELECT permissions on that table, or better yet,giving it EXECUTE permissions on a stored proc that selects from thattable.
Wednesday, March 7, 2012
Cannot Start SQL Server Agent
Whenever I attempt to start SQL Server Agent I get the following error message: "An error 5 - (Access is denied) occurred while performing this service operation on the SQLServerAgent service"
I log in to the SQL Server as 'sa'. What could be the possible problem?Originally posted by AlveenX
Whenever I attempt to start SQL Server Agent I get the following error message: "An error 5 - (Access is denied) occurred while performing this service operation on the SQLServerAgent service"
I log in to the SQL Server as 'sa'. What could be the possible problem?
What user is set to startup the SQLAgent service? This user must be added in SQLServer logins and give system administrator rights over the SQLServer.
ionut|||The easy way (i think), on Services, try to put the service starting with the "local system account"|||SQL Server uses a windows account to start the sql server agent service. This user should have preferably administrator rights on the machine where the sql server resides. This user can be a local user ( in that case "local system account" is best, as pal19 already mentioned) or, if the sql server has to communicate with other (sql) servers, a domain user account.
You can change the user account that is used in the enterprise manager. Go to management --> sql server agent --> right click and select properties --> general tab. You can also go to the services tab in the Windows administrative tools and change the properties of the sql server agent service there.|||Thanks Jora, Pal19 and Ionut Calin. SQL Server Agent has eventually started. Thanks so much for your help.
AlveenX
I log in to the SQL Server as 'sa'. What could be the possible problem?Originally posted by AlveenX
Whenever I attempt to start SQL Server Agent I get the following error message: "An error 5 - (Access is denied) occurred while performing this service operation on the SQLServerAgent service"
I log in to the SQL Server as 'sa'. What could be the possible problem?
What user is set to startup the SQLAgent service? This user must be added in SQLServer logins and give system administrator rights over the SQLServer.
ionut|||The easy way (i think), on Services, try to put the service starting with the "local system account"|||SQL Server uses a windows account to start the sql server agent service. This user should have preferably administrator rights on the machine where the sql server resides. This user can be a local user ( in that case "local system account" is best, as pal19 already mentioned) or, if the sql server has to communicate with other (sql) servers, a domain user account.
You can change the user account that is used in the enterprise manager. Go to management --> sql server agent --> right click and select properties --> general tab. You can also go to the services tab in the Windows administrative tools and change the properties of the sql server agent service there.|||Thanks Jora, Pal19 and Ionut Calin. SQL Server Agent has eventually started. Thanks so much for your help.
AlveenX
Subscribe to:
Posts (Atom)