June 21, 2010 at 7:34 am
As admin I have installed an instance of SQL Server 2008 on a Windows machine, so I am able to navigate everything on it fine. There's another user, Mirek, who can login to my Windows machine but can't login to SQL Server.
I would like to grant him access (using Windows Authentication) to my instance but restrict him from writing or dropping any data.
I tried the following:
Databases -> Logins, right clicked to add login etc, and got the error:
"Create failed for Login 'WIN-7U1MCSXTJ5Z\Mirek
An exception occured while executing a Transact-SQL statement or batch.
...
Windows NT user or group '''WIN-7U1MCSXTJ5Z\Mirek' not found. Check the name again."
How do I do this? Thank you!
June 21, 2010 at 8:08 am
Does this login exist in AD or is it just a user that exists on your local machine?
Can you browse to the user name through the add login dialog and select it that way?
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
June 21, 2010 at 11:17 am
Hi Jason,
Aj jaj jaj!. Yes, I think I can find the user as an object in the dialog box afterall. Though the naming convention for the Name is slightly different than on the logon window in Window I think they are referring to the same user.
I will try this.
Thank you!
June 21, 2010 at 12:11 pm
Jason,
The above worked. Thanks.
At this point how to I restrict this user to only querying (reads) the databases on the instance?
June 21, 2010 at 1:55 pm
Grant db_datareader to the specific databases that the user is permitted to see. Better would be to grant select to a ROLE for the specific tables that the user should be allowed to see, then add that user to the ROLE.
And even better yet - create stored procedures that can select from the specific tables. Grant execute to these procs to the ROLE. Add the user to the ROLE, and then you know that the person in question can only select from specific objects.
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
June 21, 2010 at 3:53 pm
I'm going to go with your first advise to begin with:
"Grant db_datareader to the specific databases that the user is permitted to see. "
When I run this:
use AdventureWorksDW
go
exec sp_addrolemember db_datareader, Mirek
go
I get:
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'Mirek' does not exist in this database.
But, Mirek exists as under Instance->Security->Logins as a user.
Please, do you have another suggestion? Mirek can now logon to SQL Server but I still do not have his permissions refined so that he can only query. Also, can I grant these permissions instance wide or must I grant this permission on a database by database basis?
June 21, 2010 at 6:58 pm
I ran the same TSQL command against another db: AdventureworksLT and it worked! So, I retried against AdventureworksDW and it failed once again. I tried against a 3rd db: AdventureworksLT2008 and it failed. So, this works for some but not all databases. Any idea why?
Goal: to give user permissions to only query the databases.
Helen
June 21, 2010 at 7:57 pm
hxkresl (6/21/2010)
I'm going to go with your first advise to begin with:"Grant db_datareader to the specific databases that the user is permitted to see. "
When I run this:
use AdventureWorksDW
go
exec sp_addrolemember db_datareader, Mirek
go
I get:
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'Mirek' does not exist in this database.
But, Mirek exists as under Instance->Security->Logins as a user.
Please, do you have another suggestion? Mirek can now logon to SQL Server but I still do not have his permissions refined so that he can only query. Also, can I grant these permissions instance wide or must I grant this permission on a database by database basis?
Prior to adding the user to the datareader role, did you create the user in the database and associate it to the login?
Probably the easiest way to do this at this point is to follow the instructions in this link:
http://msdn.microsoft.com/en-us/library/aa337545.aspx
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
June 21, 2010 at 8:17 pm
To answer the question "did I create a user in the database and associate it to a login?", my answer is that I did the following:
Expanded the default instance-> Security -> and right clicked on Logins to add new login. I added Mirek in the dialog box after which his name then appeared on the list of logins for the instance. I think that means that yes, I did that.
Correct me if I that is not what I should do. I'll go to the link next.
June 21, 2010 at 9:26 pm
What you did was the first part. You created a login (in the instance) for the user. In order for that user to do anything in a database, you then need to create that user in the database. The link provided will help you with that part.
After the user is created in the database, then you assign the user to the datareader role as necessary.
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
June 21, 2010 at 10:33 pm
Jason, thank you.
Nothing better than dialogue to help make the documentation, even excellent documentation on msdn.microsoft.com come together.
I see I was missing the middle step. Using the instructions at that link I was able to assign user to the database and assign the db_datareader role to the user ie. complete third step.
Thank you so much for helping me take a bite out of my confusion around service accounts!
June 21, 2010 at 11:03 pm
You're welcome.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply