Logging With Another System User Cannot Access My Database
Mar 3, 2008
Hi,
I am using SQL Server Express Edition 2005 as a backend database working with Visual Basic 2005.
I am using Vista and having two users to access to my computer. User-1 and User-2.
I created a database in User-1 and works fine Visual Basic 2005.
Now the problem is when i login to my computer system with User-2. I cannot able to access the database with encountering error like "User-1/SQLExpress". I know that i cannot able to access to database which was created in User-1.
Do you any solution to this problem. when i login with user-1 and user-2 it should able to access database.
Thanks.
Regards
Kashif Chotu
View 1 Replies
ADVERTISEMENT
Jan 31, 2008
Hi,
How would I set permission for SQL Server 2005 "User A" to prevent access to System and other user databases, also How to hide the databases that "User A" has no rights to. I mean, when User A logs in, All other user databases are not visible to him/her.
Thanks,
View 6 Replies
View Related
Mar 6, 2014
Is there any way sa user can access folder located in local system.
Server Info:
SQL Server 2008 r2
OS: Windows Server 2008
View 1 Replies
View Related
Aug 25, 2014
I am not able to launch the Kerberos Configuration Manager due to the above error.
View 4 Replies
View Related
Sep 22, 2015
disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?
View 5 Replies
View Related
Dec 10, 2007
While attempting to set up sql replication in MSSQL 2005 one of my user databases is now in the systems database folder. I need to move it back to the user databases folder. Any help would be greatly appreciated.
View 16 Replies
View Related
Mar 25, 2008
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
View 6 Replies
View Related
Jul 13, 2004
I am trying to import tables from my access database.
1st trying simple table, In ent mgr i right clicked the db folder
all task
import data
data source : Microsoft access
file name : c:Data Management SystemDMS.mdb
User name : mike (same used for lonin in access)
password : (same used for lonin in access)
advanced button:
jet Oledb: System database c:Data Management SystemSecured1.mdw
windows authentication
server (local)
database New -> dms
copy tables
now i see the items in my access database so i am guessing every step so far was correct
hey it worked !
ha,, hope u dont mind if i leave this here for anyone else, i ansd my own ?
View 2 Replies
View Related
Jun 2, 2015
We created one SA login [login name: test_db] and give the Db_owner permission for particular user db, but this test_db login able to access the all system database except model database.Here problem is I am giving the only one particular user database but that login why able to access the system database.Here how to stop the system database access…
View 8 Replies
View Related
Mar 21, 2006
My question takes two parts; firstly, is the new table that I'm proposing going to handle the business logic I describe below, and secondly, if I put the new table in, how in hell do I use it?
Right; present schema attached.
The idea, which I hope is fairly clear from the schema, is that you send it a buch of parameters about the event, admission date, etc, and it will return all tickets matching those parameters. An example stored proc for this is below:
CREATE PROCEDURE [dbo].[getSingleTicketsByParameters]
@eventIdINT,
@standIdINT,
@admissionDateIdINT,
@bookingDateIdINT,
@concessionIdINT,
@bookingMinQuantityIdINT,
@bookingMaxQuantityIdINT,
@membershipIdINT
AS
SET NOCOUNT ON
SELECT
[tblTickets].[id],
[tblTickets].[booking_date_id],
[tblTickets].[booking_min_quantity_id],
[tblTickets].[booking_max_quantity_id],
[tblTickets].[ticket_concession_id],
[tblTickets].[membership_id],
[tblTickets].[event_id],
[tblTickets].[stand_id],
[tblTickets].[admission_date_id],
[tblTickets].[price],
[tblTickets].[availability],
[tblTickets].[description],
[tblTickets].[admin_description],
[tblTickets].[ticket_open],
[tblTickets].[ticket_live],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date],
[tblTicketConcessions].[concession_name],
[tblBookingMinQuantities].[booking_quantity],
[tblBookingMaxQuantities].[booking_quantity],
[tblAdmissionDates].[description],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblMemberships].[membership_name]
FROM [tblTickets]
LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantities ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
WHERE 1=1
AND ([tblEvents].[id]=@eventId OR @eventId=0)
AND ([tblStands].[id]=@standId OR @standId=0)
AND ([tblTicketConcessions].[id]=@concessionId OR @concessionId=0)
AND ([tblAdmissionDates].[id]=@admissionDateId OR @admissionDateId=0)
AND ([tblBookingDates].[id]=@bookingDateId OR @bookingDateId=0)
AND ([tblBookingMinQuantities].[id]=@bookingMinQuantityId OR @bookingMinQuantityId=0)
AND ([tblBookingMaxQuantities].[id]=@bookingMaxQuantityId OR @bookingMaxQuantityId=0)
AND ([tblMemberships].[id]=@membershipId OR @membershipId=0)
GO
So. It's all about to get horribly, horribly complex (well, it is to me) so take a deep breath.
Tickets are subject to quotas. However, quotas are subject to... well, at the moment they can be based on event, stand, admission date, concession, or any combination of these. They can also be based on an individual ticket. All quotas, however, are annual; they only apply to ticket purchases in the same year. For example:
- you can't buy more than 4 tickets for date A, in stand B at event C, per year.
- you can't buy more than 2 tickets for stand D per year.
- you can't buy more than 4 of ticket number 123.
Now, I'm thinking that all I need to manage this is one table, and it's going to look a little like this:
tblQuotas
id INT PK
ticket_id INT FK
event_id INT FK
stand_id INT FK
admission_date_id INT FK
quota INT
So, if I put a record in there with an event, stand and admission date - and a quota - then I've met the first business rule that I described above. If I put in a record with just a stand id and a quota, then I've met the second sort. If I put in one with just a ticket id and a quota, then I've met the third.
Now we return to that big SQL statement above. The one that says "get me all eligible tickets that match these parameters". And it's got to get a lot bigger because I now need to not only join in tblQuotas, to see if any quotas apply to the ticket I've chosen (or to the event, stand, etc that make it up), but I've also got to join in tblBasket, and tblOrders, and tblUsers, to find out how much of any particular quota they've already used up in previous orders. Although that's only orders placed in the current year, mind. And of course I also now haveto pass the users ID in as a parameter so I can look up their order history.
Your head's hurting too, right?
So... this is where I've got to:
SELECT SUM(ticket_quantity) FROM [tblBasket]
INNER JOIN [tblTickets] ON [tblBasket].[ticket_id] = [tblTickets].[id]
INNER JOIN [tblOrders] ON [tblBasket].[order_id] = [tblOrders].[id]
WHERE
[tblTickets].[stand_id] = @standId
AND [tblOrders].[user_id] = @userId
AND ([tblOrders].[order_date] BETWEEN '2006/01/01' AND '2006/12/31')
What I want to do is incorporate that into the big SQL query up top, in such a way to make it only return tickets that not only match the ticket parameters but that also aren't linked to stands, admission dates or anything else, that the user has reached their quota on.
Oh, and I'm in a bit of a hurry so do try and get a move on won't you? ;)
But seriously - how do I put those two SQL querys together? Do I need one for each paramater that might have a quota attached, or is there a quicker way? All suggestions and advice, up to and including "get an easier job, dude", received gratefully :)
View 2 Replies
View Related
Aug 9, 2007
MS SQL Server 2005 Express.
I'm trying to connect to Access DB (having System Database) via OPENROWSET.
Everything (client, server and access file) is on local drive.
This works (ODBC):
select *
from openrowset('MSDASQL',
'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:MBK.mdb;SystemDB=C:SECURED.MDW;Uid=me;Pwd=pw;',
'select * from [Mbk]')
This works (Jet.OLEDB):
select *
from opendatasource('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:MBK.mdb;Jet OLEDBystem Database=C:SECURED.MDW;User ID=me;Password=pw;')
...Mbk
This won't work (Jet.OLEDB):
select *
from openrowset('Microsoft.Jet.OLEDB.4.0',
'MS Access;Database=C:MBK.mdb;System Database=C:SECURED.MDW;UID=me;PWD=pw;',
'select * from [Mbk]')
saying ... "Wrong argument".
This won't work (Jet.OLEDB):
select *
from openrowset('Microsoft.Jet.OLEDB.4.0',
'MS Access;Database=C:MBK.mdb;SystemDB=C:SECURED.MDW;UID=me;PWD=pw;',
'select * from [Mbk]')
saying ... "There are no permissions for usage of object C:MBK.mdb". It seems that it simply hasn't found system database file C:SECURED.MDW, cause when I change SystemDB=C:SECURED.MDW to something like BlahBlahBlah=C:SECURED.MDW the same message is shown.
So, what is the right syntax for stating System Database in OPENROWSET query string? And why 'System Database' won't work?
Thank you.
View 1 Replies
View Related
Nov 15, 2007
Any help in understanding and working around this odd behavior is greatly appreciated!
I have the default SQL database logging enabled (to sysdtslog90).
I start my package with an ExecuteSQL task that writes most of the system variables to an execution log.
Included is the value of the System::ExecutionInstanceGUID variable (which I thought should match the standard SSIS logging executionid value).
I have an error handling ExecuteSQL task that updates my execution log with an end time and the task that failed.
This is right out of the ProjectREAL audit approach.
Problem with the Error ExecuteSQL update is that it is trying to match the SSIS log but cannot.
This is the SQL being used:
select top 1 @failureTask = source
from dbo.sysdtslog90
where executionid = @executionGuid
and (upper(event) = 'ONERROR')
and upper(source) <> @packageName
order by endtime desc
When I enable OnInformation event logging something interesting appears.
The ExecutionGUID changes during validation of my one Data Flow Task in the package YET the system variable does not get updated with the new value!!!!
Log Excerpt:
1 OnInformation DFT Load Customer Test B94FDA4C-46D9-4193-BD87-12CFCB31EA1A Validation phase is beginning.
2 OnInformation DFT Load Customer Test 282F7D2E-329A-49DB-90B6-838053114940 Validation phase is beginning.
3 PackageStart AuditSample 282F7D2E-329A-49DB-90B6-838053114940 Beginning of package execution.
4 OnPreExecute AuditSample 282F7D2E-329A-49DB-90B6-838053114940
5 OnPreExecute SCR Set Correct Execution GUID 282F7D2E-329A-49DB-90B6-838053114940
6 OnVariableValueChanged AuditSample 282F7D2E-329A-49DB-90B6-838053114940 ExecutionGUID
7 OnInformation SCR Set Correct Execution GUID 282F7D2E-329A-49DB-90B6-838053114940 System Variable - Package Name AuditSample Start Time 11/14/2007 8:56:26 PM ExecutionGuid b94fda4c-46d9-4193-bd87-12cfcb31ea1a
Please also reference http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=420279&SiteID=1
View 10 Replies
View Related
Aug 12, 2015
Need to create a user in sql server provide grants to few system tables to the above user.
View 10 Replies
View Related
Feb 15, 2006
Hi,
I've come up against a problem in my error handling.
I have a package-level OnError handler which is a SQL Execute task. The SQL execute task constructs a SqlStatementSource expression to insert the error details into a table. This works fine and I've tested it by introducing various errors in the package.
I have another SQL Execute task, which executes a SQL script from a file connection. This is a very long script (100s of lines) and works fine. I introduced a deliberate error into it and got the following errors:
Error: The variable System::ErrorDescription contains a string that exceeds the maximum allowed length of 4000 characters.
Error: Reading the variable "System::ErrorDescription" failed with error code 0xC0047100.
Error: The expression <expression> on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
The problem is obviously the length of the script, since SSIS attempts to copy all of it into the ErrorDescription, but this seems to occur before I can do anything about it. I've tried putting a SUBSTRING function around the ErrorDescription variable without success. If I introduce an error into a shorter script the error handling works fine.
Is this a bug in SSIS? Is there any sort of workround that anybody knows of?
thanks
- Jerzy
View 3 Replies
View Related
Oct 18, 2013
I have system database and user database file are present in G,H and W drive.The process is going to be - copy data from G to S, H to T, W to U. Rename G to X, H to Y and W to Z. Rename S to G, T to H and U to W. Reboot the servers. The original G, H and W will then be X, Y and Z. The old S will be the new G, old T will be H and old U will be W. My question is that after doing this whether my SQL server will start or not
View 8 Replies
View Related
Apr 20, 2015
We run std 2008 r2. When I deploy and run a pkg from the catalog, how can I get that flat file system log we always instructed ssis to write to when we ran from the command line? I believe it was the /L param . Not sure at this point if i'll use sql agent or somehow employ task scheduler to kick off the pkg.
View 2 Replies
View Related
Jul 7, 2000
When I try to give a login access to a database I get the message:
Error 21002: [SQL-DMO] users already exist in the database.
I checked sysusers for the database and the users name is in there, even if the user has no login.
Is it okay to just delete the record that has the users name from sysusers?
Thanks
View 1 Replies
View Related
Jun 11, 2007
Downloaded and install SQL 2005 on 2003 member server. Downloaded and ran SQLServerSamples.msi, AdventureWorksBI.msi, AdventureWorksDB.msi, and AdventureWorksLT.msi.
When I go into Sql Server Management Studio I cannot find the Adventure Works database under databases. How do I access it?
Thank you.
View 3 Replies
View Related
Sep 29, 2007
Hi All,
My question is regarding SQL Server database security. I want to create a login using SQL Server Authentication and assign it db_owner rights for my database. So far so good. But the critical part is that I want to give exclusive rights for my database to this user only i.e. no other users (dbo, guest etc) should be allowed to access my database.
It will be good to present here the scenario which I need to implement. I am running an ASP.NET application that uses SQL server 2005 db at back end. The database server might have other databases as well but I don't want the administrator (either SQL server admin or the server administrator) to be able to get access to my database or even view the schema. I don't want any other user except my own user to be able to detach the database or perform backup or restore operations.
I hope I was able to deliver the requirement.
View 6 Replies
View Related
Sep 11, 2007
Please help....
I have designed an application that uses sql server 2005 express database. This application was intended to be installed and used on a local desktop machine. However, now this application needs to be also installed on several computers and still needs to use only 1 database. Part of the database saves local user settings, so what I have done thus far is use two connection strings: one for local database (because the application may be used as origionally intended) and one for remote database that everyone should be able to connect to.
When I remotely connect to the database, it works fine. However, it seems to be locked so when I chose the same database for another user I get the error message "This File Is In Use." I need to know how to unlock it and make it usable for several users at the same time.
Thank you for your help! Please let me know if you need more information or if something is unclear in my description.
View 6 Replies
View Related
Feb 10, 2014
I am newbie to SQL.I need to create an application will run on server, and of course will be installed by using admin user. I can use the install user to access to database on that server?
View 1 Replies
View Related
May 26, 2008
Hi friends,
I have created a database DB1 using CREATE DATABASE DB1 command. Then i created login name using CREATE LOGIN login1 WITH PASSWORD = 'password1' command and created user name using CREATE USER user1 FOR LOGIN login1 command. Now i have to assign the user1 to the database DB1.
Any one please tell me how to assign DB1 access privilege to user1?
Thanks in Advance
Sathish kumar D
View 1 Replies
View Related
Mar 17, 2008
Hi,
How do I create a user that has full access to a database using only SQL statements?
I've create very basic database called "sampledb".
Then I've done:
CREATE LOGIN sampleuser WITH PASSWORD = '123sampleuser', DEFAULT_DATABASE = sampledb;
USE sampledb;
CREATE sampleuser FOR LOGIN sampleuser WITH DEFAULT_SCHEMA = 'db_owner';
Then I logon to SQL server express 2005 management studio using this user, first of all I do not see the sampledb database listed under "databases" even though it is selected in the "available databases" toolbar (how do I list it there? Because going through "attach..." tells me I do not have the privileges to browse for the database to attach).
I then open up a new query (sampledb is selected), and type (products is one of the table in sampledb):
select * from products;
And I get:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Products', database 'sampledb', schema 'dbo'.
Shouldn't the chosen schema (db_owner) give me the right to do this? And how do I fix this from the query window (without going through the GUI, just using SQL statements)? And how do I list sampledb under the databases when I log in as this user?
Thank you!
-Yannick
View 1 Replies
View Related
Feb 27, 2006
Hey all,
I've got a third party app running atop my sql server instance. When auditing logons in profiler, I see that the user (sa) logs off, and then immediately logs back on. Interestingly, the app logs back on with this user again, and the instance gives it the same spid as it previously had.
Can anyone explain this behaviour, or point me to a place where I can investigate ?
Thanks,
-Kilka
View 2 Replies
View Related
May 14, 2014
I have a tables that looks like this:
TABLE1
COL1
COL2
LOG_TABLE
TABLE_NAME
USER_ID
SQL_STATEMENT
DATETIME
I need a trigger (I assume) to log any updates/inserts/truncate/deletes/etc. to any changes as well as their USER_ID that has been executed to TABLE1.
View 1 Replies
View Related
Oct 12, 2006
/* -------------------------------------------------
The Following Procedure Helps You To
Send a Messge to all Users
those Who Access a Specified Database in a Server
*/
Create Proc Sp_SendMsg
@Msg Varchar(100),
@DbName VarChar(100)
as
if db_id(@DbName) = Null
begin
Print 'DataBase dose not Exist'
end
else
Begin
Declare @spId Varchar(1000)
DECLARE TmpCursor CURSOR FOR
Select 'master..xp_CmdShell ''Net Send ' + rtrim(convert(Varchar(50), HostName))+' "Dear ' + upper(max(rtrim(loginame)))+ space(1) + @Msg + '"''' as spId
from master..SysProcesses
where db_Name(dbID) = @DbName and
spId <> @@SpId
and rtrim(ltrim(HostName)) <> ''
and HostName <> host_Name()
and dbID <> 0
and spId > 50
group by HostName
OPEN TmpCursor
FETCH NEXT FROM TmpCursor
INTO @spId
WHILE @@FETCH_STATUS = 0
BEGIN
Exec (@spId)
FETCH NEXT FROM TmpCursor
INTO @spId
END
CLOSE TmpCursor
DEALLOCATE TmpCursor
end
View 2 Replies
View Related
Aug 23, 2004
Dear All,
I want my SQL database to be accessed always in a single user mode through my application. How can I do this ?
Thanks in advance.
Sushma
View 5 Replies
View Related
Apr 22, 2014
Currently there are various teams accessing the database. For costing reasons, we need to track usage.Is there an efficient way to monitor User access to the database.Can we track which user has executed which query(SELECT,insert etc),the login time and such parammeters?
View 5 Replies
View Related
Apr 26, 2008
Hi all,
I have a SSIS package that copies data from one database, into another, makes a backup, and then FTP's up the backup to a web server. In Sql Server I've made a job to run the package and a Stored procedure to activate the job from an Access database front-end.
Can anyone tell me how I might return a success/failure message to my access user?
Thanks
Melt
View 2 Replies
View Related
May 17, 2006
Hi all,
Can someone explain it to me why I am getting the following error when I try to connect SQL server express with .NET 2.0?
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: Login failed for user ''. The user is not associated with a trusted SQL Server connection.Here is my code and i am using windows authentication:
<%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim connAkaki As SqlConnection Dim cmdSelectAuthers As SqlCommand Dim dtrAuthers As SqlDataReader connAkaki = New SqlConnection("Server=.SQLEXPRESS;database=akaki") connAkaki.Open() cmdSelectAuthers = New SqlCommand("select Firstname from UserTableTest", connAkaki) dtrAuthers= cmdSelectAuthers.ExecuteReader() While dtrAuthers.Read() Response.Write("<li>") Response.Write(dtrAuthers("Firstname")) End While dtrAuthers.Close() connAkaki.Close() %>
View 2 Replies
View Related
Sep 24, 2013
I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2]
SELECT
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]'
FROM sys.views;
View 5 Replies
View Related
Jul 28, 2015
How do I grant a user permissions to only one table in a database. How would it affect him using our Main App which is NAV with regards to his user's permission in NAV
View 2 Replies
View Related
Feb 7, 2008
I have a restriced user on SQL Server that is only permitted to creat a new database and manage it only. All other database are hidden to that user.
when that user login to SQL Server and create a database and try to change the default folder path for data and log files, gets an error,
The Server principal "User" is not able to access the database "model" under the current security context. (MSSQL Server, Error: 916)
Any idea???
Thanks,
View 7 Replies
View Related