August 14, 2008 at 9:24 am
Do you remember that recent article by Paul Randal, concerning what he called "involuntary DBA's". Well, that's me. (Actually, I don't mind it, but when it comes to the more DBA'er type activities, I confess to being a novice.)
Anyway, I've caused myself a problem and am not sure how to get out of it. I've got SQL Server 2005 on a VM, and I've copied all of our test databases to it. We've got a regular user, called intake, which we use to run SP's, perform SELECT's, etc. Normal database activities for a user that can't change the database schema, etc. I had thought that I had set the permissions correctly for intake, but I've found that on several tables I've got a grantor of "intake" as well as "dbo". I don't know how that has happened.
Second, although I can log into SSMS as intake, I cannot connect to the database as intake in our applications. I'm getting a, "Login failed for user 'Intake'" error message, when I attempt to run a SP. The weird thing is intake has execute permissions to that SP, and I'm still getting that error message.
So, I could use help.
Kindest Regards, Rod Connect with me on LinkedIn.
August 14, 2008 at 9:36 am
Grantor is simply who granted the permissions to the grantee. You can change this by revoking the permissions and regranting it.
Do the SIDs match up for intake on the database (query sys.database_principals in the database) and on the server for the login (query sys.server_principals)?
K. Brian Kelley
@kbriankelley
August 14, 2008 at 10:26 am
K. Brian Kelley (8/14/2008)
Grantor is simply who granted the permissions to the grantee. You can change this by revoking the permissions and regranting it.Do the SIDs match up for intake on the database (query sys.database_principals in the database) and on the server for the login (query sys.server_principals)?
Yes, the SID's are identical between sys.database_principals and sys.server_principals for user "intake".
Kindest Regards, Rod Connect with me on LinkedIn.
August 14, 2008 at 10:30 am
Is the login failed happening for all stored procedures or just that one?
K. Brian Kelley
@kbriankelley
August 14, 2008 at 1:34 pm
Okay, so the connection is failing. Check the password you're using in the app.
K. Brian Kelley
@kbriankelley
August 14, 2008 at 2:07 pm
OK, I've found the problem. Apparently the username is case sensitive. I didn't expect that. I know that the password is case sensitive, but I didn't realize the username was case sensitive. How do I make it so the username is not case sensitive?
Kindest Regards, Rod Connect with me on LinkedIn.
August 14, 2008 at 2:20 pm
The SQL Server server collation determines that. However, to change it isn't a quick thing:
Setting and Changing the Server Collation (SQL Server 2005 Books Online)
K. Brian Kelley
@kbriankelley
August 14, 2008 at 2:57 pm
WOW! You're right, that is not going to be easy or quick. Someone else set up this new test database, and now I wish he hadn't done that. How, then, should SQL Server be installed on a new Windows Server, so that it will not be case sensitive with regards to usernames?
Kindest Regards, Rod Connect with me on LinkedIn.
August 14, 2008 at 3:12 pm
The default collation isn't case-sensitive.
K. Brian Kelley
@kbriankelley
August 14, 2008 at 3:42 pm
Password is always case sensitive because SQL Server is generating and comparing a hash.
K. Brian Kelley
@kbriankelley
August 15, 2008 at 9:30 am
Brian, I'd like to ask a question about the "Setting and Changing the Server Collation" article that you referred to. Steps 2 and 3 of the instructions say to export all of the user data from databases and then drop the databases. Can I just detach the databases instead, change the collation, and then reattach the databases?
Kindest Regards, Rod Connect with me on LinkedIn.
August 15, 2008 at 3:10 pm
I would assume so, but to be honest, I've not tried it. I've only had to deal with collations when a 3rd party vendor insists on the whole binary sort order (PeopleSoft and other HRP packages). Something I'd test out in a non-prod environment to be sure.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply