CREATE FILE encountered operating system error 5(Access is denied.)

When trying to attach the database to SQL Server with an account other then yours you might get following error.

TITLE: Microsoft SQL Server Management Studio
——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBName.mdf’. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

——————————
BUTTONS:

OK
——————————

If you follow the link it explains nothing; a little confused I did search on the Google (yeeh?).  With no answers as to why this is happening, I made sure SQL Server had proper permissions to the MSSQL Data and Log folders.  I decided to run Process Monitor to see what service account is trying to access the file when I am attaching it; it was as expected my SQL Server Service account was trying to access the file.  So why the permission denied?  File was located in same folder as rest of mdf/ldf files…

Looking into it I saw the file had a small lock icon on it like ““. Looking into the permissions on the file I see it was only accessible by my user name.  I don’t know why this was the case (actually I found why, I’ll post on it tomorrow; need to do a bit more research on that topic).  I edited the permission properties on the file to inhert form parent folder.

Reattached the database with no issues.

Issue: Can’t attach mdf and ldf file to the SQL Server, keeps getting permissions deined even though file is in default data/log directory and SQL Server has access to it.
Solution: Check file to make sure it is inheriting permissions from parent and is not owned by single user.

Actual value was -1. (Microsoft.SqlServer.GridControl) Error when attaching database.

When attaching a database in SQL Server 2008, a user might get the following error.

 TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:

Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)

——————————
BUTTONS:

OK
——————————

The default solution that is listed on the sites [1] & [2] is use a user a user with sysadmin.  It does resolve the issue; but why?

Digging into it a bit, I found when attaching a database SQL Server executes few extended stored procedures.  These stored procedures can only be executed by sysadmin role, therefore when someone with just dbcreator permissions try to attach the database it fails. The actual error is this …

Msg 22001, Level 16, State 1, Line 0
xp_regread() returned error 5, ‘Access is denied.’

So I decided to dive into books online to confirm I am not getting forgetful, Books Online state for sp_attach_db “sysadmin and dbcreator fixed server roles can execute this procedure’.So I decided to execute following command:

EXEC sp_attach_db    @dbname = 'DBName',
                     @filename1='D:\MSSQL10.MSSQLServer\MSSQL\Data\DBName.mdf',
                     @filename2='E:\MSSQL10.MSSQLServer\MSSQL\LOG\DBName_log.ldf'

It attached successfully, so it seems like an issue in GUI only.

I have tried to look for KB article to see if there is a fix for this; also on Microsoft connect with no luck.  So I have a Microsoft Connect article here.

References
[1] How to change SQL Server login default database through SQL Script. Jeff Widmer’s Blog. Link.
[2] Unable to attach database using Management Studio. MSDN Forums Online. Link.

Get listing of Databases user has access to …

You can right click on each user to find out what database he or she has access to; which can be very time consuming. I have wrote the script below that does similar thing for one user or multiple users.

Please note this procedure will take a while to complete depending on the number of databases in your system. You can submit three types of searches:

-- Check Single User [DEFAULT]
EXEC up_CheckUsersAccessInDB 'Test'
GO

-- Check Multiple Users
EXEC up_CheckUsersAccessInDB 'Test1,Test2', 1
GO

-- Check All Users
EXEC up_CheckUsersAccessInDB '%', 2
GO

Stored Procedure: Link.

If any errors or issues please feel free to mail me, mohitkgupta at msn dot com.

Surface Area Configuration Manager *R.I.P.*

All who liked the SACM raise your hands …

Right that’s what I thought no one heh; okay it wasn’t that bad. But non the less it was discontinued in SQL 2008.

So how do you lower the Surface Area?

Answer: Use SQL Server Configuration Manager to manage services and you can use Facets to configure Surface Area Configuration.

Right Click on Server -> Click on Facets -> Click on Drop down list select Surface Area Configuration

Change settings you need and click OK.

Following settings can be adjusted here:

• AdHoc Remote Queries
• CLR Integration
• Database Mail
• OLE Automation
• Remote DAC
• Service Broker Endpoint
• Soap Endpoints
• SQL Mail
• Web Assistant
• XP CmdShell

Ref #1: http://msdn.microsoft.com/en-us/library/cc281850.aspx
Ref #2: http://msdn.microsoft.com/en-us/library/ms161956.aspx

"New Maintenance Plan…" doesn’t work

For some reason Microsoft decided not to enable that function in some editions of RTM in SQL server 2008 (annoying but meh). So looking around I found Microsoft Connect article, link. According to that it was suppose to be for the IA64 only but I was only running developer edition on x86 Windows XP Sp3…

What made it more annoying was you don’t get any error messages or nothing indicating it is not going to work. But I was able to use the wizard to create the maintenance plan; heh guess they over looked that.

So anyhow I decided to install CU4 and I got my Maintenance Plans back again…

CU4 download link; note you have to specially request it.

Default Backup Path in SQL 2008

In the SQL Server 2008 setup you can now set the default backup location but in case you set it wrong or need to change it at later time the SSMS interface does not provide you with any options to do that. You can do the same thing as in 2005 …

Go to following key in Registry and put the new path in …

First we need to determine the instance Name; go to
[HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\]

Note down the value for the default instance usually MSSQL10.MSSQLSERVER (note they changed the naming convention of instance names from MSSQL.Instance# to MSSQL10.InstanceID which you enter in at install time).

Now go to …
[HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\]

And change the value for BackupDirectory to the new value.

Now if you modify a Maintenance Plan it will grab the new value. You don’t need to restart server or services.

Install SQL Server 2008

Microsoft has made the SQL Server 2008 install more function rich then the previous version. They have included functionality into this install which make it easier to set up setups and configure the SQL Server settings that in previous version you would have to do post install.

For example setting up sysadmin privileged accounts:

We saw a similar screen to in the later service pack releases of SQL Server 2005. To me this is one of the most important functions; because installing SQL Server 2005 on servers in gave local administrators SA rights on the server. So anyone part of domain administrators and server operations essentially got SA on the SQL Server which made it difficult to manage the servers because you’ll have databases created on the server out of the blue or jobs that you didn’t know that existed. I can go on rant about that but I’ll stop here…

Next long over due addition in the setup is directory structure:

In 2005 setup we were able to change the data directory; which made it so the system databases’ (master, model, msdb, tempdb) data and log files, along with all user databases were stored in the entered location. In addition we had to manually set the log file path for all user databases if we wanted it to be in different location. We also had to use “alter database” statements for tempdb to locate it on a different drive. Another nice added bonus is Backup Directory!! I had made a post on how to change default backup directory path for 2005 earlier last month as the GUI and the Setup didn’t give you that option (link). All these are nice a plus, I would have liked to have the ability to set the number of data files for the tempdb. As per Microsoft’s recommendation most people are splitting up tempdb to number of CPU in their server; since this functionality is not available in setup this will still need to be completed manually.

Here is a complete setup document on step-by-step guide, link.

Sub-Folder Grouping for Folders

When managing large SQL Server instances; the number of databases on a server sometimes start creating a headache to manage. If we can group the SQL Server databases by logical, function, or whatever sub-folder structure we have it can make DBA life easier.

For example we have System Database and Database Snapshot folder. I might want to make a folder for SharePoint Databases and throw all the SharePoint DB in there; for the users it is no different in functionality but for viewing it makes it easier to access (I hope).

Anyhow, ran into this on NewGroup, link.

So I decided to search Microsoft Connect, few people would like something similar. Please go there and vote, link.

Default Schema in SQL 2005/SQL 2008

When you create a new AD group login in SQL Server and then create the Database user based of that login it is not possible to assign it a Default Schema. If you try to assign it a default login you get the following error:

TITLE: Microsoft SQL Server Management Studio
——————————

Create failed for User ‘Test’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys. (Microsoft SQL Server, Error: 15259)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1442&EvtSrc=MSSQLServer&EvtID=15259&LinkId=20476

——————————
BUTTONS:

OK
——————————

There is a Microsoft Connect article over it: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328585

What I had asked the people to do is qualify their object names with schema where it needs to be; even for dbo schema.

That’s the only workable work-around I had for them; other was create a login for each individual developer. Decided not to go that route because of number of users and turn around on the developer staff.

Activity Monitor in SQL 2008 …

It disappeared …

So it seems; it no longer shows up under Management -> Activity Monitor. Even on-line books state it is there.

I still have RC0 and have read on other sides even in RTM. It is in same location …

If you right click on the server, in the pop-up menu we have the “Activity Monitor” option now.

I don’t know why it was moved there; I thought it was good under management. Mind you this does make it easier to get to it now, maybe that why. .