Can not drop a member from a role

I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
AvniCheck out sp_change_users_login. Run it to find the mismatched users, as
well as to correct the problem. Then, drop the role member.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<avni.cengel@.gmail.com> wrote in message
news:1141984205.157637.62570@.u72g2000cwu.googlegroups.com...
I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
Avni

Can not drop a member from a role

I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
AvniCheck out sp_change_users_login. Run it to find the mismatched users, as
well as to correct the problem. Then, drop the role member.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<avni.cengel@.gmail.com> wrote in message
news:1141984205.157637.62570@.u72g2000cwu.googlegroups.com...
I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
Avni

Can not drop a member from a role

I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
Avni
Check out sp_change_users_login. Run it to find the mismatched users, as
well as to correct the problem. Then, drop the role member.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<avni.cengel@.gmail.com> wrote in message
news:1141984205.157637.62570@.u72g2000cwu.googlegro ups.com...
I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
Avni
sql

Can I restrict members of db_datareader from SELECTing from a tabl

I have a large database with lost of users, many of whom are in the
db_datareader role.
I have a table that I want to restrict SELECTs on to just sa's.
How can I restrict members of db_datareader from SELECTing from this table?
As shown below, I cannot REVOKE or DENY SELECT from this role.
I could remove everyone from db_datareader and put them in a special role
with SELECT on everything but this table. But is there an easier way?
Thanks
USE admin
CREATE TABLE myTable
(col1 int PRIMARY KEY IDENTITY
,col2 int
,col3 int)
INSERT myTable (col2) VALUES (10)
INSERT myTable (col2) VALUES (20)
INSERT myTable (col2) VALUES (30)
SELECT * FROM myTable
execute as login = 'appuser'
select suser_name()
SELECT * FROM myTable
revert
sp_helprolemember db_datareader
REVOKE SELECT ON myTable TO db_datareader
--Cannot grant, deny or revoke permissions to or from special roles.
DENY SELECT ON myTable TO db_datareader
Hello,
You can not attempt to issue a DENY or REVOKE on to a fixed role.Issue the
DENY SELECT statement on to all uses who are not supposed to
query the table and see if that works well.
THanks
Hari
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:B4980135-A8AE-4D4B-8B11-2623407D8986@.microsoft.com...
>I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
> How can I restrict members of db_datareader from SELECTing from this
> table?
> As shown below, I cannot REVOKE or DENY SELECT from this role.
> I could remove everyone from db_datareader and put them in a special role
> with SELECT on everything but this table. But is there an easier way?
> Thanks
> --
> USE admin
> CREATE TABLE myTable
> (col1 int PRIMARY KEY IDENTITY
> ,col2 int
> ,col3 int)
> INSERT myTable (col2) VALUES (10)
> INSERT myTable (col2) VALUES (20)
> INSERT myTable (col2) VALUES (30)
> SELECT * FROM myTable
> execute as login = 'appuser'
> select suser_name()
> SELECT * FROM myTable
> revert
> sp_helprolemember db_datareader
> REVOKE SELECT ON myTable TO db_datareader
> --Cannot grant, deny or revoke permissions to or from special roles.
> DENY SELECT ON myTable TO db_datareader
>
>
|||Hi Dave
"Dave" wrote:

> I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
>
It sounds like you need to make your security more granular, by creating
your own role(s) that only have the permissions the users actually need. You
could also do this using Windows Groups (or a combination of both!) if you
are using Windows Authentication. By only using Stored Procedures to access
your data it may be possible to limit who can SELECT from a table further.
John

Can I restrict members of db_datareader from SELECTing from a tabl

