SQL Server Performance Forum – Threads Archive

sa Permissions Issue?!?!?!

See if you can replicate this: create a new user and add them to the pubs database. Don’t give them ANY permissions. Then log in to Query Analyzer, and do SELECT TOP 20 * FROM authors. On my machine, it works. Shouldn’t I get a permissions error? Then, I make an .asp page, and utilize the ADO command object to run a stored procedure with that query (and nothing else):
=================================
CREATE PROCEDURE spSelectTest
AS
SET NOCOUNT ON
SELECT TOP 20 * FROM authors
================================== ASP/ADO Code:
Set cmdSQLStoredProcedure = Server.CreateObject("ADODB.Command")
With cmdSQLStoredProcedure
.ActiveConnection = "Provider=SQLOLEDB; Data Source=(LOCAL); Initial Catalog=XXXXXXX; Password=YYYYYYYYY; UserID=ZZZZZZZZZ"
.CommandText = "spSelectTest"
.CommandType = adCmdStoredProc
.NamedParameters = True
.Parameters.Append .CreateParameter("return", adInteger, adParamReturnValue)
End With Set rsSQLStoredProcedure = cmdSQLStoredProcedure.Execute()
arrRecordset = rsSQLStoredProcedure.GetRows
rsSQLStoredProcedure.Close Session("return") = cmdSQLStoredProcedure.Parameters("return") Set rsSQLStoredProcedure = Nothing
Set cmdSQLStoredProcedure = Nothing If IsArray(arrRecordset) Then
Response.Write("An array has been provided by ADO from the database using the GetRows method. ")
Response.Write("The first element at 0,0 is: " & arrRecordset(0,0) & " ")
Response.Write("The Return Parameter is: " & Session("return") & " ")
Session.Contents.Remove("return")
Else
Response.Write("No array has been provided by ADO from the database using the GetRows method. ")
End If
====================================================== If I use my new user account UID and PWD in the connection string, it fails and I get the Else condition. However, if I use the sa account it returns a value. This is true no matter what the permission settings are for the new user! I even made them a system administrator and it still didn’t work on the .asp page. But, the sa account works great. It seems to me that I have ruled out both permissions problems and code problems, and I don’t know what is left. Can you repeat this issue on your system?

Any new user created will be a part of PUBLIC group and for that default permission on PUBS will be db_datareader. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

What does that mean? How do I set the permissions on the new user so that when I run the ADO command it returns a recordset? Both sa and the new user account return a recordset when running in Query Analyzer. What I can’t figure out is why the sa account returns a recordset through the .asp page, but the new user does not, no matter what permissions I give them.

Set the permission for the application login. You should never set permissions at the public level anyway as that is a bad security practice You can set permissions to a database in Enterprise manager and try setting exec permission on the sp for the application login. But if you used any dynamic SQL you will have to set the permissions on the tables. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Although I appreciate your answering the post, your answer does not help me. I HAVE already set the permissions for the specific user created to access the database on behalf of the application. It doesn’t help. The ONLY user that can access the simple sproc listed in my message is the sa user. It’s obviously something with the permissions, but I have given my new user every permission you can through enterprise manager and it doesn’t work. If I change the connection string to the sa user account, the .asp page shows a returned field and the return value. If I use ANY other user, with ANY other permissions assigned, it fails. How is this possible? Can you repeat this problem with the sample below? I have never had anything like this happen before. This is code I have used successfully on multiple applications over the last 3 years. It’s a simple select query. What the hell is going on? Please help me before I go crazy and run off to join the circus. My family will bless you for ever.

GRANT EXEC ON spSelectTest TO newusername MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

This is like one of those twilight zone movies. Suddenly, I think I’m speaking English but in reality I’m apparently speaking in Chinese or something. To repeat: I DID grant EXEC permissions on the sp for the new user. Then I granted select permissions on the table. Then I added the user to the sysadmin group. Then I just checked every single permission available to them when you go in to Enterprise Manager and select the newuser and their Permissions table. Nothing works. ONLY the sa user runs the procedure. This is happening to me on two separate machines locally and I’m getting what I think is the same issue on my hosted server (though I can’t be sure because I don’t have sa permissions there). Although it’s possible I have a weird glitch somewhere I feel it is more likely that somehow a setting is off, but I cannot for the life of me figure out what that might be. Could somebody at least try the code below and see if you can repeat the problem?

What I would look at is: who is listed as the owner of spTestSelect. When you created the sp in QA, you were perhaps logged on as a different user than sa. It you used ‘CREATE PROCEDURE spTestSelect’, rather than ‘CREATE PROCEDURE dbo.spTestSelect’, then the problem might be a "broken ownership chain".

quote:Originally posted by blackblade This is like one of those twilight zone movies. Suddenly, I think I’m speaking English but in reality I’m apparently speaking in Chinese or something. To repeat:

not being funny, but you’re trying to get people to help FOR FREE, OF THEIR OWN ACCORD, I think your style and its implied attitude kind of sucks. Tom Pullen
DBA, Oxfam GB

Adriann: thank you, I will definitely look into that. Thomas: You are right, and I apologize. I hope you and others can understand that it is extremely frustrating to have code and an implementation process that has worked fine for over 3 years on multiple applications suddenly stop working for no reason that I can detect. It is even more frustrating when for days all you can get from your ISP and the multiple user forums you visit is "set exec permissions on the sproc". I am sure we have all been under a serious time crunch before, and had to deal with very frustrating errors or issues at inopportune times. This is by far the worst one I have had. Please forgive me if my frustration is bleeding through on these posts. – Eric

Eric, no problem, we’ve all been in similar situations, I’m sure, I hope you get it sorted. Tom Pullen
DBA, Oxfam GB

Eric, I didn’t read where you said you had actually done that, and I apologize. I wasn’t trying to be flippant. I do actually try to help people out on the forums, and think I probably have helped a few out along the way. Sometimes it’s the simple things people miss when they spend hours trying to solve a complex problem. It might help you to remember that going forward. Pull up Profiler and track what’s hitting the SQL Server. For events, choose: Security Audit: Audit Login Failed Security Audit: Audit Object Permissions Event Security Audit: SP<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tarting Security Audit: SP:Completed This should give you a clear picture of what’s actually happening at the SQL Server level. We had a user with a problem like this a couple weeks ago. He had permissions on his computer configured slightly wrong and wasn’t able to execute the procedure. Either way, this should level down exactly what’s happening. One last thing, we all have busy lives and get frustrated. Be careful not to burn bridges you might need later though. I’ve been lucky enough to form a group of friend that have helped me out tremendously over the years. I’ve also been stupid enough to burn a couple bridges and regret it. It’s easy to do. You might want to consider that next time your surfing for free help. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Derrick, thanks for the advice. I ran Profiler yesterday, and strangely enough it acted as though there were no requests coming in at all. No errors, no nothing when I used the app user connection string. Gave me great feedback when I used the sa user, but nothing when I used the app user. I already run an error checker on the ADO connection, and it sends back nothing as well. So, after finally hitting rock bottom and giving up all hope, I decided to break down and try to learn .NET. I found a tutorial site and made a data grid connection (with the app user) using ADO.NET, and voila! It works. I am at a loss now. If it were an ADO/asp code issue then the the code wouldn’t work for the sa user or on the approx. 600 other connections from asp pages I use in two other apps that are running live. On the other hand, if it were a SQL issue then I wouldn’t be able to run the sproc from QA using the permissions available. At this point I think that the cause may be my bad vibes affecting the app’s sensitivities, and since .NET is supposed to be less sensitive to this sort of thing I think I will just bite the bullet and switch over. This site has always been the most helpful for tough SQL issues in the past, and I thank everyone who attempted to help despite my curmudgeonly responses!

If it were an SQL issue or reaching the SQL Server at all, you would have at least seen the connections failing. You could try a network sniffer to see if the traffic has ever left the box with the old code. I would definitely be interested in seeing the results of that. Having said that, I think .NET is awesome so learning it can’t hurt. Maybe you can help me out with that a little in the future. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I’m much better with SQL, database design, relational theory, etc than .NET (imagine that). [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

]]>