SQL 2008 R2 SERVER ROLES

Dear VMontalvao,

We can not reduce rights in application its very big change. Please suggest some thing else.

Our old application runs without SYSADMIN successfully on SQL 2005 BUT
on SQL 2008 our application gives error we identified the error and find that when we give SYSADMIN rights to user our application works fine in SQL 2008.

My question is
"DID MICROSOFT HAS CHANGED SOME THING IN SYSADMIN PERMISSION IN SQL 2008?"

Thanks
Adeel Imtiaz

Dear  dbaduck,

You  have mentioned that Well, sysadmin is sysadmin in both servers, but there are some permissions that may have been inherent in SQL 2005 and now changed in SQL 2008.

Please can you elaborate your comments above.

Thanks
Adeel Imtiaz

In this case you will need to post the error for us to help you further because there are many permissions that were made more granular in 2008. it would be very difficult to guess.

Actually there is no specified error. The error came in our Application.

I am posting my setting of SQL 2005 and SQL 2008 in which my application is working fine.
SQL-20051.jpg
SQL-20052.jpg
SQL-20053.jpg
SQL-20054.jpg
SQL-20081.jpg
SQL-20082.jpg
SQL-20083.jpg
SQL-20084.jpg

What this means to me is that there is a permission that is in SQL 2005 that this user was assigned, but is not assigned in 2008.

But even then if we were to just go off of the screens above, it indicates that I should expect nothing different, because DBO is DBO and that contains all permissions in the database itself.

The only other thing I can think of is if there is a dependence on another database that is named differently or something.  
Again without knowing the error message that gets thrown, it is very hard to diagnose since you have db_owner permissions.

Why the user need access to msdb database?

If the user is dbowner he don't need the others permissions in the same database. dbowner have all possible permissions in the database.

you mean in User Mapping i need DBOWNER.
I have this right but problem is still there

when i click SYSADMIN in SERVER ROLES problem get resolved. WITH OUT SYSADMIN rigths my queries did not even get executed.
SQL-20082.jpg

I think we understand, but it seems that there is a permission that has been granted to the 2005 SQL user that is not being transferred over to the SQL 2008 server.

All possible permissions are not displayed in the gui screenshots that you show.

Right click on the Login under Security on the SQL 2005 server and choose the option on the left of "Securables" and it will show you what rights this login has been granted in the server.  If any are checked, you will want to ensure that those are checked in the SQL 2008 server as well.

This is the only thing that makes sense now is that there has been something granted to the user in 2005.

Dear DBADUCK,

 Can i telecon with you on this you can send em your contact number on

adeel.imtiaz@netsoltech.com

Special Thanks
Adeel Imtiaz

Please see screen shots of affected applications
aaaa.png
aaaa1.png

You didn't answer me why the user need access to msdb database.
Are you running some bulk insert command or process?

I have asked client to send me there user "Securables" .
Actually  we have tested our application with SA so there is no "Securables" option with SA.

Dear  VMontalvao,

Bulk insert in this case no.
Current issue is not related user to bulk insert operations, we are just facing issues regarding execution of procedures where our procedures are converting dates from one format to another.

Hi,
Please note that you are able to debug error message for you application by yourself ;) I've attached you a SQL Server Profiler template. Please import this template in profiler and start a trace using this template. It's good to start profiler locally on the server which you are tracing.

Please rename attached file User-errors.png to User-errors.tdf (I cannot attach file with this extension).

Here's a procedure to trace your err message:
Start profiler

File -> Templates -> Import Template... (pick attached file: User-errors.tdf)

File -> New Trace -> Connect to your SQL Server instance

On trace properties page choose newly imported template (Use the template field)

You can use Save to file if you wish to retain your trace data

On Events Selection tab go to Column Filters..., click on LoginName property and inside box at right side open Like tree and put your application's login there

Click ok and then click Run and start using your application.

===

Methodology is siple here, when you grab error message about permission you grant this permission and again run application, etc.

Please let me know if anything above is unclear or whether you need any additional assistance.

Kind regards,
Daniel
scr.zip
User-errors.png

Dear All,

Till now we have changed the CODE to make our application worked with out SYSADMIN permission.

Special thanks to DBADUCK.

Thanks

There is no second way to access your application with out sysadmin rights if your application   has only one access through SYSADMIN you need to change your application code so that the other users which are without sysadmin rights can access it.

I've requested that this question be closed as follows:

Accepted answer: 0 points for Netsol-NOS's comment #38178429

for the following reason:

We have changed our Application code. So that the SQL user which are without sysadmin rights can access the application.

Till now we have changed the CODE to make our application worked with out SYSADMIN permission.
Which is exactly what was suggested here:  http:#a38021533 and notice your response here: http:#a38022268 :

We can not reduce rights in application its very big change. Please suggest some thing else.

Which turned out to be exactly what you ended up doing see here http: #a:38048114:

Till now we have changed the CODE to make our application worked with out SYSADMIN permission.

Of course, you could also argue that your question was:

What are the difference between SYSADMIN rights in SQL 2005 and SQL 2008.

And the answer to that question was provided here: http:#a38020520, here: http:#a38021533 and here: http:#a38022344

So I am going to go ahead and open the question to allow you to reconsider how you allocate the points for this question.

Thanks,
Anthony