We have an audit issue that is requiring me to revoke the select permissions from the public role to the system tables. Has anyone had to do this?? What problems did you experience? Are their any tables that you were not able to change the permissions on? Any help is greatly appreciated...when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...
...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)
...as to what affect revoking system table access from the public role will have, you would first have to answer the above questions...
...also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?|||..when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...
--I mean all. From the master, msdb, and user databases. My concerns are mostly with the master and msdb databases.
...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)
--We use several types of applications. Everything from web pages( We are a web company), to PeopleSoft, Siebel, and Access.
also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?
-- I am not sure, but I believe that they will. This is related to the new Sarbanes-Oxley act.|||...we are working on Sarbanes as well...but there has been nothing to compell us to look at this type of restriction...
...I am not sure you can restrict this access and still have application functionality...
...if this is still something you would like to pursue, I would start at the database level, create a test database and userid's and a method of access.
First you would have to test to ensure the access method works for the objects created in the database. Once that was proven, I would try the revoke against the databases system tables. To obtain a list of system tables:
select name from sysobjects where type = 'S'
you can then use the "sp_helprotect" stored procedure to obtain the current security settings.
then use the REVOKE command to revoke public access from these objects...
...then the access methid would have to be tested again...
now as far as the system databases goes, i'm sure that revoking public access would allow funtionality to remain...but you'd have to follow a similiar test...good luck...|||Why on earth would SOX be interested in the system tables? Are you storing SOX-sensitive data in the system tables? Using system tables for storing user data is a really bad idea!
-PatP|||...we discussed SOX at lunch and agree that everyone seems to be going overboard in the name of SOX...including auditors who are just guessing at how to comply with the new expectations...(not that they ever knew!)...
Showing posts with label revoke. Show all posts
Showing posts with label revoke. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Revoking permissions from dbo
Hi,
Is there any possible way to revoke a permission in a database from someone
who is a database owner? I don't think there is, but I'm making a last ditch
attempt to find out (this is SQL Server 2005).
The reason is that we want to use the "VIEW ALL DATABASES" permission to
stop other users viewing databases that they don't own, but the only way to
do this is to make the user who is allowed to see the database the owner. Bu
t
by doing this, they get access to do things like backup databases etc which
is a security risk (this is a shared server).
All my research points to the fact that we can't stop them from backing up
databases etc if thye are the owner, and since we can't allow that, it means
"VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
permission would be especially useful for if it was implemented with care).
We really want to hide all databases from other users, but we can't do it if
it means opening large holes in our security.
If anyone has any ideas before I give up, they would be appreciated!I don't have 2005 on this laptop. I believe though that you can deny view
definition on all but the databases a particular person owns. They can stil
l
see theirs, but they won't be able to see others. Isn't that, in effect, th
e
exact thing you are trying to achieve?
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Paul Woodland" wrote:
> Hi,
> Is there any possible way to revoke a permission in a database from someon
e
> who is a database owner? I don't think there is, but I'm making a last dit
ch
> attempt to find out (this is SQL Server 2005).
> The reason is that we want to use the "VIEW ALL DATABASES" permission to
> stop other users viewing databases that they don't own, but the only way t
o
> do this is to make the user who is allowed to see the database the owner.
But
> by doing this, they get access to do things like backup databases etc whic
h
> is a security risk (this is a shared server).
> All my research points to the fact that we can't stop them from backing up
> databases etc if thye are the owner, and since we can't allow that, it mea
ns
> "VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
> permission would be especially useful for if it was implemented with care)
.
> We really want to hide all databases from other users, but we can't do it
if
> it means opening large holes in our security.
> If anyone has any ideas before I give up, they would be appreciated!
>|||Hi,
Thank you for your reply. Although doing that hides the databases fine
(thats not the problem), it's the security holes it opens up which I'm
worried about. As an example, if we set our customers as database owners the
n
they can use "backup database" whenever they wish to backup to any location
on the servers hard disk that the user SQL Server is running as has access t
o
(which is a limited number of places because it's running as a limited user,
but there are still places - such as where we store our own automated
backups). Being a shared hosting environment rather than a corporate
environment, we can't "trust" our users not to take advantage of this (for
example, they could overwrite our own backups).
The way I can think of to solve this is to somehow revoke the "backup
database" permission (and other permissions you don't want them to have)
from the user, but it won't let you revoke permissions from dbo. Which is wh
y
I'm stuck.
Any ideas?
"MeanOldDBA" wrote:
[vbcol=seagreen]
> I don't have 2005 on this laptop. I believe though that you can deny view
> definition on all but the databases a particular person owns. They can st
ill
> see theirs, but they won't be able to see others. Isn't that, in effect,
the
> exact thing you are trying to achieve?
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Paul Woodland" wrote:
>
Is there any possible way to revoke a permission in a database from someone
who is a database owner? I don't think there is, but I'm making a last ditch
attempt to find out (this is SQL Server 2005).
The reason is that we want to use the "VIEW ALL DATABASES" permission to
stop other users viewing databases that they don't own, but the only way to
do this is to make the user who is allowed to see the database the owner. Bu
t
by doing this, they get access to do things like backup databases etc which
is a security risk (this is a shared server).
All my research points to the fact that we can't stop them from backing up
databases etc if thye are the owner, and since we can't allow that, it means
"VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
permission would be especially useful for if it was implemented with care).
We really want to hide all databases from other users, but we can't do it if
it means opening large holes in our security.
If anyone has any ideas before I give up, they would be appreciated!I don't have 2005 on this laptop. I believe though that you can deny view
definition on all but the databases a particular person owns. They can stil
l
see theirs, but they won't be able to see others. Isn't that, in effect, th
e
exact thing you are trying to achieve?
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Paul Woodland" wrote:
> Hi,
> Is there any possible way to revoke a permission in a database from someon
e
> who is a database owner? I don't think there is, but I'm making a last dit
ch
> attempt to find out (this is SQL Server 2005).
> The reason is that we want to use the "VIEW ALL DATABASES" permission to
> stop other users viewing databases that they don't own, but the only way t
o
> do this is to make the user who is allowed to see the database the owner.
But
> by doing this, they get access to do things like backup databases etc whic
h
> is a security risk (this is a shared server).
> All my research points to the fact that we can't stop them from backing up
> databases etc if thye are the owner, and since we can't allow that, it mea
ns
> "VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
> permission would be especially useful for if it was implemented with care)
.
> We really want to hide all databases from other users, but we can't do it
if
> it means opening large holes in our security.
> If anyone has any ideas before I give up, they would be appreciated!
>|||Hi,
Thank you for your reply. Although doing that hides the databases fine
(thats not the problem), it's the security holes it opens up which I'm
worried about. As an example, if we set our customers as database owners the
n
they can use "backup database" whenever they wish to backup to any location
on the servers hard disk that the user SQL Server is running as has access t
o
(which is a limited number of places because it's running as a limited user,
but there are still places - such as where we store our own automated
backups). Being a shared hosting environment rather than a corporate
environment, we can't "trust" our users not to take advantage of this (for
example, they could overwrite our own backups).
The way I can think of to solve this is to somehow revoke the "backup
database" permission (and other permissions you don't want them to have)
from the user, but it won't let you revoke permissions from dbo. Which is wh
y
I'm stuck.
Any ideas?
"MeanOldDBA" wrote:
[vbcol=seagreen]
> I don't have 2005 on this laptop. I believe though that you can deny view
> definition on all but the databases a particular person owns. They can st
ill
> see theirs, but they won't be able to see others. Isn't that, in effect,
the
> exact thing you are trying to achieve?
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Paul Woodland" wrote:
>
Revoking permission to view SQL Stored procs
Hi
Is there a way which I can revoke a users ability to view SQL stored procs,
but still have the ability to execute?
I want to do this via T-SQL, and without just considering the 'WITH
ENCRYPTION' option
Thanks
hi Paul,
Paul Aspinall wrote:
> Hi
> Is there a way which I can revoke a users ability to view SQL stored
> procs, but still have the ability to execute?
> I want to do this via T-SQL, and without just considering the 'WITH
> ENCRYPTION' option
> Thanks
every database user is allowed to view the definition of stored procedures,
and you can not change this behaviour... unfortunately, as you already
pointed out, you can only consider the WITH ENCRYPTION option
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What about granting only EXECUTE permissions and not CREATE, ALTER or
DROP ?
|||hi,
bd wrote:
> What about granting only EXECUTE permissions and not CREATE, ALTER or
> DROP ?
every database user has implicit permission to script objects, so he/she
will be able to access the object definition and DDL
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Is there a way which I can revoke a users ability to view SQL stored procs,
but still have the ability to execute?
I want to do this via T-SQL, and without just considering the 'WITH
ENCRYPTION' option
Thanks
hi Paul,
Paul Aspinall wrote:
> Hi
> Is there a way which I can revoke a users ability to view SQL stored
> procs, but still have the ability to execute?
> I want to do this via T-SQL, and without just considering the 'WITH
> ENCRYPTION' option
> Thanks
every database user is allowed to view the definition of stored procedures,
and you can not change this behaviour... unfortunately, as you already
pointed out, you can only consider the WITH ENCRYPTION option
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What about granting only EXECUTE permissions and not CREATE, ALTER or
DROP ?
|||hi,
bd wrote:
> What about granting only EXECUTE permissions and not CREATE, ALTER or
> DROP ?
every database user has implicit permission to script objects, so he/she
will be able to access the object definition and DDL
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Revoke/deny SOX issue
Hi all,
Either revoke/deny doesnt work or I dont understand the concept correctly.
I need to separate the duties of SA and DBO for SOX
I am planning on creating 2 new roles in every database. Securityadmin and
DataAdmin.
In testing this I set myself up as a user of a test DB, as a member of
Public I can do everything in the DB as if I was SA.
I tried to deny all rights to my username, and to Public. After doing so I
was still able create insert, update, blah, blah...
I have tried all of the following:
DENY CREATE TABLE TO public
DENY SELECT, INSERT, UPDATE, DELETE
ON testrights
TO PUBLIC --username
REVOKE ALL ON testrights TO jfischer
I don't understand why I can still do everything on the server.
TIA,
JoeWhat does the following return
select is_srvrolemember('sysadmin')
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
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:F0EA0EB9-EF0D-460D-84D0-A1AF4E9F9307@.microsoft.com...
> Hi all,
> Either revoke/deny doesnt work or I dont understand the concept correctly.
> I need to separate the duties of SA and DBO for SOX
> I am planning on creating 2 new roles in every database. Securityadmin
> and
> DataAdmin.
> In testing this I set myself up as a user of a test DB, as a member of
> Public I can do everything in the DB as if I was SA.
> I tried to deny all rights to my username, and to Public. After doing so I
> was still able create insert, update, blah, blah...
> I have tried all of the following:
> DENY CREATE TABLE TO public
> DENY SELECT, INSERT, UPDATE, DELETE
> ON testrights
> TO PUBLIC --username
> REVOKE ALL ON testrights TO jfischer
> I don't understand why I can still do everything on the server.
> TIA,
> Joe|||Thank you!
The issue was I was part of the administrator group on the server. I didn't
realize the window account mattered. I thought SQL security was only
controled thru SQL.
Thanks again!
joe
Either revoke/deny doesnt work or I dont understand the concept correctly.
I need to separate the duties of SA and DBO for SOX
I am planning on creating 2 new roles in every database. Securityadmin and
DataAdmin.
In testing this I set myself up as a user of a test DB, as a member of
Public I can do everything in the DB as if I was SA.
I tried to deny all rights to my username, and to Public. After doing so I
was still able create insert, update, blah, blah...
I have tried all of the following:
DENY CREATE TABLE TO public
DENY SELECT, INSERT, UPDATE, DELETE
ON testrights
TO PUBLIC --username
REVOKE ALL ON testrights TO jfischer
I don't understand why I can still do everything on the server.
TIA,
JoeWhat does the following return
select is_srvrolemember('sysadmin')
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
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:F0EA0EB9-EF0D-460D-84D0-A1AF4E9F9307@.microsoft.com...
> Hi all,
> Either revoke/deny doesnt work or I dont understand the concept correctly.
> I need to separate the duties of SA and DBO for SOX
> I am planning on creating 2 new roles in every database. Securityadmin
> and
> DataAdmin.
> In testing this I set myself up as a user of a test DB, as a member of
> Public I can do everything in the DB as if I was SA.
> I tried to deny all rights to my username, and to Public. After doing so I
> was still able create insert, update, blah, blah...
> I have tried all of the following:
> DENY CREATE TABLE TO public
> DENY SELECT, INSERT, UPDATE, DELETE
> ON testrights
> TO PUBLIC --username
> REVOKE ALL ON testrights TO jfischer
> I don't understand why I can still do everything on the server.
> TIA,
> Joe|||Thank you!
The issue was I was part of the administrator group on the server. I didn't
realize the window account mattered. I thought SQL security was only
controled thru SQL.
Thanks again!
joe
Revoke View any database from public in MSSQL 2005
Hello Everyone,
I am running into an issue when I run the following query to revoke access
to all the SQL 2005 users from other DB.s
use master
Revoke View any database from public
Go
When the users logs in with SMSE he/she does not see the db under “databas
es”.
They can only access via QUERY.
Thanks,
-CarlosDoesn't it make sense, after all, you removed (REVOKE) the ability to see
(VIEW) any (that means ALL) database to everyone (PUBLIC).
Perhaps you had a different outcome in mind, and you would like some help.
But you're going to have to be more specific, It's a waste of time to try to
guess what you want as a final outcome.
Your question/request is not clear.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
> Hello Everyone,
> I am running into an issue when I run the following query to revoke access
> to all the SQL 2005 users from other DB.s
> use master
> Revoke View any database from public
> Go
> When the users logs in with SMSE he/she does not see the db under
> "databases".
> They can only access via QUERY.
> Thanks,
> -Carlos
>|||Arnie,
Sorry for not being so clear on this. I will try to explaing in a different
way.
We run a shared hosting enviroment and SQL 2005 out of the box let's users
view all the db's and logins on the server when using SMSE.
By revoking the view command the users should still be able to see their
DB's since their role to each is "DBO".
As I said on the post, they can run queriues against the db using SMSE but
the tool will not graphicaly show you the db as it shows Master and tempdb.
Perhaps this will also help you understand my issue a big better.
http://weblogs.asp.net/hosterposter.../17/443118.aspx
Thanks for the reply,
-Carlos
"Arnie Rowland" wrote:
> Doesn't it make sense, after all, you removed (REVOKE) the ability to see
> (VIEW) any (that means ALL) database to everyone (PUBLIC).
> Perhaps you had a different outcome in mind, and you would like some help.
> But you're going to have to be more specific, It's a waste of time to try
to
> guess what you want as a final outcome.
> Your question/request is not clear.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
> news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
>
>|||You mention they should see the databases "since their role
to each is "DBO".
The behavior will work as the article describes but I'm not
sure what you mean by their role to each is dbo. If you mean
adding the user to db_owner role, that won't do it. The user
needs to be mapped to dbo - as in be the owner of the
database. There is a difference.
Look at who the owner of the database is by executing
sp_helpdb 'YourDatabase'
or execute
sp_helpuser 'dbo'
-Sue
On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Arnie,
>Sorry for not being so clear on this. I will try to explaing in a differen
t
>way.
>We run a shared hosting enviroment and SQL 2005 out of the box let's users
>view all the db's and logins on the server when using SMSE.
>By revoking the view command the users should still be able to see their
>DB's since their role to each is "DBO".
>As I said on the post, they can run queriues against the db using SMSE but
>the tool will not graphicaly show you the db as it shows Master and tempdb.
>Perhaps this will also help you understand my issue a big better.
>http://weblogs.asp.net/hosterposter.../17/443118.aspx
>Thanks for the reply,
>-Carlos
>"Arnie Rowland" wrote:
>|||Sue,
Each user is the dbo to each database.
-Carlos
"Sue Hoegemeier" wrote:
> You mention they should see the databases "since their role
> to each is "DBO".
> The behavior will work as the article describes but I'm not
> sure what you mean by their role to each is dbo. If you mean
> adding the user to db_owner role, that won't do it. The user
> needs to be mapped to dbo - as in be the owner of the
> database. There is a difference.
> Look at who the owner of the database is by executing
> sp_helpdb 'YourDatabase'
> or execute
> sp_helpuser 'dbo'
> -Sue
> On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
> <CarlosCaneja@.discussions.microsoft.com> wrote:
>
>|||Lines: 1
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: c-24-8-112-204.hsd1.co.comcast.net 24.8.112.204
Xref: leafnode.mcse.ms microsoft.public.sqlserver.security:1263
DBO gets misunderstood sometimes so that's why I'm wondering
if that's not the issue. I just did a few tests and it works
fine on two instances I just tested in on. As long as the
logins were database owners, they could see the database.
Anything else, and they could not see the database.
If SomeUser is the login and user in question, the results
for sp_helpuser 'dbo' in that database are:
UserName: dbo
GroupName: db_owner
LoginName: SomeUser
The results for sp_helpdb 'YourDatabase list the owner as
SomeUser.
If that's your results, try changing the database owner
using sp_changedbowner and try changing it back to the user
you need to have own the database.
-Sue
On Wed, 27 Sep 2006 11:01:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Each user is the dbo to each database.
>-Carlos
>"Sue Hoegemeier" wrote:
>
I am running into an issue when I run the following query to revoke access
to all the SQL 2005 users from other DB.s
use master
Revoke View any database from public
Go
When the users logs in with SMSE he/she does not see the db under “databas
es”.
They can only access via QUERY.
Thanks,
-CarlosDoesn't it make sense, after all, you removed (REVOKE) the ability to see
(VIEW) any (that means ALL) database to everyone (PUBLIC).
Perhaps you had a different outcome in mind, and you would like some help.
But you're going to have to be more specific, It's a waste of time to try to
guess what you want as a final outcome.
Your question/request is not clear.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
> Hello Everyone,
> I am running into an issue when I run the following query to revoke access
> to all the SQL 2005 users from other DB.s
> use master
> Revoke View any database from public
> Go
> When the users logs in with SMSE he/she does not see the db under
> "databases".
> They can only access via QUERY.
> Thanks,
> -Carlos
>|||Arnie,
Sorry for not being so clear on this. I will try to explaing in a different
way.
We run a shared hosting enviroment and SQL 2005 out of the box let's users
view all the db's and logins on the server when using SMSE.
By revoking the view command the users should still be able to see their
DB's since their role to each is "DBO".
As I said on the post, they can run queriues against the db using SMSE but
the tool will not graphicaly show you the db as it shows Master and tempdb.
Perhaps this will also help you understand my issue a big better.
http://weblogs.asp.net/hosterposter.../17/443118.aspx
Thanks for the reply,
-Carlos
"Arnie Rowland" wrote:
> Doesn't it make sense, after all, you removed (REVOKE) the ability to see
> (VIEW) any (that means ALL) database to everyone (PUBLIC).
> Perhaps you had a different outcome in mind, and you would like some help.
> But you're going to have to be more specific, It's a waste of time to try
to
> guess what you want as a final outcome.
> Your question/request is not clear.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
> news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
>
>|||You mention they should see the databases "since their role
to each is "DBO".
The behavior will work as the article describes but I'm not
sure what you mean by their role to each is dbo. If you mean
adding the user to db_owner role, that won't do it. The user
needs to be mapped to dbo - as in be the owner of the
database. There is a difference.
Look at who the owner of the database is by executing
sp_helpdb 'YourDatabase'
or execute
sp_helpuser 'dbo'
-Sue
On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Arnie,
>Sorry for not being so clear on this. I will try to explaing in a differen
t
>way.
>We run a shared hosting enviroment and SQL 2005 out of the box let's users
>view all the db's and logins on the server when using SMSE.
>By revoking the view command the users should still be able to see their
>DB's since their role to each is "DBO".
>As I said on the post, they can run queriues against the db using SMSE but
>the tool will not graphicaly show you the db as it shows Master and tempdb.
>Perhaps this will also help you understand my issue a big better.
>http://weblogs.asp.net/hosterposter.../17/443118.aspx
>Thanks for the reply,
>-Carlos
>"Arnie Rowland" wrote:
>|||Sue,
Each user is the dbo to each database.
-Carlos
"Sue Hoegemeier" wrote:
> You mention they should see the databases "since their role
> to each is "DBO".
> The behavior will work as the article describes but I'm not
> sure what you mean by their role to each is dbo. If you mean
> adding the user to db_owner role, that won't do it. The user
> needs to be mapped to dbo - as in be the owner of the
> database. There is a difference.
> Look at who the owner of the database is by executing
> sp_helpdb 'YourDatabase'
> or execute
> sp_helpuser 'dbo'
> -Sue
> On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
> <CarlosCaneja@.discussions.microsoft.com> wrote:
>
>|||Lines: 1
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: c-24-8-112-204.hsd1.co.comcast.net 24.8.112.204
Xref: leafnode.mcse.ms microsoft.public.sqlserver.security:1263
DBO gets misunderstood sometimes so that's why I'm wondering
if that's not the issue. I just did a few tests and it works
fine on two instances I just tested in on. As long as the
logins were database owners, they could see the database.
Anything else, and they could not see the database.
If SomeUser is the login and user in question, the results
for sp_helpuser 'dbo' in that database are:
UserName: dbo
GroupName: db_owner
LoginName: SomeUser
The results for sp_helpdb 'YourDatabase list the owner as
SomeUser.
If that's your results, try changing the database owner
using sp_changedbowner and try changing it back to the user
you need to have own the database.
-Sue
On Wed, 27 Sep 2006 11:01:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Each user is the dbo to each database.
>-Carlos
>"Sue Hoegemeier" wrote:
>
revoke grant access for specific user
i want to ask how to revoke grant access to specific user, so the spesific user can give grant access to other user.
thank in advance
I'm not exactly sure what you're asking for here, but i'll take a guess:
Have a look at the fixed database roles db_securityadmin and db_accessadmin which manage permissions/db access in BOL.
Also check the GRANT and REVOKE statements which may help.
If i've misundertood your intentions, could you please elaborate?
HTH!
If you provided a user permissions to also GRANT permission on the object(s) using the WITH GRANT option, you can remove just the ability to grant permissions to others.
REVOKE GRANT OPTION ON OBJECT::ObjectName FROM User
Revoke execute permission from a login
Dear All,
I need to revoke execute permission from sp_configure (SP) from a user named(a) which do not exists in master database.
Regards
Mohd sufian
Why do you need/want to do this? Normal users can only use sp_configure to query the system settings; they cannot change the settings. See http://msdn2.microsoft.com/en-us/library/ms188787.aspx.
You cannot revoke the permission from a, because a is not granted the permission to EXECUTE sp_configure.
The permission to execute sp_configure is instead granted to the public role. You could revoke this one, but this is most likely not what you want, as you would then have to grant the permission explicitly to those you want to have it.
Instead, you can create the user a in the master database and explicitly DENY EXECUTE permission on sp_configure.
Thanks
Laurentiu
revoke delete permission from large no of tables
Hi,
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2
D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>
|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2
D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>
|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
revoke delete permission from large no of tables
Hi,
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
Revoke all permissions?
Is there a way to revoke all permissions? I want to setup a test database
with permissions that make more sense than the random ones that have been
placed on the database over the years (SQL 2000). There are too many
factors to know for sure the plan will cover all scenarios without a test.
Hoping... is there is a way to revoke all permissions and then re-add them
[via a script] so that the permission set is clean and based solely on the
security script being used on the test database?
Regards,
Jamie
Scott Morris answered this one for me in another post with a script to check
for orphaned users.
Thanks Scott.
set nocount on
go
if exists(select * from tempdb..sysobjects where id =
object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by name
declare @.lc_name sysname
SET @.lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @.lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @.lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @.lc_name
else
PRINT '*** not fixing ' + @.lc_name
SET @.lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@.lc_name )
END
go
Regards,
Jamie
"thejamie" wrote:
> Is there a way to revoke all permissions? I want to setup a test database
> with permissions that make more sense than the random ones that have been
> placed on the database over the years (SQL 2000). There are too many
> factors to know for sure the plan will cover all scenarios without a test.
> Hoping... is there is a way to revoke all permissions and then re-add them
> [via a script] so that the permission set is clean and based solely on the
> security script being used on the test database?
> --
> Regards,
> Jamie
with permissions that make more sense than the random ones that have been
placed on the database over the years (SQL 2000). There are too many
factors to know for sure the plan will cover all scenarios without a test.
Hoping... is there is a way to revoke all permissions and then re-add them
[via a script] so that the permission set is clean and based solely on the
security script being used on the test database?
Regards,
Jamie
Scott Morris answered this one for me in another post with a script to check
for orphaned users.
Thanks Scott.
set nocount on
go
if exists(select * from tempdb..sysobjects where id =
object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by name
declare @.lc_name sysname
SET @.lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @.lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @.lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @.lc_name
else
PRINT '*** not fixing ' + @.lc_name
SET @.lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@.lc_name )
END
go
Regards,
Jamie
"thejamie" wrote:
> Is there a way to revoke all permissions? I want to setup a test database
> with permissions that make more sense than the random ones that have been
> placed on the database over the years (SQL 2000). There are too many
> factors to know for sure the plan will cover all scenarios without a test.
> Hoping... is there is a way to revoke all permissions and then re-add them
> [via a script] so that the permission set is clean and based solely on the
> security script being used on the test database?
> --
> Regards,
> Jamie
Revoke all permissions?
Is there a way to revoke all permissions? I want to setup a test database
with permissions that make more sense than the random ones that have been
placed on the database over the years (SQL 2000). There are too many
factors to know for sure the plan will cover all scenarios without a test.
Hoping... is there is a way to revoke all permissions and then re-add them
[via a script] so that the permission set is clean and based solely on t
he
security script being used on the test database?
--
Regards,
JamieScott Morris answered this one for me in another post with a script to check
for orphaned users.
Thanks Scott.
--
set nocount on
go
if exists(select * from tempdb..sysobjects where id =
object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by
name
declare @.lc_name sysname
SET @.lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @.lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @.lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @.lc_name
else
PRINT '*** not fixing ' + @.lc_name
SET @.lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@.lc_name )
END
go
Regards,
Jamie
"thejamie" wrote:
> Is there a way to revoke all permissions? I want to setup a test database
> with permissions that make more sense than the random ones that have been
> placed on the database over the years (SQL 2000). There are too many
> factors to know for sure the plan will cover all scenarios without a test.
> Hoping... is there is a way to revoke all permissions and then re-add them
> [via a script] so that the permission set is clean and based solely on
the
> security script being used on the test database?
> --
> Regards,
> Jamie
with permissions that make more sense than the random ones that have been
placed on the database over the years (SQL 2000). There are too many
factors to know for sure the plan will cover all scenarios without a test.
Hoping... is there is a way to revoke all permissions and then re-add them
[via a script] so that the permission set is clean and based solely on t
he
security script being used on the test database?
--
Regards,
JamieScott Morris answered this one for me in another post with a script to check
for orphaned users.
Thanks Scott.
--
set nocount on
go
if exists(select * from tempdb..sysobjects where id =
object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by
name
declare @.lc_name sysname
SET @.lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @.lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @.lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @.lc_name
else
PRINT '*** not fixing ' + @.lc_name
SET @.lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@.lc_name )
END
go
Regards,
Jamie
"thejamie" wrote:
> Is there a way to revoke all permissions? I want to setup a test database
> with permissions that make more sense than the random ones that have been
> placed on the database over the years (SQL 2000). There are too many
> factors to know for sure the plan will cover all scenarios without a test.
> Hoping... is there is a way to revoke all permissions and then re-add them
> [via a script] so that the permission set is clean and based solely on
the
> security script being used on the test database?
> --
> Regards,
> Jamie
Revoke all permissions?
Is there a way to revoke all permissions? I want to setup a test database
with permissions that make more sense than the random ones that have been
placed on the database over the years (SQL 2000). There are too many
factors to know for sure the plan will cover all scenarios without a test.
Hoping... is there is a way to revoke all permissions and then re-add them
[via a script] so that the permission set is clean and based solely on the
security script being used on the test database?
--
Regards,
JamieScott Morris answered this one for me in another post with a script to check
for orphaned users.
Thanks Scott.
--
set nocount on
go
if exists(select * from tempdb..sysobjects where id =object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by name
declare @.lc_name sysname
SET @.lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @.lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @.lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @.lc_name
else
PRINT '*** not fixing ' + @.lc_name
SET @.lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@.lc_name )
END
go
Regards,
Jamie
"thejamie" wrote:
> Is there a way to revoke all permissions? I want to setup a test database
> with permissions that make more sense than the random ones that have been
> placed on the database over the years (SQL 2000). There are too many
> factors to know for sure the plan will cover all scenarios without a test.
> Hoping... is there is a way to revoke all permissions and then re-add them
> [via a script] so that the permission set is clean and based solely on the
> security script being used on the test database?
> --
> Regards,
> Jamie
with permissions that make more sense than the random ones that have been
placed on the database over the years (SQL 2000). There are too many
factors to know for sure the plan will cover all scenarios without a test.
Hoping... is there is a way to revoke all permissions and then re-add them
[via a script] so that the permission set is clean and based solely on the
security script being used on the test database?
--
Regards,
JamieScott Morris answered this one for me in another post with a script to check
for orphaned users.
Thanks Scott.
--
set nocount on
go
if exists(select * from tempdb..sysobjects where id =object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by name
declare @.lc_name sysname
SET @.lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @.lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @.lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @.lc_name
else
PRINT '*** not fixing ' + @.lc_name
SET @.lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@.lc_name )
END
go
Regards,
Jamie
"thejamie" wrote:
> Is there a way to revoke all permissions? I want to setup a test database
> with permissions that make more sense than the random ones that have been
> placed on the database over the years (SQL 2000). There are too many
> factors to know for sure the plan will cover all scenarios without a test.
> Hoping... is there is a way to revoke all permissions and then re-add them
> [via a script] so that the permission set is clean and based solely on the
> security script being used on the test database?
> --
> Regards,
> Jamie
revoke a role
Hi,
How can I revoke a role to a user.
I was trying something like this, but it doesn't make any sense to my the ObjectPermissionSet:
Dim Role As New ObjectPermissionSet
Role.Select = True
db_login.Revoke(Role, TextBox2.Text, False, False, "securityadmin")
Actually, the permission you're working with is a database role, not a permission in the strict sense. Try something like this:
Dim objDBRole As DatabaseRole
objDBRole = objDatabase.Roles("db_securityadmin")
objDBRole.DropMember(TextBox2.Text)
Hi,
Thanks, but how do I specify which is the database.
|||Hi,
Never mind is in objDatabase.Roles("db_securityadmin"). Thanks a lot.
Dim sRole As ServerRole
sRole = myServer.Roles("securityadmin")
sRole.DropMember(TextBox2.Text)
Dim dRole As DatabaseRole
dRole = sys_database.Roles("db_owner")
dRole.DropMember(TextBox2.Text)
Subscribe to:
Posts (Atom)