February 23, 2011 at 3:41 pm
These are the commands I'm executing:
USE [Messenger];
GO
GRANT DELETE ON [dbo].[Messages] TO [MM_TableAccess];
GO
GRANT INSERT ON [dbo].[Messages] TO [MM_TableAccess];
GO
GRANT SELECT ON [dbo].[Messages] TO [MM_TableAccess];
GO
GRANT UPDATE ON [dbo].[Messages] TO [MM_TableAccess];
GO
MM_TableAccess is a role. Toad tells me these commands executed successfully.
After executing this, when I look at the permissions on the table, it shows none; viewing the create script with "permissions" turned on shows no grant commands.
I've tried refreshing the table; closing Toad and reopening it again; rebooting. Nothing works. This is the ONLY table in the database (only 6 tables) I can't add these permissions to.
Here's my table create script:
USE [Messenger];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[Messages] (
[MessageID] bigint IDENTITY(1, 1) NOT NULL,
[ParentID] bigint NOT NULL,
[CategoryID] bigint NOT NULL,
[PostedByID] bigint NOT NULL,
[DatePosted] datetime NOT NULL,
[MessageTitle] nvarchar(50) NULL,
[MessageBody] nvarchar(4000) NOT NULL,
CONSTRAINT [PK__Messages__07F6335A]
PRIMARY KEY NONCLUSTERED ([MessageID] ASC)
WITH ( PAD_INDEX = OFF,
FILLFACTOR = 100,
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY],
CONSTRAINT [FK_ParentID]
FOREIGN KEY ([ParentID])
REFERENCES [dbo].[Messages] ( [MessageID] ),
CONSTRAINT [FK_CategoryID]
FOREIGN KEY ([CategoryID])
REFERENCES [dbo].[Categories] ( [CategoryID] )
ON DELETE CASCADE
)
ON [PRIMARY];
GO
I'm using SQL 2005 SP4, on Windows XP SP3.
What could be going on?
February 24, 2011 at 6:49 am
Try to use different tools than TOAD for start (for example sqlcmd or SQL Server Management Studio) and see if it works.
February 24, 2011 at 9:45 am
Well, I can't use Management Studio because it's corrupt (that's why we went to Toad).
But anyway, I changed the name of the table to MessageArchive, and lo and behold, the permissions were applied properly.
Is Messages some kind of reserved name?
February 24, 2011 at 12:34 pm
Management Studio is just a client tool. If it's corrupt, try reinstalling it.
February 24, 2011 at 12:59 pm
Oh believe me, I did. Multiple times. It still didn't work. It could be that it's not Management Studio but some weirdness in the registry, or something like that. Something related to my particular computer.
But Toad seems to work fine most of the time (it's occasionally buggy -- no question Management Studio is a better product overall) and it beats wiping the hard drive clean and reinstalling everything.
February 25, 2011 at 4:48 am
I know this is a stupid question, but did you uninstall it first? Did you try the repair utility?
As far as Messages go, message is a keyword for Service Broker, but the plural form is not. EDIT: Maybe it's a TOAD keyword or system table name or something? (Haven't used TOAD, so I don't know).
February 25, 2011 at 8:59 am
CynthiaD (2/24/2011)
Oh believe me, I did. Multiple times. It still didn't work. It could be that it's not Management Studio but some weirdness in the registry, or something like that. Something related to my particular computer.But Toad seems to work fine most of the time (it's occasionally buggy -- no question Management Studio is a better product overall) and it beats wiping the hard drive clean and reinstalling everything.
Ummm if Management Studio is not working and won't work on the same system the database is being served from I would guess your issue is the SQL Server Install and not your script or Client tool.
If the SQL Client tool that comes with SQL did not install correctly, how would you expect the server to?
February 25, 2011 at 9:46 am
I don't think it's the install -- then Management Studio would not have worked from the beginning. Instead it gradually got buggier and buggier until it would crash as soon as you did anything.
We have not had very many problems with the server itself. I doubt this problem is related to Sql Server itself. It's more likely some bug in Toad. But anyway I found a workaround so I can live with it.
February 25, 2011 at 9:49 am
@Brandie -- it was so long ago I can't remember what I did (a couple of years ago). I think I probably did uninstall it. Maybe I will give it a try again -- it would be nice to use it instead of Toad.
February 25, 2011 at 9:51 am
SanDroid (2/25/2011)
CynthiaD (2/24/2011)
Oh believe me, I did. Multiple times. It still didn't work. It could be that it's not Management Studio but some weirdness in the registry, or something like that. Something related to my particular computer.But Toad seems to work fine most of the time (it's occasionally buggy -- no question Management Studio is a better product overall) and it beats wiping the hard drive clean and reinstalling everything.
Ummm if Management Studio is not working and won't work on the same system the database is being served from I would guess your issue is the SQL Server Install and not your script or Client tool.
If the SQL Client tool that comes with SQL did not install correctly, how would you expect the server to?
So if you use the sqlcmd command prompt utility to execute youre script it works?
February 25, 2011 at 12:02 pm
I will run the utility and let you know -- not enough time right at the moment.
February 25, 2011 at 12:45 pm
CynthiaD (2/25/2011)
I will run the utility and let you know -- not enough time right at the moment.
The command line utility should return a better error message and let you know if it is an issue with MDAC 2.8 or the SQL Management Studio. My guess is the OLEDB and SQL Native drivers loaded by MDAC and SQL Client install are currupted. You can d/l a manual install of MDAC from MSDN.
TOAD does not need OLEDB to connect and execute SQL commands, but some DMO engine commands will not work over ODBC calls unless they are in a sproc running on the server and you exec the sproc.
This SPROC method is how I used to resolve this issue as a DBA for my developers that could only connect ODBC (JAVA, Net Fussion, PHP, NSF, etc...).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply