December 23, 2012 at 9:05 pm
I created users with login option in security tab in sql server 2008 R2 running on windows 7 successfully and grant permissions to :
Use dummydb
Grant REFERENCES ON OBJECT::vuUser TO nvp
GO
Use dummydb
Deny INSERT,SELECT,UPDATE,DELETE ON OBJECT::dummyDB.userTb to nvp
GO
nvp user can login successfuly and resctriction work great but when i create user and grant permissions as above on sql serevr 2008 R2 running on windows server 2008.User login successfully but when i try to grant permission using above code,it says:
Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'usertb', because it does not exist or you do not have permission.
.
Is their is something I am doing wrong or I need to try something else ?????
database+table name is correct...
Need urgent help n thanks in advance
December 23, 2012 at 11:48 pm
Don't forget Schema name. Dataabse.Schema.Table
Deny INSERT,SELECT,UPDATE,DELETE ON OBJECT::dummyDB.dbo.userTb to nvp
December 24, 2012 at 12:20 am
Ok.I tried :
Use dummydb
Deny INSERT,SELECT,UPDATE,DELETE ON OBJECT::userTb to nvp
GO
and work f9 and have another issue,How can i restrict user who login through windows authentication ?
and run this query :
SELECT * FROM sys.tables;
SELECT * FROM sys.databases;
and it returns lists of all tables and databases which i really dont want
December 24, 2012 at 12:55 am
Why do you want to block Windows Authentication ? I don't think that is a great idea.
You can create a logon trigger as follow:
IF EXISTS (SELECT isntuser FROM syslogins where isntuser = 1 and name = system_user)
PRINT 'Windows Authentication';
ELSE
PRINT 'SQL Authentication';
GO
December 24, 2012 at 2:02 am
User can only perform select query on a view of a specified database and he is not allow to access any other stuff i.e. tables,databases,views and stored procedures
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply