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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Restricting access to System Databases for Windows User

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

Hi All,

     I was asked to provide access to windows user for only User databases in server. I added this user to db_reader role in all databases after creating login in Server. But here is the problem, this user can able to access System databases even I have n't assign any role for this login in system databases. Your response will be helpful  to fix this . Please let me know if I am missing here to understand it.





View Complete Post

More Related Resource Links

"NT AUTHORITY\SYSTEM" access - to which databases/actions and why?

Subquestion from the question Purpose of "NT AUTHORITY\SYSTEM" login in SQL Server 2005 discussing removal of NT AUTHORITY\System from SysAdmin group in MS SQL Server 2005. Is my SQL Server 2008 R2 "NT AUTHORITY\SYSTEM" login is mapped (right click on --> Properties --> User Mapping) to: user dbo for db AdvenureWorks user "NT AUTHORITY\SYSTEM" for databases - maser - msdb - ReportServer - ReportServerTempDB But no mapping exists for databases tempdb model any manually new created database AdvenureWorks2008 AdvenureWorks2008R2 AdvenureWorksDW AdvenureWorksDW2008 AdvenureWorks2008R2 AdvenureWorksLT AdvenureWorksLT2008 AdvenureWorksLT2008R2 Why such mapping was created for AdvenureWorks but not all other sample databases and newly created databases? Why does ReportServerTempDB requires mapping but not tempdb?

Why does Windows Sharepoint Services Search want access to non-sharepoint databases.


I have installed a new instance of Sharepoint 2007 and am in the process of configuring all the farm services.  At the moment the farm consists of a sharepoint box and a separate sql server.  Here is the catch.  There are already some non-sharepoint databases on the sharepoint server.  The account under which Central Administration runs has dbcreator and securityadmin privileges in the database.  The Sharepoint_Config and SharePoint_AdminContent databases were created without any issues.

When I try to start the Windows Services Search, I get an "Unknown Error."  Looking in the event viewer gives the following error "The server principal "MyServerPrincipal" is not able to access the database "MyDatabase" under the current security context."  This is true, MyServerPrincipal does not have privileges to access MyDatabase, but why should it.  MyDatabase has nothing to do with sharepoint.  

In an attempt to get past this issue, I gave MyServerPrincipal public access to MyDatabase, but that resulted in an additional error saying that it lacked INSERT privileges.  This is really worriesome.  Does anyone know why Sharepoint Search configuration would try to access the non-Sharepoint database?


Thanks a lot.

How to get WIndows Current User without Anonymus Access


Hi All,

We have developed an web application, that should run with Active Directory users. That is working fine in our local machines, but when we hosted this into client environment, we are unable to  get the Current User, it is returning NULL.

Could you please give a solution to us.


Thanks in Advance,

Sateesh Nagalla. 

Restricting access to SQL Server box from using sysadmin user


Is there any way we can restrict a particular sys admin sql user from accessing database on the production enviornment?

There are couple of people who knew the password and keep accessing via this user in the manageemnt studio.

Is it possible to achieve thru policy management?

Cant move system databases of SQL Server 2008 (Clustered) running in Windows 2008 with Service SID e



I was trying to move system databases of SQL Server 2008 on clustered emvironment running on Windows 2008.

I remember while doing the cluster installation somewhere it gave me a recomended option of using Service SID. I think that is the culprit here. Because if you see the "Security" of the DATA folder containing all the MDF and LDF (by default location) you will find a Group named "MSSQLSERVER" is having full access to that directory.

While moving the system databases you need that account (MSSQLSERVER) given access to the parent directory containing the MDF and the LDFs, But unfortunately you cannot add that account manually. Even I tried XCOPY to make sure that while copying the ACLs gets transferred properly to the destination, even that failed.

I think it does provide a solid Security but at the expense of Maintenance, which I cant agree.

Also I forgot to tell you that this Sevice SID option is available if you are using Windows Vista / Windows 2008 or above,

Does anyone has any experience of moving the system databases with Service SID enabled in a cluster environment.

As a testing just try to copy/xcopy/robocopy any folder having MSSQLSERVER group in the ACL and see what happens .. If you are lucky like me then you will get ACCESS DENIED error.

Have anyone faced the same error? I h

user access to system tables



I've some developers who want to be able to select from some system tables. I'm reluctant to give this in case we get any locking issues. As an alternative I'm thinking of creating views with nolock hints, and granting them access to this. e.g.

CREATE VIEW vw_sysjobs
select * FROM sysjobs WITH(NOLOCK)		

Is there a better way of doing this ? Am I right to asume that locking would be a potential problem on these tables ?

(SQL Server 2005)






Access User Profile and Databases with WebParts?


Hello, my name is Mauricio Dominguez and I'm new to MOSS developement and I'm having some trouble adapting and finding my ground in its paradigme.


I need to have a Sharepoint Site with a Form that creates an event in a Database and then it gets indexed with Lucene.net. Then I also need a page from witch a user can query these events and it filters them acording to the "Ask me about" section of their profile.

My questions are:

1. How do I get my Form to create the event on the DB?

2. How can I get the profile information from the current user?

For the last part I tried following this blog post but it throws an error in the line 


TermStore store = session.TermStores["Managed Metadata Service"];


Using windows authentication to access SQL when using ASP.NET 4.0 via COM+


I am using SQL Server 2008 under windows authentication, front end is ASP.NET which uses COM+ to access database.

COM+ components are configured to run as domain user.

When looking through the logs (SQL Profiler), I can see the login name as the configured identity instead of windows indentity.

I have got Website running under "Intergrated Windows Authentication" and database is running locally on webserver.

Web configure contains entry for <identity impersonate="true"/>.

My connection string is

connectionString="data source=db01\test01;initial catalog=test; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes"

Any ideas?




No ASPNET User in Windows Server 2008?


I have been looking everywhere online regarding this, but have not found a clear answer.


I have an ASP.Net app running on Windows Server 2008, it's basically a reporting app using Crystal Reports. I have encountered a roadblock in regards to rendering some pages. When I run the app in Visual Studio(on the same 2008 machine) everything works perfectly. Yet when I publish it and run it live the reports will not display correctly, I get a message saying Failed to render page.


I looked this error up in the Crystal Reports website and the fixes they have there refer to giving the windows "aspnet" user full access rights to certain folders on the machine. I would be happy to do this, however there is no aspnet user in Windows Server 2008.


I installed the server and set its role to be Web Server. I also checked all the options regarding ASP.Net and IIS 6 compatibility. Yet there is no ASPNET username anywhere.


Did I set the server up wrong? or do I have to do something within ASP.Net to  create this user and then give it rights? 

Access Control: Understanding Windows File And Registry Permissions


Understanding the ACLs that govern permissions and rights before an operation is allowed to proceed is critical to enhancing security.

John R. Michener

MSDN Magazine November 2008

CLR Inside Out: Extend Windows Forms Apps Using System.AddIn


See how Windows Forms applications can be adapted to use the new .NET Add-in framework (System.AddIn) this month.

Mueez Siddiqui

MSDN Magazine July 2008

Least Privilege: Teach Your Apps To Play Nicely With Windows Vista User Account Control


User Account Control in Windows Vista keeps the OS safe from intentional and accidental configuration changes.

Chris Corio

MSDN Magazine January 2007

App Fundamentals: Build A Great User Experience With Windows Presentation Foundation


The Windows Presentation Foundation application model distinguishes between standalone and browser applications and between menu driven and hyperlink driven navigation, resulting in a more satisfying experience for your users.

Michael Weinhardt

MSDN Magazine October 2006

Safety in Windows: Manage Access to Windows Objects with ACLs and the .NET Framework


Until now, Microsoft did not provide explicit support in the .NET Framework for manipulating security settings. With the .NET Framework 1.x, access can only be granted to users via a series of cumbersome P/Invoke calls. By introducing the concepts of security objects and rules, the .NET Framework 2.0 allows developers to manipulate security settings of objects in a few easy steps using managed code. Want to know more? Read on.

Mark Novak

MSDN Magazine November 2004

Windows Server 2003: Discover Improved System Info, New Kernel, Debugging, Security, and UI APIs


There's a lot to say about Windows Server 2003. First of all, it's the first operating system with built-in .NET Framework support, and it's the first 64-bit OS from Microsoft. But wait, there's more! There are lots of new features and APIs in this version as well. For instance, Windows Server 2003 features Hot Add Memory and a number of other arcane new tidbits. There are new APIs for handling threads, directories, and files, and new features like the low fragmentation heap for managing memory and system information. There's vectored exception handling and new UI APIs as well.OS internals expert Matt Pietrek takes a look at the additions he finds most interesting and useful so you'll have a good place to start when you dive into Windows Server 2003.

Matt Pietrek

MSDN Magazine June 2003

Windows Forms: Developing Compelling User Controls that Target Forms in the .NET Framework


In the beginning, writing controls meant dealing with Windows messages. Then came Visual Basic controls, which introduced methods, properties, and events. Later, ActiveX controls, which ran atop COM, became popular. While each innovation in control writing brought more flexibility, nothing has matched the versatility of the new .NET Windows Forms controls and Web Forms controls. This article, the first of a two-part series, introduces the reader to Windows Forms, beginning with their inheritance from one of the .NET CLR base classes, which makes control creation much faster than before. Control programming is illustrated through the development of a login control. The equally flexible Web Forms controls will be covered in Part 2.

David S. Platt

MSDN Magazine April 2002

Autoplay in Windows XP: Automatically Detect and React to New Devices on a System


The last few years have seen dramatic growth in digital multimedia content and an increasing number of devices generating such content. Accordingly, the Autoplay feature of Microsoft Windows has been enhanced to handle these scenarios. This article presents samples that use the new features and provides advice on how to take advantage of these additions. The topics covered include Autoplay support for audio CDs and for DVD movies, the Autoplay registration process, event handlers, and how your software can cancel Autoplay when you don't want certain files interrupted. The download includes tools for diagnosing problems and to help the reader understand the shell hardware detection service processing of hardware events.

Stephane St-Michel and Brian Aust

MSDN Magazine November 2001

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