User Access in SQL server 2005

Question

Post reply

User Access in SQL server 2005

Azzu

Ten Centuries

Points: 1363
More actions
January 29, 2010 at 12:52 am

#215133

Hi
I am facing a unique problem maybe i am missing something here. The problem is I have a user Domainname\Azeem
I give the user db_dataReader permission on a database Customers.
Security --> Logins --> LoginName (Domainname\Azeem) rightclick --> Properties --> Usermapping --> databaseName
--> db_dataReader.
but when i right click on the database --> select Properties --> Permissions\EffectivePermissions i see that the user has got the permissions on everything.
Any advice will be immensely appreciated.
Thanks & Regards
Azeem.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Please clarify "permissions on everything"

db_datareader gives a User permission to select from all tables within the database.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Azzu Ten Centuries Points: 1363 More actions · Answer 2

hi

some of the Permissions the user has are:

1.ALTER

2. ALTER ANY APPLICATION ROLE

3. ALTER ANY ASSEMBLY

4. ALTER ANY ASYMMETRIC KEY

5. ALTER ANY CERTIFICATE

6. ALTER ANY SCHEMA

7. ALTER ANY SERVICE

Regards.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Did the user get added to a server role as well? Did the user get added to DBO within the database?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Azzu Ten Centuries Points: 1363 More actions · Answer 4

No i did not add the user to any server roles. I have given the default schema for the user as dbo

Do you want me to look for something specific?

When i am looking at the database properties for the user i am seeing only the db_datareader checked for the database role membership.

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

when you granted permissions to the user, what were the exact commands you issued? Or, in the case that you did it through the GUI, what was your process?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Azzu Ten Centuries Points: 1363 More actions · Answer 6

I have done using GUI. The process i used is

Security --> Loginss --> New Login --> Login with windows Authentication --> User Mapping --> Database --> db_datareader --> ok

then when i go right click the database and select properties and look at the effective permissions for that user everything is being shown in that list.

thanks

sql-638562 Valued Member Points: 57 More actions · Answer 7

Hi Azzu,

Has this database been associated with the user in the past?

Reason for this line of thinking is, if a database has been migrated the exisitng users will remain within the database even though they have never been granted access on the new server.

Just a thought.

Azzu Ten Centuries Points: 1363 More actions · Answer 8

Yes the database was associated with the user. but even if i take any new user and assign him the db_datareader role this issue is encountered.

may be the this has got something to do at the database level. because as and when a user is assigned access to the database all the roles are being assigned irrespective of our choice.

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

I would start by removing the login and the user from the database (make sure it is gone). Even so far as to remove all the new users you have added.

Next, verify that no orphaned users exist - remove them from the database if no Login exists for them. Do this for your user database and the master and model databases as well.

Then stop and restart the service (if you can).

Lastly, try to add the user back in, one by one.

This may seem a bit overkill, but you want to make sure the user is not over-permissioned.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events