Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Statment, Pull Object name from database and Revoke Expand / Collapse
Author
Message
Posted Friday, January 08, 2010 8:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 02, 2010 11:19 AM
Points: 18, Visits: 33
Hey,

I am kind of new to SQL. I have lots of experience with Selects and Inserts and all that jazz, but this is the first time I have had to push beyond that. I am trying to Revoke all public rights to a database in SQL. I keep encountering two errors my revoke statement

REVOKE SELECT ON @ObjectName To public

wont take unless I put an object name in place of my variable.


The second error is comes from trying to pull an object out of a temporary database the SELECT statement is working but it keeps telling me it cant convert the Object from an NVARCHAR to an int. I understand this which is why I declared my variable as an NVARCHAR in the first place so why is it trying to convert into an int? Here is some example code

DECLARE @ObjectName NvarChar(30)

SELECT @ObjectName = (SELECT Rand(Object) FROM TempTable)

If anyone can tell me what is going on I would be much obliged!

Thanks
Post #844395
Posted Friday, January 08, 2010 9:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 02, 2010 11:19 AM
Points: 18, Visits: 33
Hey,

I am updating my post I figured out the first problem, I cant use Rand() in that context I assume. Because when I re-wrote it without it worked!
Post #844414
Posted Friday, January 08, 2010 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 02, 2010 11:19 AM
Points: 18, Visits: 33
Fixed my second issue as well by using another variable and execution statment!

Thanks! ... I guess
Post #844519
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse