October 28, 2009 at 9:46 am
Hi
Sorry, I need to post this as well 'cause I can't find how to relate a login to a user.
What I know ...
A person can log into the SQL Server thru a login with "SQL Server authentication".
He then can query the databases without a problem.
I can create a user whom I grant permissions on some databases, tables but how the hall can I tell SQL Server to use this user when somebody logs in with a specific user.
If anybody out there knows the answer please tell me ... the few hairs remaining on my head will be grateful.
🙂
October 28, 2009 at 10:28 am
in SSMS, right click the login and go to properties. Choose user mapping on the menu that is on the left side of that screen. It will show you what databases that login has access to and it will show you what user that login is mapped to.
If you need to change the mapping, use sp_change_users_login
October 29, 2009 at 2:23 am
Thank you for your answer Adam. This is what I did, but it doesn't work the way I'd like to. I may have overlooked something, but what 🙂
October 29, 2009 at 2:50 am
rot-717018 (10/29/2009)
Thank you for your answer Adam. This is what I did, but it doesn't work the way I'd like to. I may have overlooked something, but what 🙂
Can you explain what the problem is ?
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 29, 2009 at 3:17 am
Let me summarize the situation.
SQL server
a login zl1 is mapped to the DB dbqa3 with user zu1 default schema zs1
database roles are db_datareader, db_denydatareader, public
DB dbqa3
USERS default schema is zs1
database roles are db_datareader, db_denydatareader
SCHEMAS
zs1 user zu1 explicit permission Select granted
VIEWS
v107 user zu1 explicit permission Select granted
Now I have 2 problems
1.
Logged in as dbadmin,
the query 'Select * from dbqa3.zs1.v107' doesn't work --> Invalid object name dbqa3.zs1.v107
2.
Logged in as zl1 with SQL Server Authentication,
the query 'Select * from dbqa3..v107' doesn't work either --> The SELECT permission was denied on the object 'v107', database 'dbqa3', schema 'dbo'.
I certainly missed something somewhere, but what?
Thanks for your help Siverfox
October 29, 2009 at 3:28 am
ok..
juts to confirm something for me. if you go into SSMS, navigate to the database in question, expand the tables, you should see, based on your query.
zs1.v107 in the table list
if that is the case, using a sysadmin account, based on question 1, should work.
for question 2.
because you are not specifying the schema, it is defaulting to dbo.
it does make me wonder if you have actually created the objects under the schema you mentioned, or are they under dbo.
Although looking again at your post, why are you giving it read rights, then denying read rights
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 29, 2009 at 3:37 am
If you want to play around with permissions, wouldnt it be easier not to use any of the database roles, and just grant select permissions to the objects that are required.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 29, 2009 at 3:52 am
a large step forward, thanks
the view was created under dbo. I deleted it and recreated it under zs1 and it solves question 1! thanks!!!
for question 2, my aim is to grant only access to view v107 to the user zu1. the views to which access is granted will be extended in the future ...
If you want to play around with permissions, wouldnt it be easier not to use any of the database roles, and just grant select permissions to the objects that are required.
well, this may be the problem after all ... I'll try to remove the database roles ...
October 29, 2009 at 4:25 am
thank you so much silverfox for your precious help!!!
it works now ...
I'll drink a big beer on you this evening
🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply