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
DrLostinExcel 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 Servers
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
>
Showing posts with label moved. Show all posts
Showing posts with label moved. Show all posts
Friday, March 9, 2012
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
>
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
>
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 Servers
Hope this helps
John
"David Gugick" wrote:
> DrLostinExcel 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
>
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 Servers
Hope this helps
John
"David Gugick" wrote:
> DrLostinExcel 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
>
Wednesday, March 7, 2012
Permission Questions After running DTS TransferLogins
I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:
> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the new
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership of
> the public role cannot be changed" AND there I am unable to make any changes
> to the public ROLE. Can some one tell me what I am missing ' Thanks you..
>
>
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:
> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the new
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership of
> the public role cannot be changed" AND there I am unable to make any changes
> to the public ROLE. Can some one tell me what I am missing ' Thanks you..
>
>
Permission Questions After running DTS TransferLogins
I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:
> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the n
ew
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership o
f
> the public role cannot be changed" AND there I am unable to make any chang
es
> to the public ROLE. Can some one tell me what I am missing ' Thanks you
.
>
>
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:
> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the n
ew
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership o
f
> the public role cannot be changed" AND there I am unable to make any chang
es
> to the public ROLE. Can some one tell me what I am missing ' Thanks you
.
>
>
Subscribe to:
Posts (Atom)