.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

SQL Server : How to deny access?

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :Sql Server
 

Hi,

I want to ask how to deny users to delete tables or more specifically deny DML statements but allow DDL statements in database.

It's urgent.


Satya Prakash Jugran


View Complete Post


More Related Resource Links

Data Points: Deny Table Access to the Entity Framework Without Causing a Mutiny

  

Julie Lerman shows database administrators how to limit access to databases from the Entity Framework by allowing it to work only with views and stored procedures instead of tables-without impacting application code or alienating developers.

Julie Lerman

MSDN Magazine August 2010


SharePoint Portal Server 2001: Search and Access Disparate Data Repositories in Your Enterprise

  

The knowledge worker is greatly empowered if she is able to access information across the enterprise from a central access point. With the SharePoint Portal Server 2001 Search Service you can catalogue information stored in Exchange public folders, on the Web, in the file system, and even in Lotus Notes databases. This article discusses the use of ActiveX Data Objects and the Web-based Distributed Authoring and Versioning protocol for creating search solutions based on SharePoint Portal Server 2001.

Kayode Dada

MSDN Magazine April 2002


Upsize Your Database: Convert Your Microsoft Access Application to Take Advantage of SQL Server 7.0

  

What if you need to convert an existing Microsoft Access 97 database application into a true client-server application that is based on a SQL Server back end? If you know a little about Visual Basic and SQL Server, it's easy to make your app take advantage of the power and scalability provided by SQL Server 7.0. Using some concrete code examples, this article takes you step by step through converting the native Jet queries in your Access application into stored procedures and pass-through queries that SQL Server can use. You'll also learn how to pass on parameters when your client-server app calls these SQL Server stored procedures and queries.

Michael McManus

MSDN Magazine June 2000


Using Remote Data Access with SQL Server CE 2.0

  
Microsoft SQL Server CE edition is the database server built by Microsoft to run on mobile devices. Besides being a standalone database for mobile applications, SQL Server CE also allows you to connect to your desktop SQL Server 2000 and perform remote data access and merge replication. In this article, you will learn how to build a .NET Compact Framework mobile application using Visual Studio .NET 2003 and how it can perform remote data access using SQL Server CE 2.0.

For more information on .NET Compact Framework, see my previous article, "Developing Pocket PC Apps with SQL Server CE."
Features of SQL Server CE 2.0

Figure 1 shows the main components in SQL Server CE and its relationship to SQL Server 2000 (on the desktop).

How to get access to content database after server hardware failure

  

Sharepoint server 2007 and remote sql 2000 SP4 database, after ShraPoint server crash only content databases are available and intact. Trying to restore from backup fail, stsadm -o restore says that not valid backup is available on path. How to get access to available content database using a new installation with same server name, ip address and partitions configuration. How to recover site with content databases information.

Regards, thanks for help.


Victor Naranjo MCSE + Security MCSA + Security MCSE + Messaging MCSA + Messaging ITIL Certified Comptia Security+

Administrator and Developer Guide to Code Access Security in SharePoint Server 2007

  
Explore configuration options, get best practices for managing CAS in SharePoint environments, and walk through a complex CAS scenario.

Linked Server to access Excel 2007

  

Hi

I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.


Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
go
SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]')
Exec sp_dropserver 'AB2','droplogins'


Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges

GRANT ALTER ANY LOGIN TO sqlaccount
GRANT ALTER ANY LINKED SERVER TO sqlaccount


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE

The DisAllowAdHocProcess in

[SSRS 2008 R2]Report Builder Access error-The remote server returned an error: (401) Unauthorized.

  
Hello,        I am able to login Web Service URL and report manager using computer name and report builder is also working fine.  http://RPTSRV/reportserver   (RPTSRV is report server name. web service url working fine. all accessible) http://RPTSRV/reports  (report manager working fine and all accessible ,report builder too) It means I am able to access everything locally right? http://rs.test.abc.com/reportserver  (web service url working fine. all accessible)  http://rs.test.abc.com/reports    (Report manager working fine and all accessible accept report builder)(I carefully checked the link and its taking rs.test.abc.com/reportserver.....)     when I click on reportbuilder button in report manager it shows me error dialog box with following error log!! I tried from local and remote computer too but gives the same error both side. I am not sure but I am just thinking is this double hop kerberos security issue? Please help me to solve this problem. I am finding solution from last 4 days. Reporting Service configuratin tool settings Web Serivice URL -  all set to default Report Manager URL - all set to default Environment - Distributed deployment Server1(report_srv) --Windows server 2003, Installed sql server 2008R2 reporting service,

