Last Access Time For SQL Login

SQL Server Admin 2014 :: Deny Access To AD Login For Certain Period Of Time

SQL server job or SP to deny access to an AD login for certain period of time to SQL server instance...i.e. to deny access to login ADxyz from 12 PM to 10 PM and revoke access to same login at 10:01 PM...

How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.

I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.

1 Has the current user been granted login access to the trusted SQL Server?

2 Has the current user been granted access to a specific database?

3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?

Thanks,
Sean

SysProcesses Login Time

Hi,

We have written various applications that have a connection table for managing users logins, we have a connect stored proc that inserts the SIPD and the login time from sysprocesses.

From time to time we clean up our connections table by queryng against the SPID and login_time in sysprocesses and clear it if these don't match. This has been fine for a few years, howver we have found recently with a client that with some SPID's in sysprocesses that SQL server is changing the login time, this is unexpected behaviour.

The client is using SQL server 2005 and this has caused us problems, we have worked around it for now but wondered if anyone new why on this particular setup why the login time would change even though they have not disconnected.

We have other clients using SQL 2005 but have never seen this issue before, I wondered if there was a setting we have not seen that can cause this.

Thanks

Splitting SQL Server Date/Time Column Into Access Date Column And Access Time Column

I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.

What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?

Thanks,

Steve

Cant Login Into Lcoal When Installing Time Tracker Starter Kit..

During the istall of the issue tracker starter kit, im prompted ti select the type of DB. If I select MSACCESS, all is good I have " Microsoft SQL Server 2005 Express Edition with Advanced Services" installedIf I select Microsoft SQL Server/MSDE 2000 I get the following 1) Sql Server ( I select local)2) connect using                 a) Windows authentication - WhenI do this I get the error message "Login Failure: could not loginto server (local)"                 b) SQL Server authtication - WhenI do this I get the error message "Login Failure: could not loginto server (local)"Please Please help me. I have to get this going or I'm mud !!!  actually , less than mud 

How To Avoid RS Login Window At Time Of Deploying Report?

Hi,

i created report and i have to deploy report , at time of deploying it asks report services login credentials.my sql server is windows authentication .it doesn't accept any credentials.how to avoid RS login window at time of deploying report.


Plz help me ASAP

thanks,

...kalyan

Login Failed For User Null Occurs After A Period Of Time

I have an identical SQL database on two machines (my machine and a webserver) that links to a database on a third server (S3). When I executea stored procedure on my machine that accesses a database on S3, italways runs without a problem. However, when I run the same storedprocedure on the webserver (via Query Analyzer on my machine, connectedto the webserver), the stored procedure runs without a problem forabout ten minutes, and then I suddenly receive the error "Login failedfor user '(null)'. Reason: Not associated with a trusted SQL Serverconnection.". If I then connect remotely to the webserver via RemoteDesktop Connection and run the stored procedure there it works, andthen I can run it again on the webserver via Query Analyzer on mymachine, but the same problem will occur again after about ten minutes.The only difference between my machine and the webserver is that thewebserver is running Windows Server 2003, whereas my machine runsWindows XP. All settings in SQL on the two machines are identical, so Iwould love to know if anyone has a possible solution to this seeminglyrandom problem.

Reporting Services :: SSRS 2012 Login Prompt During Run Time?

SSRS 2012 Login prompt during runtime?I've already did some work arounds that we're posted here but still doesn't solve the login prompt.

How To Get SQL SERVER Login Name From Access Project

Restrict Login Access By Hostname/ip Possible?

In SQL2005, is it possible to restrict incoming sql or windows authentication logins by source hostname or IP address? Perhaps using endpoints?

We are currently using hosts file on the db server as a temporary solution, but the SA will not allow us to use this as a permanent solution.

The app team I support is asking me to build an "idiot proof" database so that an Dev or Test app server they may configure, doesn't unintentionally connect to the Production database and damage/alter data.

Don't worry, I've already asked if they can't follow some kind of M&P's to prevent this kind of silly behavior, but they insist on DBA team creating a bullet-proof database that can outsmart app developers who try to attempt something dumb like this.

Any ideas or suggestions would be greatly appreciated.

TIA,
Phil

Phil Streiff

Novell Login Prevents SQL Access??

I found this on the SQL Help page on the Data Link Connection Properties Tab (created by opening a 'test.udl' file on the workstation):

"The SQL Server system administrator must have associated your Microsoft Windows network ID with a SQL Server login ID."

While I can log into the SQL Server from a different workstation NOT using Novell, and using my Windows Authenticated UserID and PW, trying the same from a Novell networked workstation using the UserID and Password which is good ONLY at that one workstation, I cannot access the same SQL Server.

How does one "associate a Microsoft Windows network ID" when Novell is being used to access the Windows network?

The IT department says Novell should have not effect a Windows login, but it apparently is.

SQL Server Login Form For Access

Hi there...I use SQL server integrated security so when a user opens a database inaccess it prompts the username & password in a small popup box onconnection, but I'd like to use my own customised form for theauthentication process, is this possible? I do know that this login popboxis displayed before any forms are loaded, can it be said that on databaseconenct that the form is opened? How will I transfer the values entered intothe login form to the sql server for authentication?Thanks alot in advanceRudi Groenewald

Can I Access SQL Login Users In ASP.Net Application

Hello All,

Limited Remote Login Access

Hello all,

I'm very new to this so excuse my naiveity...I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>sqlexpressuserid I can only see the systemdb's...I am trying to view adventure works, just to see how this remote connection stuff works...any ideas?



Thanks,

Rob

Limited Remote Login Db Access

I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>sqlexpressuserid I can only see the system db's...I am trying to view adventure works, which I can see on the desktop fine using the same login...any ideas?



Thanks,

Rob

Recovery :: How To Find Last Login Date / Time For User DML Activity On Databases On Instance

How to find last login date/time for user DML acitivity on databases on Instance?

Is there any way we can find our the last login date/time for databases?

Note: 1. We can find if the SQL Trace is running and store.This is not good solution
         2. Audit logins off/on is also not good solution.
         3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.

500 Login Can Access Single Database And Can Save?

I have a small project for educational institutuin online examination. But 500 students has to login and save their answers.. Is it possible to have in sql server 2008 for 500 login can access a single database & can save?

Question: Recording Login Access To SQL Database

Hi, all:I have been tasked with determining how many licenses of a certain SQLServer 2000-based application are actually in use: in other words,record or log connections made to the database by clients.Client connections will be made with a generic SQL Server ID which hasbeen granted access to the DB; security is maintained inside theapplication, and a user only logs in with their personal ID in theapplication itself.I know I can go in at any time into the management/CurrentActivity/Process info and see current sessions against the target db,grab the host name from that view, and do an "nbtstat -a" to glean thelogin ID of the connected user.If there were a way to record connections made to a DB in the eventviewer or SQL log, I could hopefully get the information I need.But I see no way to have the SQL Server log connection information likethis.Does anyone know how I might collect this info?Thanks!Cheers,BD

SQL Script To Add A SQL Server Login That Can Access All Databases

Dear all,

I wrote the below script to add a SQL server login account that is the db_datareader, db_datawriter, and granted permission on all stored procs, functions, and views on all databases within a server.



Code Snippet
USE master
GO
SET NOCOUNT ON
DECLARE @database_name sysname
DECLARE @object_name sysname
DECLARE @object_type char(2)
CREATE TABLE #databases (DATABASE_NAME sysname, DATABASE_SIZE int, REMARKS varchar(254))
INSERT #databases EXEC sp_databases
-- ++++++++++++++++++ add SQL Server Login ++++++++++++++++++
IF EXISTS (
SELECT 1 FROM master.dbo.syslogins
WHERE [name] = 'WEB_USER2'
) BEGIN
DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT DATABASE_NAME FROM #databases
OPEN db_cur
WHILE 1 = 1
BEGIN
FETCH db_cur INTO @database_name
IF (@@FETCH_STATUS <> 0) BREAK
EXEC ('USE ' + @database_name +';
IF EXISTS (
SELECT 1 FROM sysusers
WHERE [name] = ''WEB_USER2''
) BEGIN
EXEC sp_revokedbaccess ''WEB_USER2''
END
')
END
CLOSE db_cur
DEALLOCATE db_cur
EXEC sp_droplogin 'WEB_USER2'
END
EXEC sp_addlogin
@loginame = 'WEB_USER2',
@passwd = 'password'
-- ++++++++++++++++++ loop thro' all User-Databases ++++++++++++++++++
DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT DATABASE_NAME FROM #databases
WHERE DATABASE_NAME NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution', 'ASPState')
OPEN db_cur
WHILE 1 = 1
BEGIN
FETCH db_cur INTO @database_name
IF (@@FETCH_STATUS <> 0) BREAK
PRINT ''
PRINT 'Current database=' + @database_name
-- add user to databases
EXEC ('USE ' + @database_name +';
IF EXISTS (
SELECT 1 FROM sysusers
WHERE [name] = ''WEB_USER2''
) BEGIN
EXEC sp_revokedbaccess ''WEB_USER2''
END
')
EXEC ('USE ' + @database_name +'; EXEC sp_grantdbaccess ''WEB_USER2''; ')
-- add user to db_datareader
EXEC ('USE ' + @database_name +'; EXEC sp_addrolemember ''db_datareader'', ''WEB_USER2''; ')
-- add user to db_datawriter
EXEC ('USE ' + @database_name +'; EXEC sp_addrolemember ''db_datawriter'', ''WEB_USER2''; ')
-- grant permission on Stored proc, Scalar function, Inlined table-function, Table function, View
-- !! coz EXEC is a self-contained batch, so must use GLOBAL
EXEC('USE ' + @database_name +';
DECLARE obj_cur CURSOR GLOBAL FAST_FORWARD FOR
SELECT [name], [type] FROM sysobjects
WHERE [type] IN (''P'', ''FN'', ''IF'', ''TF'', ''V'')
')
OPEN obj_cur
WHILE 1 = 1
BEGIN
FETCH obj_cur INTO @object_name, @object_type
IF (@@FETCH_STATUS <> 0) BREAK
-- PRINT 'object=' + @object_name + '; type=' + @object_type
IF LTRIM(RTRIM(@object_type)) = 'P' OR @object_type = 'FN'
BEGIN
-- EXEC on Stored proc, Scalar function
EXEC('USE ' + @database_name +'; GRANT EXEC ON dbo.' + @object_name + ' TO WEB_USER2 ')
END
ELSE
BEGIN
-- SELECT ON table function, View
EXEC('USE ' + @database_name +'; GRANT SELECT ON dbo.' + @object_name + ' TO WEB_USER2 ')
END
END
CLOSE obj_cur
DEALLOCATE obj_cur
END
CLOSE db_cur
DEALLOCATE db_cur

DROP TABLE #databases





plz revise it if you have better idea! Thx!

How To Eliminate Login Screen When I Try To Access Report Via URL

I am trying to access a report via url

http://69.23.3.112/reportserver?/rptProject/rptStatus&eStatus=All&eUser=2

it always asks for username and password.

All my users login to my project which is asp.net 1.1(vs2003) based project, now from inside the project, if they try to access any report from (which is on framework 2.0), they have to go through a autentication screen which is related to sql server reporting services.

can you please help, how to override this login screen.

Thank you very much for the information.

Last Access Time

How can i find databases' last access times in server

Windows App Cannot Access SQL Server Express DB (login Failure)

I am using an unattended install script to install the database on the client machine. All the settings look correct. But my application cannot access the db -



Error: Cannot open database requested by the login.



Now when I install SQL Server Express manually, it works fine. I have the service running under local system and enabled user instances is true. I cannot figure out the problem(been working on it for 2 days).



Any ideas?

Access Permissions On Server Scoped Objects For Login

We are having problems with the response times from UPS WorldShip after switching from SQL Server 2000 to 2005.


I think that the problem can be fixed from the database end by setting the permissions correctly for the user/role/schema that is being used by WorldShip to connect to the server but, I'm not sure how to do it.

The Setup

Client
UPS WorldShip 8.0 running on XP Pro SP2
Connecting via Sql Native Client via SQL Server Login
Connection is over a T1 via VPN

Server -
SQL Server Standard Edition on Windows Server 2003
2x3ghz Xeon processors w/ 4gb ram

The user that is being used to connect runs under it's own schema and role and only needs access to two tables in a specific database on the server.

What UPS WorldShip seems to be doing is on a continual basis retrieving information about the layout of the database via calls such as the following

exec [sys].sp_tables NULL,NULL,NULL,N'''VIEW''',@fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'CHECK_CONSTRAINTS',N'INFORMATION_SCHEMA',N'webservices',NULL,@fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'COLUMN_DOMAIN_USAGE',N'INFORMATION_SCHEMA',N'webservices',NULL,@fUsePattern=1

This seems to happen whenever WorldShip contacts the database to find out information in order to be able to create a mapping to the database as well as exporting information to it. Because of the VPN connection these calls take anywhere from 20 seconds to 3 minutes.

I am fairly confident that the problem lies with these calls to the database which I was able to capture using the SQL Server Profiler. We have experimented with the following setups.

1. Connecting to SQL 2000 over VPN with SQL Native Client - No noticeable lag
2. Connecting to SQL 2000 over VPN with SQL Server 2000 driver - No Noticable lag
3. Connecting to SQL 2005 locally with SQL Native Client - No Noticable lag
4. Connectiong to SQL 2005 over VPN with SQL Native Client - Lots of lag

Our network admin has been testing the network connections over the VPN and it is very responsive with none of the long wait times found when using UPS WorldShip.


Now for a possible solution other than getting UPS to fix their software. I think that by limiting the tables and views that the login is able to see will cut down significantly on the lag times that are being experienced. The problem is that there were 264 items that were being returned by sp_tables. I was able to cut that down to 154. I am unable to disable access to any of the rest of the items because they are server scoped.

Take for example the INFORMATION_SCHEMA.CHECK_CONSTRAINTS view. When I try to deny access to it in any way I get the following error:

Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master (Microsoft SQL Server, Error: 4629)


Am I able to deny access to these types of object and if so how? Also, what objects should be accessable such as sys.database_mirroring, sys.database_recovery_status, etc?

Is There Any Way To Know The Last Access Time Of A Table?

I have some junk tables in my database, but I am not sure if they are really not in use. Does sql server log the last time that a table gets accessed? when I say access I mean any actions toward the table, including select, update, insert, etc.thanks in advance.

Access Time In SQL Server

My manager has asked me to pull out the following info from the SQL Server.


Who access the system, How many times?
Average duration of access?
Average daily/weekly� use of the system?


I'm new to SQL Server and do not know, how to get these following things out of the system. Would anybody please advice me, how to get it. I would really apprecaite it.

Thanks a million for your help.

Sindi

Unable To Access Mdf(db) For The First Time

Hi All,
Which some 1 can help me this.
I've developed an application and everything work fine after deploy. But after restarted my PC, i'm unable to login to my application which tie to the xxx.mdf DB and prompt Database error with no description. Until when the i click second time, only i'm able to acess into it. What happened actually?

I'm using sql express as embedded db on my local pc. This is without any remoting access.

Error: Cannot Open Database Requested In Login 'projectAllocations'. Login Fails. Login Failed For User 'sa'.

Hi,
Im getting this error when attempting to retrieve data from an sql database.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.

Source Error:


Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword")
Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn)
Line 15: objConn.Open()
Line 16: objRdr = objCmd.ExecuteReader()
Line 17: While objRdr.Read()

Source File: C:finalyearproject2sample.aspx Line: 15



Please Help!! Im a beginner to this, so if anyone knows the answer, take baby steps when explaining. Thanks

Cannot Open Database Requested In Login 'dbName'. Login Fails. Login Failed For User 'machineNameASPNET'

Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.

Login Issue When Aspx Pages On IIS Attempt To Access Msde

I am new to asp.net programing and am trying to put a basic secured login page on out IIS server to test, etc... Using the wbe matrix tool I was able to sucessfully login, and access avrious database tables without a problem, however on the IIS box I receive the following error when i attempt to access pages that use the database.

Login failed for user 'NT AUTHORITYNETWORK SERVICE'.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITYNETWORK SERVICE'.

Source Error:


Line 48:
Line 49: Dim ds As New DataSet()
Line 50: myCommand.Fill(ds)
Line 51:
Line 52: MasterGrid.DataSource = ds


Source File: C:InetpubwwwrootaspDefault.aspx Line: 50

Stack Trace:


[SqlException: Login failed for user 'NT AUTHORITYNETWORK SERVICE'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
ASP.Default_aspx.BindMasterGrid() in C:InetpubwwwrootaspDefault.aspx:50
ASP.Default_aspx.Page_Load(Object Sender, EventArgs E) in C:InetpubwwwrootaspDefault.aspx:14
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032

All asp & aspx pages that do not reference a database work fine. I have ready many articles about simialr problems, and it appears that I either need to cerate a aspnet user or add the nt authority as a database user. Can someone point me in the irght direction, and how to get this working?

Thanks much!

Integration Services :: Login To SSMS Got Access Denied Error

When I try to connect to "Integration services" in "SSMS" with my window authentication, for some servers can able to connect. But for some server i am getting error like "Access denied".

To fix these Access Denied, as per instruction from google, i have configured the Administrative Tools-->Component Services->Component Services-->Computer-->My Computer-->DCOM Config-> Microsoft SQL Server Integration Services 11.0--> Right
Click-->Properties-->navigate to “Security”--> then adding the Active direcotry login in the "Launch and Activation Permissions", "Access Permissions" and "Configuration Permissions".

Then this issue was fixed. I want to know why this was happening to some servers only, what is the reason behind this.

Data Access :: Login Failed For User When Run Application In Network

When i wanna run Application in other computers in network. The following error occurs.

error : System.Data.SqlClient.SqlException: Login failed for user 'Suren-PCGuest'.ConnectionStr = "Data Source=SUREN-PCMSSQLSERVER,1433;Initial Catalog=WorkFlow;Integrated Security=True";

Reporting Services :: Can Control Access Of Parameters From Login Accounts?

I am just wondering if i can control the access of range of variables from users' logins.

To be more specific, if i a parameter alpha has five values (1,2,3,4,5) and then i have 3 users A, B C, what i need is:

1. A can access Vaues(1, 3)
2. B can access Values (2, 4)
3. C can access Values(3,5)

Is there anyway i can do this either from the stored procedure i am getting the data or i can do it on SSIS development side?