Permissions

I just moved a database from our old server to our new box using DTS.
Everything looks like it's there but I can't find out because I can't query
the database! I keep getting:
SELECT permission denied on object 'RESPONSES', database 'EMYST', owner 'dbo
'.
I've turned on every permission I can find for the user I'm using in Query
Analyzer but I keep getting this message.
I'm not a DBA (ours left) so I'm kind of clueless about where to look. Any
help would be massively appreciated.
Thanks,
DLIEDrLostinExcel wrote:
> I just moved a database from our old server to our new box using DTS.
> Everything looks like it's there but I can't find out because I can't
> query the database! I keep getting:
> SELECT permission denied on object 'RESPONSES', database 'EMYST',
> owner 'dbo'. I've turned on every permission I can find for the user
> I'm using in Query Analyzer but I keep getting this message.
> I'm not a DBA (ours left) so I'm kind of clueless about where to
> look. Any help would be massively appreciated.
> Thanks,
> DLIE
You might want to script out object permissions and logins from the
original server using SQL Enterprise Manager to get them in sync. The
error indicates that the user ID you are using to connect to the server
does not have rights to select from the table in question.
To get through this particular issue for this table, you can log into
the server with a user with sufficient rights and issue a grant on the
table:
grant select on table to username
But I suspect there are larger issues at work here. From SQL EM, right
click on the database - All Tasks - Generate SQL Script. Uncheck
everything on Formatting tab and check Script Database users and Role,
check Script SQL Server Logins, and check Script Object-Level
Permissions from the Options tab. Generate the script, look it over, and
then run it on the new server.
David Gugick
Imceda Software
www.imceda.com|||Hi
Here are some links to articles that should help you resolve your problem.
http://www.support.microsoft.com/?id=246133
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897
Mapping Logins & SIDs after a Restore
http://www.dbmaint.com/SyncSqlLogins.asp
Utility to map logins to users http://www.support.microsoft.com/?id=168001
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
Hope this helps
John
"David Gugick" wrote:

> DrLostinExcel wrote:
> You might want to script out object permissions and logins from the
> original server using SQL Enterprise Manager to get them in sync. The
> error indicates that the user ID you are using to connect to the server
> does not have rights to select from the table in question.
> To get through this particular issue for this table, you can log into
> the server with a user with sufficient rights and issue a grant on the
> table:
> grant select on table to username
> But I suspect there are larger issues at work here. From SQL EM, right
> click on the database - All Tasks - Generate SQL Script. Uncheck
> everything on Formatting tab and check Script Database users and Role,
> check Script SQL Server Logins, and check Script Object-Level
> Permissions from the Options tab. Generate the script, look it over, and
> then run it on the new server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>