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

New login is created but user cant login using credientials ? Expand / Collapse
Author
Message
Posted Sunday, December 23, 2012 9:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, Visits: 111
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
Post #1399790
Posted Sunday, December 23, 2012 11:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 27, 2014 5:16 AM
Points: 18, Visits: 276
Don't forget Schema name. Dataabse.Schema.Table

Deny INSERT,SELECT,UPDATE,DELETE ON OBJECT::dummyDB.dbo.userTb to nvp
Post #1399819
Posted Monday, December 24, 2012 12:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, Visits: 111
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
Post #1399829
Posted Monday, December 24, 2012 12:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 27, 2014 5:16 AM
Points: 18, Visits: 276
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


Post #1399833
Posted Monday, December 24, 2012 2:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, Visits: 111
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
Post #1399843
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse