New login is created but user cant login using credientials ?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » New login is created but user cant login...

New login is created but user cant login using credientials ?

Rate Topic

Display Mode

Topic Options

Author

Message

maida_rh

Posted Sunday, December 23, 2012 9:05 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 12:10 AM
Points: 42, Visits: 95

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

boeta.schutte

Posted Sunday, December 23, 2012 11:48 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:57 AM
Points: 16, Visits: 228

Don't forget Schema name. Dataabse.Schema.Table

Deny INSERT,SELECT,UPDATE,DELETE ON OBJECT::dummyDB.dbo.userTb to nvp

Post #1399819

maida_rh

Posted Monday, December 24, 2012 12:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 12:10 AM
Points: 42, Visits: 95

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

boeta.schutte

Posted Monday, December 24, 2012 12:55 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:57 AM
Points: 16, Visits: 228

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

maida_rh

Posted Monday, December 24, 2012 2:02 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 12:10 AM
Points: 42, Visits: 95

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 »

Permissions