Hi
I've a database in development environment that uses a full text catalog
created on the directory d:\abc
When i migrate my database to production, on another server, i did the
complete restore from my backup done in development
all tables and so passed ok but my catalogs remains with the old path d:\abc
that doesn't exists on the new server (d: is cdrom)
how can i change it?!?1
i tried to use sp_fulltext_catalog 'cat', 'rebuild', 'new_path' and it
reported error saying thet the database is not yet fulltext enabled
i then tried to do so using sp_fulltext_database 'enable' and the error
returned was something like 'can't enable catalgos because path d:\abc does
not exists'
when i try to delete the catalog it again reports the fact that the database
is not fulltext enabled...
it seems that this is an infinite loop...
deleting the row from sysfulltextcatalogs does not do it because it removes
it but does not let me create another on on the same tables that the previous
was
using sp_fulltext_table and sp_fulltext_columns the error is te same...
how can i go arround this?!?!
best regards
Jorge Ribeiro
Jorge,
This is a bug related to moving / restoring FT-enabled databases to another
server where the drive letter for the FT Catalogs is different than the
orgianal server. You can work around this this "Catch-22" situation with the
following code (Note: Sysadmin level permission is required):
-- Enable System Table UPDATEs
sp_configure allow,1
go
reconfigure with override
go
use <your_user_database_name>
go
-- Record FT Catalog info. (Note: path = NULL)
select * from sysfulltextcatalogs
go
UPDATE sysfulltextcatalogs set path = '<new_drive_letter:\<new_directory>'
WHERE ftcatid = <ftcatid_of_affected_catalog>
go
-- Record new FT Catalog info.
select * from sysfulltextcatalogs
go
use master
go
-- Disenable System Table UPDATEs
sp_configure allow,0
go
reconfigure with override
go
Regards,
John
"Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
news:BDA37D16-A43A-4F51-9D9A-57F3CF7AE5B3@.microsoft.com...
> Hi
> I've a database in development environment that uses a full text catalog
> created on the directory d:\abc
> When i migrate my database to production, on another server, i did the
> complete restore from my backup done in development
> all tables and so passed ok but my catalogs remains with the old path
d:\abc
> that doesn't exists on the new server (d: is cdrom)
> how can i change it?!?1
> i tried to use sp_fulltext_catalog 'cat', 'rebuild', 'new_path' and it
> reported error saying thet the database is not yet fulltext enabled
> i then tried to do so using sp_fulltext_database 'enable' and the error
> returned was something like 'can't enable catalgos because path d:\abc
does
> not exists'
> when i try to delete the catalog it again reports the fact that the
database
> is not fulltext enabled...
> it seems that this is an infinite loop...
> deleting the row from sysfulltextcatalogs does not do it because it
removes
> it but does not let me create another on on the same tables that the
previous
> was
> using sp_fulltext_table and sp_fulltext_columns the error is te same...
>
> how can i go arround this?!?!
> best regards
> Jorge Ribeiro
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Wednesday, March 28, 2012
Wednesday, March 7, 2012
permission question
This is a multi-part message in MIME format.
--=_NextPart_000_0006_01C7F06A.A723CF50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I converted over an SQL 2000 DB to SQL 2005, I added the correct users = to the db. I tried to run an application that uses the db, and I get = access denied to execute stored procedures. I then gave the ID execute = permission to all of the stored procedures, and now it works.
I was just seeing if this is the norm? Do I have to add the id's to = every stored procedure and view or can I do this at a top level security = for the db?
--=_NextPart_000_0006_01C7F06A.A723CF50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I converted over an SQL 2000 DB to SQL 2005, I added = the correct users to the db. I tried to run an application that uses the db, = and I get access denied to execute stored procedures. I then gave the ID = execute permission to all of the stored procedures, and now it = works.
I was just seeing if this is the norm? Do I have to = add the id's to every stored procedure and view or can I do this at a top level = security for the db?
--=_NextPart_000_0006_01C7F06A.A723CF50--Security in SQL Server, as in many other systems work such as:
A user is allowed to perform an operation IF:
That use was (at some level) granted that permission, provided that:
That user do *not* at any level was DENY permission for the operation
Yes, you can grant permissions at a higher level than the individual object level. That higher level
can be schema or database. For instance:
GRANT EXECUTE ON DATABASE::dbname TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mike" <Mike@.community.nospam.com> wrote in message news:uKUJvwI8HHA.3900@.TK2MSFTNGP02.phx.gbl...
I converted over an SQL 2000 DB to SQL 2005, I added the correct users to the db. I tried to run an
application that uses the db, and I get access denied to execute stored procedures. I then gave the
ID execute permission to all of the stored procedures, and now it works.
I was just seeing if this is the norm? Do I have to add the id's to every stored procedure and view
or can I do this at a top level security for the db?|||Hi Mike
To add to Tibors comment, you could grant execute permission to the schema.
But granting at the higher level may allow the user to execute procedures
that they should not, therefore you would have to deny it on that specify
procedure. In general it is better practice to only allow permissions than to
allow widely and deny specifically. This may help
create database test
GO
use test
GO
CREATE TABLE dbo.MyTable ( id int identity not null, value char(1) )
GO
INSERT INTO dbo.MyTable ( value )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
GO
CREATE PROCEDURE dbo.GetMyTableData1
AS
SELECT id, value FROM dbo.MyTable
GO
CREATE PROCEDURE dbo.GetMyTableData2
AS
SELECT value, id FROM dbo.MyTable
GO
CREATE LOGIN USER1 WITH PASSWORD = 'P@.ss0rd!!'
GO
CREATE USER USER1
GO
EXEC dbo.GetMyTableData1
GO
EXEC dbo.GetMyTableData2
GO
/* Each returns resultset */
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData1, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData1', database
'test', schema 'dbo'.
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
GRANT EXECUTE ON dbo.GetMyTableData1 TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Returns Dataset
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
REVOKE EXECUTE ON dbo.GetMyTableData1 TO USER1
GO
GRANT EXECUTE ON SCHEMA:: dbo TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Returns Dataset
*/
EXEC dbo.GetMyTableData2
GO
/*
Returns Dataset
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
REVOKE EXECUTE ON SCHEMA:: dbo TO USER1
GO
GRANT SELECT ON dbo.MyTable TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData1, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData1', database
'test', schema 'dbo'.
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Returns Resultset
*/
REVERT
GO
GRANT EXECUTE ON SCHEMA:: dbo TO USER1
GO
DENY EXECUTE ON dbo.GetMyTableData2 TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Return Resultset
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
-- cleanup
USE MASTER
GO
DROP DATABASE TEST
GO
DROP LOGIN USER1
GO
These links to Books Online may help!
http://msdn2.microsoft.com/en-us/library/ms191465.aspx
http://msdn2.microsoft.com/en-us/library/ms191291.aspx
http://msdn2.microsoft.com/en-us/library/ms190401.aspx
http://msdn2.microsoft.com/en-us/library/ms187965.aspx
http://msdn2.microsoft.com/en-us/library/ms188338.aspx
John
"Mike" wrote:
> I converted over an SQL 2000 DB to SQL 2005, I added the correct users to the db. I tried to run an application that uses the db, and I get access denied to execute stored procedures. I then gave the ID execute permission to all of the stored procedures, and now it works.
> I was just seeing if this is the norm? Do I have to add the id's to every stored procedure and view or can I do this at a top level security for the db?
--=_NextPart_000_0006_01C7F06A.A723CF50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I converted over an SQL 2000 DB to SQL 2005, I added the correct users = to the db. I tried to run an application that uses the db, and I get = access denied to execute stored procedures. I then gave the ID execute = permission to all of the stored procedures, and now it works.
I was just seeing if this is the norm? Do I have to add the id's to = every stored procedure and view or can I do this at a top level security = for the db?
--=_NextPart_000_0006_01C7F06A.A723CF50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I converted over an SQL 2000 DB to SQL 2005, I added = the correct users to the db. I tried to run an application that uses the db, = and I get access denied to execute stored procedures. I then gave the ID = execute permission to all of the stored procedures, and now it = works.
I was just seeing if this is the norm? Do I have to = add the id's to every stored procedure and view or can I do this at a top level = security for the db?
--=_NextPart_000_0006_01C7F06A.A723CF50--Security in SQL Server, as in many other systems work such as:
A user is allowed to perform an operation IF:
That use was (at some level) granted that permission, provided that:
That user do *not* at any level was DENY permission for the operation
Yes, you can grant permissions at a higher level than the individual object level. That higher level
can be schema or database. For instance:
GRANT EXECUTE ON DATABASE::dbname TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mike" <Mike@.community.nospam.com> wrote in message news:uKUJvwI8HHA.3900@.TK2MSFTNGP02.phx.gbl...
I converted over an SQL 2000 DB to SQL 2005, I added the correct users to the db. I tried to run an
application that uses the db, and I get access denied to execute stored procedures. I then gave the
ID execute permission to all of the stored procedures, and now it works.
I was just seeing if this is the norm? Do I have to add the id's to every stored procedure and view
or can I do this at a top level security for the db?|||Hi Mike
To add to Tibors comment, you could grant execute permission to the schema.
But granting at the higher level may allow the user to execute procedures
that they should not, therefore you would have to deny it on that specify
procedure. In general it is better practice to only allow permissions than to
allow widely and deny specifically. This may help
create database test
GO
use test
GO
CREATE TABLE dbo.MyTable ( id int identity not null, value char(1) )
GO
INSERT INTO dbo.MyTable ( value )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
GO
CREATE PROCEDURE dbo.GetMyTableData1
AS
SELECT id, value FROM dbo.MyTable
GO
CREATE PROCEDURE dbo.GetMyTableData2
AS
SELECT value, id FROM dbo.MyTable
GO
CREATE LOGIN USER1 WITH PASSWORD = 'P@.ss0rd!!'
GO
CREATE USER USER1
GO
EXEC dbo.GetMyTableData1
GO
EXEC dbo.GetMyTableData2
GO
/* Each returns resultset */
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData1, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData1', database
'test', schema 'dbo'.
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
GRANT EXECUTE ON dbo.GetMyTableData1 TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Returns Dataset
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
REVOKE EXECUTE ON dbo.GetMyTableData1 TO USER1
GO
GRANT EXECUTE ON SCHEMA:: dbo TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Returns Dataset
*/
EXEC dbo.GetMyTableData2
GO
/*
Returns Dataset
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
REVOKE EXECUTE ON SCHEMA:: dbo TO USER1
GO
GRANT SELECT ON dbo.MyTable TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData1, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData1', database
'test', schema 'dbo'.
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Returns Resultset
*/
REVERT
GO
GRANT EXECUTE ON SCHEMA:: dbo TO USER1
GO
DENY EXECUTE ON dbo.GetMyTableData2 TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Return Resultset
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
-- cleanup
USE MASTER
GO
DROP DATABASE TEST
GO
DROP LOGIN USER1
GO
These links to Books Online may help!
http://msdn2.microsoft.com/en-us/library/ms191465.aspx
http://msdn2.microsoft.com/en-us/library/ms191291.aspx
http://msdn2.microsoft.com/en-us/library/ms190401.aspx
http://msdn2.microsoft.com/en-us/library/ms187965.aspx
http://msdn2.microsoft.com/en-us/library/ms188338.aspx
John
"Mike" wrote:
> I converted over an SQL 2000 DB to SQL 2005, I added the correct users to the db. I tried to run an application that uses the db, and I get access denied to execute stored procedures. I then gave the ID execute permission to all of the stored procedures, and now it works.
> I was just seeing if this is the norm? Do I have to add the id's to every stored procedure and view or can I do this at a top level security for the db?
Subscribe to:
Posts (Atom)