what is required to run dbcc...etc

[Database Administrator]

what is the minimum role and access required to allow a power user login id
to successfully attach dbf file?


when I try connect to a dbf as power user, I get

TITLE: Locate Database Files - mypc\SQLEXPRESS
------------------------------

C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA
Cannot access the specified path or file on the server. Verify that you have
the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the
full path for the file in the File Name control in the Locate dialog box.

------------------------------
BUTTONS:

OK
------------------------------

the service account as net work service does have change access to the
folder and consents, so does the my ID

I have granted my login power user ID
- default contents as master,
as user mapping: master, model, msdb, tempdb,
database role membership for master as db_owner
in Securables, the database mypc\SQLEXPRESS objects: :
connect sql as sa
create any database as sa

effective my ID has administer bulk operation, alter any connection, alter
any credential, alter resources, alter server state, connect sql, create any
database,
view nay database. view server state

what else do I need so I done get the following msg

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

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

------------------------------

User mypc\myID' does not have permission to run DBCC checkprimaryfile.
(Microsoft SQL Server, Error: 2571)

For help, click:
http://go.microsoft.com/fwlink?ProdN...1&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


-( the provided link is as helpful as "we're sorry..There is no additional
information about this issue in the Error and Event Log Messages or
Knowledge Base databases at this time. You .")

[Database Administrator]

Hi

I believe to attach a database you need the same permissions needed to
create a database i.e.
CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

When a database is detached from a version other than SQLExpress some file
permissions are changed. If you have moved this database from a different
version this may cause some issues. SQL Express does not set file
permissions. Information on this is detailed in the "CREATE DATABASE" topic
in Books Online.

John


"GS" wrote:

> what is the minimum role and access required to allow a power user login id
> to successfully attach dbf file?
>
>
> when I try connect to a dbf as power user, I get
>
> TITLE: Locate Database Files - mypc\SQLEXPRESS
> ------------------------------
>
> C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA
> Cannot access the specified path or file on the server. Verify that you have
> the necessary security privileges and that the path or file exists.
>
> If you know that the service account can access a specific file, type in the
> full path for the file in the File Name control in the Locate dialog box.
>
> ------------------------------
> BUTTONS:
>
> OK
> ------------------------------
>
> the service account as net work service does have change access to the
> folder and consents, so does the my ID
>
> I have granted my login power user ID
> - default contents as master,
> as user mapping: master, model, msdb, tempdb,
> database role membership for master as db_owner
> in Securables, the database mypc\SQLEXPRESS objects: :
> connect sql as sa
> create any database as sa
>
> effective my ID has administer bulk operation, alter any connection, alter
> any credential, alter resources, alter server state, connect sql, create any
> database,
> view nay database. view server state
>
> what else do I need so I done get the following msg
>
> TITLE: Microsoft SQL Server Management Studio
> ------------------------------
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
>
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
>
> ------------------------------
> ADDITIONAL INFORMATION:
>
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
>
> ------------------------------
>
> User mypc\myID' does not have permission to run DBCC checkprimaryfile.
> (Microsoft SQL Server, Error: 2571)
>
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...1&LinkId=20476
>
> ------------------------------
> BUTTONS:
>
> OK
> ------------------------------
>
>
> -( the provided link is as helpful as "we're sorry..There is no additional
> information about this issue in the Error and Event Log Messages or
> Knowledge Base databases at this time. You .")
>
>
>

[Database Administrator]

thx, I thought I got all permission but still not sure why I can't start
user instance. Is there a script and sql I can run to make sure.?


Even a template for script and sql will help


I am just about give up and start using something more predictable like
Sybase sql despite it does not quite have the level of integration with
visual studio.. but Sybase sql goes wrong, I can at least fix it or export
it easily

"John Bell" wrote in message
news4BABB16-CAEC-4F04-9AC0-3C753ABBF9A1@microsoft.com...
> Hi
>
> I believe to attach a database you need the same permissions needed to
> create a database i.e.
> CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.
>
> When a database is detached from a version other than SQLExpress some file
> permissions are changed. If you have moved this database from a different
> version this may cause some issues. SQL Express does not set file
> permissions. Information on this is detailed in the "CREATE DATABASE"
topic
> in Books Online.
>
> John
>
>
> "GS" wrote:
>
> > what is the minimum role and access required to allow a power user login
id
> > to successfully attach dbf file?
> >
> >
> > when I try connect to a dbf as power user, I get
> >
> > TITLE: Locate Database Files - mypc\SQLEXPRESS
> > ------------------------------
> >
> > C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA
> > Cannot access the specified path or file on the server. Verify that you
have
> > the necessary security privileges and that the path or file exists.
> >
> > If you know that the service account can access a specific file, type in
the
> > full path for the file in the File Name control in the Locate dialog
box.
> >
> > ------------------------------
> > BUTTONS:
> >
> > OK
> > ------------------------------
> >
> > the service account as net work service does have change access to the
> > folder and consents, so does the my ID
> >
> > I have granted my login power user ID
> > - default contents as master,
> > as user mapping: master, model, msdb, tempdb,
> > database role membership for master as db_owner
> > in Securables, the database mypc\SQLEXPRESS objects: :
> > connect sql as sa
> > create any database as sa
> >
> > effective my ID has administer bulk operation, alter any connection,
alter
> > any credential, alter resources, alter server state, connect sql, create
any
> > database,
> > view nay database. view server state
> >
> > what else do I need so I done get the following msg
> >
> > TITLE: Microsoft SQL Server Management Studio
> > ------------------------------
> >
> > Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> >
> > For help, click:
> >
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
> >
> > ------------------------------
> > ADDITIONAL INFORMATION:
> >
> > An exception occurred while executing a Transact-SQL statement or batch.
> > (Microsoft.SqlServer.ConnectionInfo)
> >
> > ------------------------------
> >
> > User mypc\myID' does not have permission to run DBCC checkprimaryfile.
> > (Microsoft SQL Server, Error: 2571)
> >
> > For help, click:
> >
http://go.microsoft.com/fwlink?ProdN...1&LinkId=20476
> >
> > ------------------------------
> > BUTTONS:
> >
> > OK
> > ------------------------------
> >
> >
> > -( the provided link is as helpful as "we're sorry..There is no
additional
> > information about this issue in the Error and Event Log Messages or
> > Knowledge Base databases at this time. You .")
> >
> >
> >

[Database Administrator]

Hi

Management studio has a template for attaching a database in the template
explorer. I do not know if this is available with Management Studio Express.
Books online has examples that you can copy, if you haven't downloaded a
local copy you can see these at
http://msdn2.microsoft.com/en-us/library/ms176061.aspx for CREATE DATABASE
(use the FOR ATTACH option) or
http://msdn2.microsoft.com/en-us/library/ms179877.aspx for sp_attach_db. I
don't think you are going to find as much easily accessible documentation for
other products.

John

"GS" wrote:

> thx, I thought I got all permission but still not sure why I can't start
> user instance. Is there a script and sql I can run to make sure.?
>
>
> Even a template for script and sql will help
>
>
> I am just about give up and start using something more predictable like
> Sybase sql despite it does not quite have the level of integration with
> visual studio.. but Sybase sql goes wrong, I can at least fix it or export
> it easily
>
> "John Bell" wrote in message
> news4BABB16-CAEC-4F04-9AC0-3C753ABBF9A1@microsoft.com...
> > Hi
> >
> > I believe to attach a database you need the same permissions needed to
> > create a database i.e.
> > CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.
> >
> > When a database is detached from a version other than SQLExpress some file
> > permissions are changed. If you have moved this database from a different
> > version this may cause some issues. SQL Express does not set file
> > permissions. Information on this is detailed in the "CREATE DATABASE"
> topic
> > in Books Online.
> >
> > John
> >
> >
> > "GS" wrote:
> >
> > > what is the minimum role and access required to allow a power user login
> id
> > > to successfully attach dbf file?
> > >
> > >
> > > when I try connect to a dbf as power user, I get
> > >
> > > TITLE: Locate Database Files - mypc\SQLEXPRESS
> > > ------------------------------
> > >
> > > C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA
> > > Cannot access the specified path or file on the server. Verify that you
> have
> > > the necessary security privileges and that the path or file exists.
> > >
> > > If you know that the service account can access a specific file, type in
> the
> > > full path for the file in the File Name control in the Locate dialog
> box.
> > >
> > > ------------------------------
> > > BUTTONS:
> > >
> > > OK
> > > ------------------------------
> > >
> > > the service account as net work service does have change access to the
> > > folder and consents, so does the my ID
> > >
> > > I have granted my login power user ID
> > > - default contents as master,
> > > as user mapping: master, model, msdb, tempdb,
> > > database role membership for master as db_owner
> > > in Securables, the database mypc\SQLEXPRESS objects: :
> > > connect sql as sa
> > > create any database as sa
> > >
> > > effective my ID has administer bulk operation, alter any connection,
> alter
> > > any credential, alter resources, alter server state, connect sql, create
> any
> > > database,
> > > view nay database. view server state
> > >
> > > what else do I need so I done get the following msg
> > >
> > > TITLE: Microsoft SQL Server Management Studio
> > > ------------------------------
> > >
> > > Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> > >
> > > For help, click:
> > >
> http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
> > >
> > > ------------------------------
> > > ADDITIONAL INFORMATION:
> > >
> > > An exception occurred while executing a Transact-SQL statement or batch.
> > > (Microsoft.SqlServer.ConnectionInfo)
> > >
> > > ------------------------------
> > >
> > > User mypc\myID' does not have permission to run DBCC checkprimaryfile.
> > > (Microsoft SQL Server, Error: 2571)
> > >
> > > For help, click:
> > >
> http://go.microsoft.com/fwlink?ProdN...1&LinkId=20476
> > >
> > > ------------------------------
> > > BUTTONS:
> > >
> > > OK
> > > ------------------------------
> > >
> > >
> > > -( the provided link is as helpful as "we're sorry..There is no
> additional
> > > information about this issue in the Error and Event Log Messages or
> > > Knowledge Base databases at this time. You .")
> > >
> > >
> > >
>
>
>