Linked Server to access Excel 2007

  
Hi I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0. Apparently the query requires the sql account to have SYSADMIN privileges. Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method. Following is my code. Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1' Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL go SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]') Exec sp_dropserver 'AB2','droplogins' Now i get the following error Error.15247-User does not have permission to perform this action If I execute the query from Query Analyzer it works fine, but fails when I execute it using Windows App and encapsulate code in Stored Proc. My Excel file, Database and Windows Application run on separate machines. i have provided the following privileges GRANT ALTER ANY LOGIN TO sqlaccount GRANT ALTER ANY LINKED SERVER TO sqlaccount EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE The DisAllowAdHocProcess in Registry has value 0 Please let me know what additional permissions should i set to get it working???

Linked server to access 2010

  
I keep getting an error when setting up a linked server in sql 2008 R2 x64 to a microsoft access database file (mdb or accdb file).  (The problem occurs on computers with either the 32-bit or 64-bit version of ms office installed).  What are the proper connection string settings?  (This is after right clicking linked servers in ssms and selecting "add new linked server")  The below settings don't work: Provider (from dropdown): Microsoft Office 12.0 Access Database ENgine OLE DB Provider Product name: Access Data source: C:\foopath\foo.mdb  --also doesn't work for foo.accdb Provider string: Microsoft.ACE.OLEDB.12.0 An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER" returned message "Could not find installable ISAM.". (Microsoft SQL Server, Error: 7303) I got some exotic errors when following the directions in Books Online (under sp_addlinkedserver). For example: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apa

SQL Server doesn't exist or access denied while connecting with Access 2010 ODBC

  
Hello all. I am using Access 2010 and SQL Server 2008 (locally). I can easily connect to server using SSMS and it works excellent. But when I want to import a table to Access 2010 using ODBC, it says that "SQL Server doesn't exist or access denied". It worked previously - almost 3 days ago, but now, it doesn't! Please help! Thanks Mohammad Javahery

SQL Server 2008 Powershel trying to access SQL Server 2000

  
Below is a script from my SQL server 2008 PowerShell trying to connect a SQL Server 2000 instance. PS SQLSERVER:\SQL\SS2K8\DEFAULT\databases> cd \ PS SQLSERVER:\> cd sql PS SQLSERVER:\sql> cd MySS2KMachine PS SQLSERVER:\sql\MySS2KMachine> cd default PS SQLSERVER:\sql\MySS2KMachine\default> cd Databases PS SQLSERVER:\sql\MySS2KMachine\default\Databases> dir and I see a list of all databases on the default (SS2K) instance. I also see al instances on the machine: PS SQLSERVER:\sql\MySS2KMachine\default> cd .. PS SQLSERVER:\sql\MySS2KMachine> dir Instance Name ------------- DEFAULT MSSQL2005 As you can see from the above, there is also a SQL Server 2005 instance installed on the MySS2KMachine. Now, if I try the same thing for a Sql Server 2000 instance on another server (Windows 2003 R2 with SP1) with no SQL Server 2005 or higher installed on the machine, I get the error below: PS SQLSERVER:\> cd sql PS SQLSERVER:\sql> cd MyPureSS2KMachine WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'MyPureSS2KMachine' failed with the following error: SQL Server WMI provider is not available on MyPureSS2KMachine. --> Invalid namespace Set-Location : Cannot find path 'SQLSERVER:\sql\MyPureSS2KMachine' because it does not exist. At line:1 char:3 + cd <<<< MyPureSS2KMachine PS SQLSERVER:\sql> So it see

Access to SQL Server Jobs

  
Hello folks, Would like to grant group of users ( windows logins) to CREATE / MODIFY / DELETE / SCHEDULE jobs on SQL Server 2005. But can not grant users SYSADMIN privileges. I know that by granting users SQLAgentOperatorRole, SQLAgenetReaderRole and SQLAgentUserRole the User will be able to create jobs but they will be the owner of the newly created job and other users can not MODIFY / VIEW / RESCHEDULE the jobs. Is there any way to customize permissions to windows logins to have permission to CREATE / MODIFY / DELETE / SCHEDULE jobs without granting them SYSADMIN privilages ? Appreciate your reply   Thank You Arunaroon

Remote access to SQL-Server 2008 R2 on Windows Server 2008 denied

  
Hello, after successful installation of SQLServer 2008 R2 and providing my account as a 'sysadmin' I failed to access the server from a management studio of a SQL-Server 2005 release installation. The TCP/IP access is enabled and the server is configured to allow access from remote connections.  The error message reads a follows: ' A network-related or instance-specific error occured while establishing a connection to SQL Server. The server was not found or was not accessible. Verify ... (Provider: Named Pipes Provider, error 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error:2)' Accessing the SQL-Server by the management studio from the local host works fine but trying to acces by command line using the '-E' parameter fails. Is there any additional configuration on the operation system level needed to open a SQL-Server 2008 instance to be selected by other members of  domain groups from the non-local host? Thank you for the answer  

Access to SQL Server CE 3.5 from SAS

  
Hi.I'm in the educational research field and we routinely the bulk of our data with SAS (www.sas.com/software/sas9/) at the home office while database in the filed use a desktop DBMS such as Access or xBase. We are considering SQL CE as a replacement for a new application and it would be great to just grab the data directly into SAS and bypass options such as syncing to a SQL Server and then reading that. SAS has an OLEDB interface that allows me two configure the provider, source, user names and all the usual seetings. CE 3.5 is installed and I can connect to it just fine from, say, Excel using ADO and the Microsoft.SQLSERVER.CE.OLEDB.3.5 provider string. SAS however gives me cryptic errors and I'm not sure whether it is a limitation of SAS or SQL CE. I can also configure my connection through the Windows "Data Link Properties" dialog but the SQL CE provider doesn't appear there despite being correctly configured in the registry.I have tried ... libname     test oledb init_string='Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=d:\northwind.sdf';run; ... and the result is ... ERROR: Error trying to establish connection: Unable to get create accessor; Error in columns :Errors occurred. [,,,,,] Anyone who is also working with SAS (either 9.1 or 9.2) and got it right? Any way to add SQL CE provider to that "Data Link Properties" window?Greetings

Problem connecting to SQL Server 2008 R2 Express from Access 2007

  
I have a problem with SQL Server 2008 R2 Express with MS Access 2007. I am running Windows 7 with 8 gigs memory. I installed SQL Server 2008 R2 Express. Do I now need to install SP1 because I am running with Windows 7? I can open SQL Server using the Management Screen but I cannot connect to MS Access 2007 using SQL Server Express as the database. When trying to connect to SQL Server from Access 2007, I keep getting the message that SQL Server 2005 cannot be found. It will not allow me to sign onto sql server 2008 stating that it cannot be found. Do I have to now run the SQL Server 2008 R2 Express SP1? Is there an upgrade to MS Access 2007 which will allow me to use SQL Server 2008 R2 Express?

The remote server returned an error: (550) File unavailable (e.g., file not found, no access).

  
Hi.   I have read the other threads regarding this but they do not solve my problem. All i am trying to do is upload a file from the webserver onto another server via FTP. But i get the above error and am at my wits end as to why.   below is the code:   Dim fs As New FileStream("H:\Visual Studio 2005\Websites\Website1\testfile.txt", FileMode.Open) Dim filecontents(fs.Length) As Byte fs.Read(filecontents, 0, fs.Length)   Dim remotePath As String = "ftp://elonwactd.uk.ml.com/apps/files/IPBHVGTRADE/QA5/in/testfile.txt" Dim ftp As FtpWebRequest = FtpWebRequest.Create(remotePath) ftp.Credentials = New System.Net.NetworkCredential("user1", "passwd1") ftp.KeepAlive = False ftp.UseBinary = True   ' (have tried UploadFile as well instead of UploadFileWithUniqueName) ftp.Method = WebRequestMethods.Ftp.UploadFileWithUniqueName     fs.Close()   ftp.GetRequestStream.Write(filecontents, 0, filecontents.Length) ftp.GetRequestStream.Close() ftp.GetResponse.Close()     Below is the stack trace that i get. [WebException: The remote server returned an error: (550) File unavailable (e.g., file not found, no access).]   System.Net.FtpWebRequest.SyncRequestCallback(Object obj) +321   System.Net.FtpWebRequest.RequestCallback(Object obj) +19   System.Net.CommandStream.Abort(Exception e) +163   System.Net.FtpWebRequest.FinishRequestStage(RequestStage stage) +45
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend