Hi,
I have a problem.While creating login for a single table in a database we know the public role is given by default.
I have use to disabled all public priviledges by-
Use northwind
revoke all from public
executing through query Analyser.
And after creating a role named "reception" and giving the priviledges of "select' to customer table by ticking and assigning that role to the login I get my login.
But using that login When I login,I can use select to see customer table.
And except that nothing is allowed.
But when i write
use pubs
select * from sales
I get the entire results.
Plz suggest me a way to block everything except the "select" of customer table.
Thanks!!
Joydeep
Hello, We have 700+ databases on SQL server 2005, and we constantly adding more db.. I know, this is not the best... I have the same group of people who needs to have identical permissions on all existing and newly created databases... How can I acchieve that in efficient way? Ideally I would like to create new database role which has read and write access to all objects in all databases, and add users to this new role. Do those users need to be added to each database? How can I assure that new database will have role and users already added after the creation? Can I add it to the model and all other dbs will have it?
I am download backup of SQL database from server and I am restore database successful into my SQL Server Management Studio Express, but near name of database stay: (Restricted User) note!When I try use database offline for login into my control panel created by me it not work. After I create small new database with same tables login work! I am admin on my local PC. Any ideas why my restored database has note: "Restricted User"? This is bigger database and looks complicate transfer data from old to new database, but if someone have these examples it can help too
I appear to be doing something wrong. When I set my Table for Unrestricted File Growth and save it, When I go back and look at the settings it is set back to restricted file growth.
It seems to me if you set any settings and apply and/or save it. Should it not keep that setting.
We have "unrestricted" size of log file on model database, but for some reason, all newly created databases have "restricted" sizing.
After taking a look at this, it appears that SQL Server changes the "Unrestricted" file growth setting specified on the log file in the model database to "restricted to 2 TB". This size is the maximum allowed for log files by SQL Server 2005, so this means the settings are identical?
Scenario: SQL Server 2005 SP2. SQL Server has only Windows Authentication Mode. SQL server 'xp_cmdshell' option has been disabled by surface area configuration. A database 'CN01' has been created I have created a schema 'Executables' that contains stored procedures to access data in 'CN01'. I have create a database role 'db_procexecutor' with execution permission on schema 'Executables'. I have created a group 'CN01-Users' in Active Directory. I have created a login 'CN01-Users' wich has been asigned 'db_procexecutor' role in 'CN01' database.
Problem: I want a user member of AD group 'CN01-Users' to execute a Stored Procedure in 'CN01' database, (in 'Executables' schema) wich needs access to 'xp_cmdshell' to write a file in OS through bcp command. The following SP works only assigning 'sysadmin' role to 'CN01-Users'. For security reasons, I don't want to assign 'sysadmin' role to 'CN01-Users', neither assign permissions on Master. I want the SP to work in the security scenario described earlier. Should I use procedure signing ? ... If so, please describe step by step.
Sample SP: EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE WITH OVERRIDE exec xp_cmdshell 'bcp "SELECT Column FROM CN01.dbo.table" queryout C: able.txt -T -c' EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE
Just released small piece of software with SQL Compact Edition, version 3, dll files distributed with the application.
Now feedback from 2 people suffering an error message as follows: SQL Mobile usage is restricted on this platform. To use SQL Mobile, you must install SQL Server 2005, Visual Studio 2005, or the Tablet PC SKU.
Both using XP under Admin accounts.
My own testing did throw up this error prior to release, but only when I tried to run the program as a Limited User. I was unable to find out why. Running the program under an Administrator account never produced this error message.
The test-bed was a fresh install of XP, SP 2, running within VMware. An Administrator account was set up. No development software was installed, nor anything to do with SQL. I tried to make it a clean environment to be used by a non-technical user.
Access 97 SR 2 Front EndSQL Server 7.00.1063 Back EndWhen I attempt to relink the tables in my Access app. I am suddenlygetting this weird error message = "ODBC--Call Failed", followed by"[Microsoft][ODBC SQL Server Driver]Restricted Data Type AttributeViolation(#0)". This problem did not manifest last time I tried a fewweeks ago, and so far as I know there have been no software changessuch as Service Packs etc.The error occurs when attempting to link a view. Here is the scriptfor the tables and the view siipl_DoesNotWork:==================================CREATE TABLE [dbo].[tblBOMEntries] ([fldID] [int] IDENTITY (1, 1) NOT NULL ,[fldCMMPartID] [int] NULL ,[fldSIPartNo] [varchar] (20) NULL ,[fldDescription] [varchar] (50) NULL ,[fldQtyAssy] [int] NULL ,[fldItemNo] [int] NULL ,[fldOnIPL] [bit] NOT NULL ,[fldRefDesig] [text] NULL ,[fldParentID] [int] NULL ,[fldBOMLevel] [int] NULL ,[sys_ts] [timestamp] NULL ,[fldObsDate] [varchar] (15) NULL ,[fldEffDate] [varchar] (15) NULL ,[fldItemRevNo] [int] NULL ,[fldModNo] [varchar] (10) NULL ,[fldNewBOMFlag] [smallint] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TABLE [dbo].[tblCMMIndex] ([fldCMMPartID] [int] IDENTITY (1, 1) NOT NULL ,[fldMainDescription] [varchar] (50) NULL ,[fldSIPartNo] [varchar] (50) NULL ,[fldBOMRevision] [varchar] (5) NULL ,[fldBOMAttached] [bit] NOT NULL ,[fldBOMDate] [datetime] NULL ,[fldCMMID] [int] NULL ,[sys_ts] [timestamp] NULL ,[fldLink] [int] NULL ,[fldNewBOMRevision] [varchar] (5) NULL ,[fldNewBOMAttached] [bit] NOT NULL ,[fldNewBOMDate] [datetime] NULL ,[fldSiteID] [int] NULL) ON [PRIMARY]GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOsetuser N'siipl'GOCREATE VIEW siipl.DoesNotWorkASSELECT dbo.tblBOMEntries.*,dbo.tblCMMIndex.fldCMMPartID AS Expr1FROM dbo.tblCMMIndex INNER JOINdbo.tblBOMEntries ONdbo.tblCMMIndex.fldCMMPartID = dbo.tblBOMEntries.fldCMMPartID================================However, if I create a new view based on exactly the same SQL asDoesNotWork I don't get the same error message:CREATE VIEW dbo.DoesWorkASSELECT dbo.tblBOMEntries.*, dbo.tblCMMIndex.fldCMMPartID AS Expr1FROM dbo.tblCMMIndex INNER JOINdbo.tblBOMEntries ONdbo.tblCMMIndex.fldCMMPartID = dbo.tblBOMEntries.fldCMMPartIDThe only difference that I can deduce between siipl.DoesNotWork anddbo.DoesWork is the ownership. Can that be the problem? If so, why?TIAEdward
How would I hide the sa account to be accessible under Logins for a restricted User in sql server 2005 and also other folders like Replication, Managment, etc need to be hidden too.
I restored a sql server database today. There is a problem though. In SQL server, right next to the name of the database are the words "Restricted User". Their seem to be some limitations on my access to this database now as I am not able to run some of my C# code against the database. How can I fix this in SQL server so that I am not a Restriced user.
Example of what I am talking about. In SQL server my database is named Gorlaz. Just to the left of this is a yellow Icon that represents the database "Gorlaz". For illustration purposes I will use the character "$" to substitute for the yellow database icon. So this is how it reads in SQL server management studio. $Gorlaz(Restricted User)
Secondary replica database(setup in async mode) of AlwaysON went in "restricted mode" during weekly reindex operation.
So I have tried below steps
1) Executed following statement on the same secondary replica database where the issue exists
alter database <DBNAME> set multi_user with rollback immediate
but it failed with the error saying "The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. ALTER DATABASE statement failed."
2) Primary database is multi_user but still tried following command on primary replia database(thinking it will replicate)
alter database <DBNAME> set multi_user
but no luck. The secondary alwaysON database shows (synchronizing) as the alwaysON is set in async mode but the command doesn't replicate across secondary
so we are left with the only option to re-setup alwaysON but I want to avoid it as database size is huge..
We're running into an issue where analysts are having problems obtaining lift charts (via the Mining Accuracy Chart UI available in the Visual Studio Analysis Services project) and performing prediction (via the Mining Model Prediction UI).
The issue seems to be related to the underlying analyst security model. Note that this post is related to:
Analysts that work on the same problem will only have access to:
- A sandbox relational database (which contains views into the same source database). The analyst is db_owner of the sandbox database, so she/he can create data transformations required, etc. The sandbox database contains views to the source database, but the analyst only has read-access to the specific data elements needed from the source DB. So, they are very restricted w.r.t. the source database, but are db_owners of their sandbox relational databases. Note that the analyst will connect to he database via Windows Authentication.
- An Analysis Services sandbox database to use for their modeling, etc. In this AS sandbox db, we've created a role called "Administrator" and checked the permissions: Full control (Administrator), Process database, and Read definition. The analyst's windows account is the "user" associated with this role.
Also, in this situation, the SQL Server 2005 Relational Engine and Analysis Services are running on a single machine. The goal of this security model is to provide analysts with the ability to work in their "workspaces" (both SQL and AS), but not to see other analysts work, etc.
Under this model, Analysts are able to deploy mining models when the Data Source object that points to their relational "sandbox" DB is set-up with "Impersonation Information" = "Use a specific user name and password", where the Analyst provides their domain account information.
But, when trying to build a lift chart using the same data source view objects that were used to successfully train the model, the following error is occurring consistently:
Window Title: "Loading Mining Accuracy Chart" Window Text: "Failed to execute the query due to the following error: Execution of the managed stored rocedure GenerateLiftTableUsingDatasource failed with the following error: Exception has been thrown by the target of invocation. Either '<domain><login>' user does not have permission to access the '' object, or the object does not exist. Errors in the high-level relational engine. A connection could not be made to the data source specified in the query. Errors in the high-level relational engine. A connection could not be made to the data source specified in the query.."
Since the Analyst was able to build the model with her/his given '<domain><login>' credentials, it is puzzling why the lift chart is failing.
Hello again,and thanx again -I now found something unexpected - that hopefully is the cause of myproblem? :In Man. studio security anmeldungen (engl. login names or users oraccounts or so?) right clicking on any user and properties, status
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()
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.
I am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
Greetings,VWD EE and other Tools do not have problems working O.K. on my machine, when I am logged-on as restricted(limited) user, both environment and local web server are functioning, and it was MS effort to do it right.BUT the Problem is, when I try to connect to database->new DB connection(either through vwd or management studio etc). I get an Error - which should be written to event log (according to msg) but it isn't - that I can't connect. I am sure that it is because NO SQL SERVER2005EXPRESS instance is running, which I think is the only prerequisite to have it work (I do not need sqlbrowser service I am doint local development).SQL2005express service is configured on MANUAL start. I can start it as administrator through SQL config manager, but it is not convienient and what I want. I need to start it ONLY as a developer user, I do not want it to be running all-the-time for everyuser using computer. It was congigured as Network Service logon, I tried Local Service logon, and I even tried configuring it to logon as -my developer user- account, e.g. with limited user name and his password.In every case I can't start the service as member of users group and this developer. Then I added limited user to SQL2005EXPRESS group. Still NO help, won't start either.My question is, If I want to start developing ADO2.NET application and I need to have running SQL2005Express instance as a develper, how can I start it? I think I do not have some rights to masterDB or something. Do it allways need to be running when computer starts? Isn't there any other way, to start it JUST when I think I start developing?I know of user-mode of accessing SQL2005express DBs, but it also assumes that SQL2005Express service is already running, what I am trying to prevent. I do not want to have it running for everyone who uses computer, just for someone who neeeds it.Any help explaining me the right way HOW TO SETUP WORKING ENVIRONMENT when developing as limited user welcome, I read the the documentations and haven't found the answers.THANK YOU !
hi, so i have a new box and I'm trying to get my websites and SQL Server 2005 Standard Edition working on it, but the pages give me the following error when I try to load them: "Cannot Open Database "XXXX" requested by login. The login failed. Login failed for user 'xxx'" Everything seems exactly the same settings and user-wise from my old box to my new one, but nevertheless everything I've tried gives me the same error. I've tried creating new users in SQL Server and giving them appropriate permissions to my database. I've even tried just using the built in 'sa' account. Nothing seems to change the error, except when I give it the incorrect password then it just says 'login failed' This leads me to believe that i'm successfully logging into the SQL Server, but it doesn't want to give me access to the database I'm requesting access too. But "apparently" the account i'm using should have access to the database. If nothing else the 'sa' account should, but that didn't work either. I'm stumped. Any ideas?
Hi I have asp.net2 app using SQLExpress which works fine in VS2005. When I compile it and try to run it from IIS on the same machine using the same sql server database with: connectionstring="server=.SQLEXPRESS;Database=abc.mdf;Trusted_Connection=yes"/> I get the following error. Cannot open database abc.mdf requested by login. The login failed. Login failed for user 'machinenameASPNET' Any help much appreciated
I have a script component. What it does, it queries the a table using a connectionstring assigned to it in the Connection Managers Editor (which is an ado.net adapter). this works fine when i'm using a windows login in the ado.net connection manager. But when i changed my connection to use SQL server login, I encounter this error:
OnError,,,Add new records to Dim_T_Status (Case),,,10/14/2007 5:54:47 PM,10/14/2007 5:54:47 PM,-1073450910,0x,System.Data.SqlClient.SqlException: Login failed for user 'CS_REPORT'. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket) OnError,,,LOAD AND UPDATE OCEAN Dimension Tables,,,10/14/2007 5:54:47 PM,10/14/2007 5:54:47 PM,-1073450910,0x,System.Data.SqlClient.SqlException: Login failed for user 'CS_REPORT'. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket) OnError,,,LoadOCEANDimensions,,,10/14/2007 5:54:47 PM,10/14/2007 5:54:47 PM,-1073450910,0x,System.Data.SqlClient.SqlException: Login failed for user 'CS_REPORT'. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
Cannot open database "QuoteSystem" requested by the login. The login failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. Last time I recieved this error I had to add ASPNet, NTAuthority, and myWebSiteUsers USERS under the database properties-->permissions setting for the specific database I am trying to access with the website but this database(QuoteSystem) does not lists any of these objects so I can add them. Does anybody know what I have to do to fix this? I am using SQL Server 2005.
Please, can anyone tell me why I am getting the undermentioned error message when I develop my application as an IIS Site in Visual Web Developer 2005 Express ? I have already developed and successfully tested it as a 'FileSystem Web Site' .I am using SQLSErver Express 2005 edition and have which I have installed along with the Northwind database as per the download instructions. My IIS software is version 5.1 which I have checked and it is configured to allow Integrated Windows Authentication. the relevant code is (1) web.config file connection strings <connectionStrings> <add name="NorthwindConnectionString" connectionString="Data Source=.sqlexpress;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings>(2) the grid view control and sqldatasource <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" InsertVisible="False" ReadOnly="True" SortExpression="CategoryID" /> <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" SortExpression="CategoryName" /> <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]"> </asp:SqlDataSource>This is the full error message and details: Server Error in '/sqlservertest' Application.
Cannot open database "Northwind" requested by the login. The login failed.Login failed for user 'JERRY-3C9615BAAASPNET'. 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 "Northwind" requested by the login. The login failed.Login failed for user 'JERRY-3C9615BAAASPNET'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
All the logins with sysadmin role are locked out in one of the SQL server. Also in that instance Logon trigger is implemented which allows only certain users with Windows Authentication to logon.
This server is converted into single user mode by using command prompt. Now we are attempting to connect to SQLServer using command sqlcmd -S FTCPU3239 (since we do not have any other login) and create a new login with sysadmin role but are refused due to logon trigger. it says Login failed for login 'DomainNameUserName' due to trigger execution.
I have a server that has 20 databases . I have tested with few users with different level of access and all of them were able to connect to the server and also see, select, update , delete from a particular database which is kind of weird because they do not have a user login associated or mapped to that database. I checked and no user is part of any group in AD that would give them permission to connect . I need a query that would find the permission path of a user. I already queried with xp_logininfo but I am not getting any thing.
Hi all, Iam getting this error System.Data.SqlClient.SqlException: Cannot open database "XYZ" requested by the login. The login failed.Login failed for user 'xyz-abcASPNET'. when trying to open the page.... http://localhost:1807/projectname/WebFormName.aspx as http://localhost/projectname/WebFormName.aspx Couldnt figure out the solution.Please help Soujanya
Hi, I just installed Visual web developper 2008 express with all options including SQL srvr express 2005!
This is from default iso image on the official website.
I create my first project and when I want to do ASP.NET configuration from web site menu, i got this error:
"Cannot open database "aspnetdb" requested by the login. The login failed."
I am just trying to learn here so i even did not create any dB or something, it is 1st time I run configuration to add security roles and users for testing!
I also un-installed all and re-installed, still the same...
Error!!! 1>Cannot open database "intranet" requested by the login. The login failed.Login failed for user 'RAGHAVEN-71AF9BASPNET'. Error!!! 2> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) These both errors r coming simultaneously when start to debug only when i tried to retrive data from database , from past two days not able to work on database,plz help me in solving this ,i tried everything what i know and even on experts advice from recommended websites, forums and on google search..
I receive this error message occasionally in my ETL - usually when trying to pull data from our source system:
An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in login response". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: Timeout error [258]. ". There is a setting in the connections manager, under the ALL tab, Initialization, Connect Timeout. It is set to zero, which would be the default. It appears (by looking at the timestamps in my error log) that the default is 15 seconds. If I change this to a higher value (60 seconds or so) should this fix the error?