I have a large database with lost of users, many of whom are in the
db_datareader role.
I have a table that I want to restrict SELECTs on to just sa's.
How can I restrict members of db_datareader from SELECTing from this table?
As shown below, I cannot REVOKE or DENY SELECT from this role.
I could remove everyone from db_datareader and put them in a special role
with SELECT on everything but this table. But is there an easier way?
Thanks
--
USE admin
CREATE TABLE myTable
(col1 int PRIMARY KEY IDENTITY
,col2 int
,col3 int)
INSERT myTable (col2) VALUES (10)
INSERT myTable (col2) VALUES (20)
INSERT myTable (col2) VALUES (30)
SELECT * FROM myTable
execute as login = 'appuser'
select suser_name()
SELECT * FROM myTable
revert
sp_helprolemember db_datareader
REVOKE SELECT ON myTable TO db_datareader
--Cannot grant, deny or revoke permissions to or from special roles.
DENY SELECT ON myTable TO db_datareaderHello,
You can not attempt to issue a DENY or REVOKE on to a fixed role.Issue the
DENY SELECT statement on to all uses who are not supposed to
query the table and see if that works well.
THanks
Hari
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:B4980135-A8AE-4D4B-8B11-2623407D8986@.microsoft.com...
>I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
> How can I restrict members of db_datareader from SELECTing from this
> table?
> As shown below, I cannot REVOKE or DENY SELECT from this role.
> I could remove everyone from db_datareader and put them in a special role
> with SELECT on everything but this table. But is there an easier way?
> Thanks
> --
> USE admin
> CREATE TABLE myTable
> (col1 int PRIMARY KEY IDENTITY
> ,col2 int
> ,col3 int)
> INSERT myTable (col2) VALUES (10)
> INSERT myTable (col2) VALUES (20)
> INSERT myTable (col2) VALUES (30)
> SELECT * FROM myTable
> execute as login = 'appuser'
> select suser_name()
> SELECT * FROM myTable
> revert
> sp_helprolemember db_datareader
> REVOKE SELECT ON myTable TO db_datareader
> --Cannot grant, deny or revoke permissions to or from special roles.
> DENY SELECT ON myTable TO db_datareader
>
>|||Hi Dave
"Dave" wrote:
> I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
>
It sounds like you need to make your security more granular, by creating
your own role(s) that only have the permissions the users actually need. You
could also do this using Windows Groups (or a combination of both!) if you
are using Windows Authentication. By only using Stored Procedures to access
your data it may be possible to limit who can SELECT from a table further.
John

Can I restrict members of db_datareader from SELECTing from a tabl

I have a large database with lost of users, many of whom are in the
db_datareader role.
I have a table that I want to restrict SELECTs on to just sa's.
How can I restrict members of db_datareader from SELECTing from this table?
As shown below, I cannot REVOKE or DENY SELECT from this role.
I could remove everyone from db_datareader and put them in a special role
with SELECT on everything but this table. But is there an easier way?
Thanks
USE admin
CREATE TABLE myTable
(col1 int PRIMARY KEY IDENTITY
,col2 int
,col3 int)
INSERT myTable (col2) VALUES (10)
INSERT myTable (col2) VALUES (20)
INSERT myTable (col2) VALUES (30)
SELECT * FROM myTable
execute as login = 'appuser'
select suser_name()
SELECT * FROM myTable
revert
sp_helprolemember db_datareader
REVOKE SELECT ON myTable TO db_datareader
--Cannot grant, deny or revoke permissions to or from special roles.
DENY SELECT ON myTable TO db_datareaderHello,
You can not attempt to issue a DENY or REVOKE on to a fixed role.Issue the
DENY SELECT statement on to all uses who are not supposed to
query the table and see if that works well.
THanks
Hari
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:B4980135-A8AE-4D4B-8B11-2623407D8986@.microsoft.com...
>I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
> How can I restrict members of db_datareader from SELECTing from this
> table?
> As shown below, I cannot REVOKE or DENY SELECT from this role.
> I could remove everyone from db_datareader and put them in a special role
> with SELECT on everything but this table. But is there an easier way?
> Thanks
> --
> USE admin
> CREATE TABLE myTable
> (col1 int PRIMARY KEY IDENTITY
> ,col2 int
> ,col3 int)
> INSERT myTable (col2) VALUES (10)
> INSERT myTable (col2) VALUES (20)
> INSERT myTable (col2) VALUES (30)
> SELECT * FROM myTable
> execute as login = 'appuser'
> select suser_name()
> SELECT * FROM myTable
> revert
> sp_helprolemember db_datareader
> REVOKE SELECT ON myTable TO db_datareader
> --Cannot grant, deny or revoke permissions to or from special roles.
> DENY SELECT ON myTable TO db_datareader
>
>|||Hi Dave
"Dave" wrote:

> I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
>
It sounds like you need to make your security more granular, by creating
your own role(s) that only have the permissions the users actually need. You
could also do this using Windows Groups (or a combination of both!) if you
are using Windows Authentication. By only using Stored Procedures to access
your data it may be possible to limit who can SELECT from a table further.
John