October 7, 2005 at 8:49 am
Hi,
I have one SQL Server and number of DB into it. Different user has access to Different DB through Application.
I have to monitor this whole system as a Sql Server Administrator. Now when user tries to Login at Sql Server I should know which user is trying to Login and which DB user is trying to access. If the user is in today's working list then I will give access otherwise restrict it becoz the user does not appear in today's list.
------------------------------
For example:
A, B and C are the employee of the company.
On 1/1/2005, on list A and B is there who can access Sql server. So when C tries to login I should know and will restrict him.
Now on 2/1/2005, on list A and C is there who can access Sql Server. So when B tries to login on 2/1/2005 I should know and will restrict him.
Sometimes it is like A can access A1 Database but not A2. In this case when A tries to access A1 DB I should Know and I will give access. But when A tries to access A2 DB I should know and I will restrict him accessing A2 DB.
-------------------------------
Remember one thing that task list is not predefined for furture dates. It comes to me every morning and according I have to restrict the user accessing Sql Server.
1) Is there any System level Store Procedure that I as an SQl Server Admin can access and restrict the user.
2) How can I monitor the user who is trying to login particular DB of Sql Server before he actually login. Through Profile we come to know when user is logged-In.
3) Into which System table does enteries are maintained of who have successfully logged IN and who were failed to Logged IN
Please help I am totally fresher to Sql Server.
October 7, 2005 at 9:01 am
1) Is there any System level Store Procedure that I as an SQl Server Admin can access and restrict the user.
Use sp_grantdbaccess to give user access to a db
and sp_dropuser to remove users access to a db
2) How can I monitor the user who is trying to login particular DB of Sql Server before he actually login. Through Profile we come to know when user is logged-In.
Thats like saying can you tell me when my server is going to crash before it crashes. I don't understand what you mean here.
Use sp_who to tell who's logged it, it will be easier to see than in profiler.
3) Into which System table does enteries are maintained of who have successfully logged IN and who were failed to Logged IN
If your using windows logins try the security log.
otherwise I don't think sql server keeps track of login/failed logins. maybe someone else knows
October 7, 2005 at 9:59 am
3) Into which System table does enteries are maintained of who have successfully logged IN and who were failed to Logged IN
Depends on what options you have set on, sql server can record failed and successfull logins, it records them to the sql server log. Right click on your server and look at the properties on the secuirty tab. I seem to recall there are one or two scripts on this site which allow you to pull back information from the log.
Growing old is mandatory, growing up is optional
October 7, 2005 at 2:31 pm
3) Into which System table does enteries are maintained of who have successfully logged IN and who were failed to Logged IN
You can create alerts for successful and failed logins that execute the proc of your preference
* Noel
October 10, 2005 at 2:01 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply