Re: Problems with Database Permissions on SQL 2008



Did you check the permissions of the user in question using the table-valued function? If so, what was the result? The SQL Server GRANT statement you issued should still work.

Also, realize that by moving database instances (depending on how you did it) you can get users and logins out of sync. You may need to use sp_change_users_login (http://msdn.microsoft.com/en-us/library/ms174378.aspx) or ALTER USER (http://msdn.microsoft.com/en-us/library/ms176060.aspx) to resynchronize the user and login on the new instance.

Hope this helps,
Bob Beauchemin
SQLskills


"Bernard" <Bernard@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:20FCB22E-81FC-475E-94FE-812088651665@xxxxxxxxxxxxxxxx
I changed the username in the web application connection string to 'sa' to
see if it's a permissions thing.

The web application will work with the 'sa' account. So I know it has to do
with the permissions of the user account but what am I'm missing. I thought
using the command below would give all the necessary permissions needed to
update, insert, delete and select?
--
Bernard


"Bob Beauchemin" wrote:

That should work unless there is a conflicting deny. You can see the
permissions that a particular user has by using the table-value function
fn_my_permissions (http://msdn.microsoft.com/en-us/library/ms176097.aspx)
with the appropriate parameters. There's also an example of listing
permissions for another user, if you have permissions to do that. If you
happened to have changed the web application at the same time, also make
sure you're running it as the user that you think you are.

Hope this helps,
Bob Beauchemin
SQLskills

"Bernard" <Bernard@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4050DD2E-B9FC-41CC-AD78-E2EA56C04E74@xxxxxxxxxxxxxxxx
>I have a web application that is going to use SQL 2008. Everytime I
>attempt
> to do anything on the table(s) I get the following message.
>
> "Unable to get records. The SELECT permission was denied on the object
> 'tablename1', database 'databasename', schema 'dbo'. The SELECT > permission
> was denied on the object 'tablename1', database 'databasename', schema
> 'dbo'."
>
> The web application was working fine on SQL 2000 database. I created > the
> same username on SQL 2008 server. I did a 'GRANT SELECT, UPDATE, > DELETE
> INSERT to username' on the database.
>
> What am I missing. Working on SQL 2008 dealing with permissions is
> different. Do I have a problem with the column permissions? If so how > to
> fix that.
>
> BL
>
> -- > Bernard



.



Relevant Pages

  • Re: SQL CE Synching Problems
    ... Have you granted IUSER_ServerName access to your publication within SQL ... It looks like the permissions problem is getting access to the publication. ... so the issue has to be between the server tools and the publisher. ... > A request to send data to the computer running IIS has failed. ...
    (microsoft.public.sqlserver.ce)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)