Minimum User Security Setup???

Question

Post reply

Minimum User Security Setup???

majid bhatti

SSC Enthusiast

Points: 184
More actions
February 14, 2010 at 5:17 am

#156676

Hi, I am new to SQL 2005 and having problem managing user permissions. I am a DBA and want to create some users those who can have only select permission on selected database. User have windows accounts and are login using windows authencations.
I tried create a windows authencated user and gave "public" server role and in User mapping i mapped the user on one UserDB giving "db_datareader & public". but the user is not able to login. i get msg "Login failed for user Domain\User, Error: 18456".
I also gave the user access on master and changed the default db to my UserDB but still the same. With many hit and try i found that User Mapping database roles are not working at all. the only way users can login is when i given them "sysadmin" but giving so they can access all DBs and all permissions.
Can anybody help me in configuring User to access on selected DB through SSMSE?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Is the SQL Browser service running?

Are you running SQL Server 2005 Express Edition? If so, is the system it is running on joined to the domain?

When you create a login, you should not have to specify the public role - it is there by default. You do not have to grant a user access to the master database and should only grant the user access to the databases they need access to.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

majid bhatti SSC Enthusiast Points: 184 More actions · Answer 2

Yes the SQL Browser Services are running. It Not Express edition but Enterprise 64bit and yes its a server machine.

Yes my frined i know that i dont need to give user access to master database but i tired all the possibles and nothing worked.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Google is your friend, first three items when I searched for that error are:

http://support.microsoft.com/kb/889646

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

http://support.microsoft.com/kb/925744

That should get you started on fixing this issue.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

majid bhatti SSC Enthusiast Points: 184 More actions · Answer 4

Thanks, i have already been through those links but nothing seems to work. i also have the SP3 installed but everything is same.

It only allows user to login if i assign he the sysadmin server role 🙁

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

What is the full error you are getting?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

majid bhatti SSC Enthusiast Points: 184 More actions · Answer 6

Let tell u that the same user were able to access before but all of the sudden they are not. i used following script to create windows login in SQL

-- User

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'Domain\User')

CREATE LOGIN [Domain\User] FROM WINDOWS WITH DEFAULT_DATABASE=[promis_05], DEFAULT_LANGUAGE=[us_english]

GO

CREATE USER [Domain\User] FOR LOGIN [Domain\User] WITH DEFAULT_SCHEMA=[dbo]

GO

-- Database Level Permissions

GRANT CONNECT TO [Domain\User]

and the error is ....................................

===================================

Cannot connect to lopsql.

===================================

Login failed for user 'Domain\User'. (.Net SqlClient Data Provider)

------------------------------

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------

Server Name: lopsql

Error Number: 18456

Severity: 14

State: 1

Line Number: 65536

------------------------------

Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)

at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)

at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 7

Check sql server error log for tht message and once u get the correct 'State' in error message refer to http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

MJ

majid bhatti SSC Enthusiast Points: 184 More actions · Answer 8

Thanks Manu, the State is 11 (its also mentioned in the error post earlier). I have been to that link many times even Jeff referred me the same. But the question is that is the user creating script is wrong? Why login works only if i give sysadmin role to the user?