Role permissions disappear

I created a new database role to give a number of users select privilege only on some tables and every day I have to go back in and add the tables back to the role. Is there something I'm missing here?First, you don't add tables to roles...you grant permissions to users on objects.

Second, it sounds like you're either recreating the tables every night (unlikely), or youre doing a restore...

which is it...

There are no miracles...|||I think you hit the nail on the head. Most if not all of these tables are dropped and recreated every night. Doh!!! Sorry, I'm stumbling through this. Our DBA up and quit out of the blue and this got dumped in my lap.|||No sweat...set this up as a stored procedure, then schedule it as the last step of your nightly batch job..

just change PUBLIC to whatever role yo have...

DECLARE myGrants99 CURSOR FOR
SELECT 'GRANT SELECT ON [' + TABLE_NAME + '] TO PUBLIC '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN myGrants99

DECLARE @.SQL varchar(8000)

FETCH NEXT FROM myGrants99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC(@.SQL)
FETCH NEXT FROM myGrants99 INTO @.SQL
END

CLOSE myGrants99
DEALLOCATE myGrants99|||Thanks for this. It'll help tremendously. I'll give this to the guys that created the DTS packages to do the loads.|||OK, Now I have a silly question...

Why are they dropping the tables?

You'd be better off if they TRUNCATE the tables...

alos I'm assuming this is not an OTLP (going out on a limb, eh) database, ratyher a reporting/ warehouse...right?

Is there any RI?

Do you work in the Northeast US?

I work cheap...

:D|||Don't know why they're dropping and not truncating. I will suggest that to the developers. And this is a reporting warehouse for Crystal. Sorry, but working for a major telecommunications company doesn't allow me to contract outside assistance. OJT is our main source of training. Sounds really stupid doesn't it?|||Naaaahhhh

Jump in to the pool...deep end...feet first...keep your head above water...
Also, Go out and buy (and read) some good books...

Check out:

http://www.sqlteam.com/store.asp

EDIT: Oh, and keeping coming back to here or sql team...|||I'll definitely keep coming back. The response has been great. This is the second question I've posted here and both have been answered quickly.

Thanks again.

Role change in log shipping

As a last step to implementing a log shipping role change...i..e making the
secondary the primary and the primary the secondary, do users change the
servernames so that apps that are referring the SQL server by host name
reflect the actual name .
We would like to implement some kind of disaster recovery using SQL 2000 log
shipping and wanted to know when we do role changes , what are the best ways
to allow the clients to still communicate to the now secondary server that
has been promoted to be the primary.I prefer the application being prepared for this. The app can for instance read the server name in a
configuration file somewhere and you quite simply change the config file to change roles.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:%232MdTFolDHA.372@.TK2MSFTNGP11.phx.gbl...
> As a last step to implementing a log shipping role change...i..e making the
> secondary the primary and the primary the secondary, do users change the
> servernames so that apps that are referring the SQL server by host name
> reflect the actual name .
> We would like to implement some kind of disaster recovery using SQL 2000 log
> shipping and wanted to know when we do role changes , what are the best ways
> to allow the clients to still communicate to the now secondary server that
> has been promoted to be the primary.
>
>|||Heelo
> As a last step to implementing a log shipping role change...i..e making
the
> secondary the primary and the primary the secondary, do users change the
> servernames so that apps that are referring the SQL server by host name
> reflect the actual name .
Sure. I'd recommend you to make an alias on DNS server
wich will always lead to your production server. In this case
you'll need to change only this alias. (But if you use large
network you'll need to propagate modifications across all
DNS servers your clients use).
Serge Shakhov

Role - Read access - Analysis Services 2005

After giving some users read access to the cube, by using a readonly role -
The [Measures] are displayed with #N/A (#I/T in danish ? ).

Everything else seems to work fine - There is access to the database and all
dimensions. I tried the test cube security button and it work fine, but from
Excel the measures is not displayed correctly. Excel is showing the measures for users with Administrator privilege.

Best regards,

Ejnar Jakobsgaard
TDC Denmark

Hi

I am currently using the ProClarity tool to access the data from the analysis services 2005 - even I am facing the same problem of getting #N/A as values for the measures. I have provided the read only access to the users at the cube and dimension level and it doesn't work - Do have any solution?

Regards,

Narayan Indran

|||

Hi Narayan

Have a look at your "Cell Data" Filters , these should all be ampty. Only use MDX expressions in "Dimension Data" filtering.

That worked for me

Louis

Role - Read access - Analysis Services 2005

After giving some users read access to the cube, by using a readonly role -
The [Measures] are displayed with #N/A (#I/T in danish ? ).

Everything else seems to work fine - There is access to the database and all
dimensions. I tried the test cube security button and it work fine, but from
Excel the measures is not displayed correctly. Excel is showing the measures for users with Administrator privilege.

Best regards,

Ejnar Jakobsgaard
TDC Denmark

Hi

I am currently using the ProClarity tool to access the data from the analysis services 2005 - even I am facing the same problem of getting #N/A as values for the measures. I have provided the read only access to the users at the cube and dimension level and it doesn't work - Do have any solution?

Regards,

Narayan Indran

|||

Hi Narayan

Have a look at your "Cell Data" Filters , these should all be ampty. Only use MDX expressions in "Dimension Data" filtering.

That worked for me

Louis

rights issue

How do I text base add my domain users group with full rights to my database. I am using teratrax to manage my database. This is what comes up when I click on new database user

-- Replace all lower case words with your own code.

EXECUTE SP_GRANTDBACCESS 'login', 'user_name'Hi,

with that statement you posted you only grant database access for an user. The user cannot do anything in your database if you don't grant him further permissions. What exactly do you mean with full rights? Systemadministrators' rights? Or are DBOwner-Rights sufficient?

Otherwise you should create a role which has specific rights in your databases and add the users group to that role.

:)|||Yeah DBOWNER would be enough rights. How do I add roles and then how do I add domain users group to them?|||To create a new role use:

sp_addrole [ @.rolename = ] 'role'
[ , [ @.ownername = ] 'owner' ]

in the specific database.

Grant Permissions to that role for each object needed:

GRANT
{ ALL [ PRIVILEGES ] | permission [ ,...n ] }
{
[ ( column [ ,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [ ,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [ ,...n ]
[ WITH GRANT OPTION ]
[ AS { group | role } ]

To add a user or group to a role use:

sp_addrolemember [ @.rolename = ] 'role' ,
[ @.membername = ] 'security_account'

in the specific database.

To add a user/group to the db_owner role use:

sp_addrolemember [ @.rolename = ] 'db_owner' ,
[ @.membername = ] 'security_account'

in the specific database.

EDIT: Replace 'security_account' with your domain users name in the database, e.g.: [domainname\groupname]

:)

Rights for clickonce Report Builder access?

I'm attempting to grant rights to Report Builder as deployed as part of my TFS install. My problem is that I have to add my users to the builtin\admin group in order for them to see the report builder button on the SQL Server Reporting homepage & then have rights to launch the ReportBuilder click once app. I obviously do not want make users admins on the box, but I've tried adding them to all of the other groups having to do with SQLServer to no avail. How can I grant users access to launch the Report Builder app?

Users need to have the Report Server system permission "Execute Report Definitions" in order to see the Report Builder icon in Report Manager.

1. access Report Manager Site Settings page: http://%machine%/Reports/Pages/Settings.aspx

2. click "Configure site-wide security"

From here you can either modify an existing RS role assignment or create a new one, and give the applicable users permissions to "Execute Report Definitions".

BTW, the Windows security groups created by SQL Server setup aren't used for RS security. Members of the local Administrators get some built-in permissions, which is why making your users local Administrators caused the Report Builder icon to show up.

|||I started with that: the users see the report builder Icon, but then they are prompted with a Sql server logon when the click the report builder icon. I've granted datareader on the sql databases 'tfswarehouse' and 'reportServer' for that user. Is it a sql rights thing?|||

No, I don't think that's going to make a difference. The data that's used in the Report Builder reports is accessed using credentials as per the data source object in Report Server that's bound to the model that Report Builder is consuming.

Making sure I have the right repro:

1. click Report Builder icon in Report Manager

2. see a SQL Server logon dialog

You never see a model selection pane or a design surface?

|||

That's correct. If I give box admin rights to user, all looks and acts normally. With Report builder admin rights & no box admin rights the user sees report builder icon but when he goes to lauch the app he gets security dialog.

|||

Resolved it. Maybe it was me being dense, but the report builder site security page does not make any sense to me. You need to first of all create a group (box or domain) and add the users to it. then use that group name in the security page granting the content manager right. It makes total sense to me now that it's done, but it's not clear on the security page that that is what you need to do. I'd suggest making that page more clear... or the documentation... or both!

Rights assigned to Windows\Power Users in SQL Express.

Hi,

In SQL Server Express Edition, what are the rights that are assigned to a Normal Windows User and PowerUser by default ?

When I install SQL Express on a clean machine and login as Power User I can add/edit/delete data but when I login as Normal Windows User I can see the data but not change it.

Please help me in this regard.

Thanks and Regards,
Gautham.

What do you mean exactly by the ability to add/edit/delete data; what kind of data is that?

In SQL Server Express, members of Builtin\Administrators are members of the sysadmin server role. Members of Builtin\Users are only granted the connect sql permission. There is no special provisioning for Builtin\Power Users, so they would just have the same right on SQL Server Express as a "normal" user.

To verify if a principal has a special permission, you can use the has_perms_by_name builtin. For example, to check if a user has select on table t, you can execute:

select has_perms_by_name ('t', 'object', 'select')

while connected as that user.

For additional information, you can check the following catalogs:

sys.server_principals
sys.server_permissions
sys.login_token
sys.database_principals
sys.database_permissions
sys.user_token

Thanks
Laurentiusql

Right way to retrieve data from sql-server

Last night I face performace problems in web site. I got like 100 000 users and site fails because of sql-connections.

Currently I'm making a new connection for every query, and my code looks like this:

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataSetDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)Dim sqlDataSetAs New DataSetTry sqlConnection.Open()Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommand myDataAdapter.Fill(sqlDataSet)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return sqlDataSetEnd Function

I was wondering would it be better to save an instance from connection object to memory and use that same connection for all querys?

No. SQL connection pooling is automatic.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.asp

Which version of SQL are you running?

As a by note, a SQLAdapter will open its connection if it is closed, and if it did open it, close it immediately after it finishes.

The only real improvement above would be the use of a SQLDataReader if applicable, and if not, the instantiation of the SQLDataAdapter, and assignment of its command, prior to opening the connection.

|||

I do not think that saving connection will help you to much because you use connection pooling anyway
try to modify your code to look like this:

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataSetDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)

Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommandDim sqlDataSetAs New DataSetTry sqlConnection.Open() myDataAdapter.Fill(sqlDataSet)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return sqlDataSetEnd Function

So now your connection should be use for a little shorter period of time and it should allow other web user to access data,
you can also try to optimize your query to be processed in shortest amount of time. Another question is:

 Do you really need to return dataset? maybe returning table will be more efficient (less resources to use)?

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataTableDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)
Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommandDim ResultTableAs New DataTableTry sqlConnection.Open() myDataAdapter.Fill(ResultTable)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return ResultTableEnd Function
 
You can also try to use different way for reading your data DataReader?
Thanks

|||

But sometimes is safe to open and close connection by hands in case you would like to use the same connection object for multiple database access, because adapter sometimes does not close connection and reader inside it if it fails so you can have problem if you would like to use the same connection object after adapter. But if you use connection object for one call you can allow adapter to take care about opening/closing connection.

Thanks

|||

Thanks you!

Automatic pooling was new to me, I'm such a newbie :-)

Thanks

Right to create db diagrams

Hello,
Does anyone knows which right has to be assigned to a user so that it can cr
eate a database diagram.
Indeed, we currently have users having the rights of connection and the role
s following: db_datareader, db_datawriter and db_ddladmin.
With these roles, the creation of a new diagram of dB raises an error indica
ting that the user does not have the authorizations necessary to create a ne
w diagram.
Which role, or grant XX, can solve this concern?
thanks
AlexandreWhat version of SQL Server and what is the exact error
message?
If you are on SQL Server 2000, the users in those roles
should be able to create the diagrams under most
circumstances. The warning that initially pops up would be
something like "you aren't logged on as database owner or
system administrator. You might not be able to save changes
to tables that you don't own" but that won't prevent them
from actually creating the diagram
If the error you get is "You do not have sufficient
privileges to create the new diagram", then you may be
running into the following issue:
FIX: You Cannot Create Diagrams in SQL Enterprise Manager If
You Are Not a Database Owner
http://support.microsoft.com/?kbid=327145
-Sue
On Tue, 16 Mar 2004 01:16:08 -0800, "Alex"
<anonymous@.discussions.microsoft.com> wrote:

>Hello,
>Does anyone knows which right has to be assigned to a user so that it can c
reate a database diagram.
>Indeed, we currently have users having the rights of connection and the rol
es following: db_datareader, db_datawriter and db_ddladmin.
>With these roles, the creation of a new diagram of dB raises an error indic
ating that the user does not have the authorizations necessary to create a n
ew diagram.
>Which role, or grant XX, can solve this concern?
>thanks
>Alexandre|||Hi,
Thanks for the input.
I was running into the issue you have mentionned, and the workaround in the
note works fine to correct this.
Alex

Revoking permission to view SQL Stored procs

Hi
Is there a way which I can revoke a users ability to view SQL stored procs,
but still have the ability to execute?
I want to do this via T-SQL, and without just considering the 'WITH
ENCRYPTION' option
Thanks
hi Paul,
Paul Aspinall wrote:
> Hi
> Is there a way which I can revoke a users ability to view SQL stored
> procs, but still have the ability to execute?
> I want to do this via T-SQL, and without just considering the 'WITH
> ENCRYPTION' option
> Thanks
every database user is allowed to view the definition of stored procedures,
and you can not change this behaviour... unfortunately, as you already
pointed out, you can only consider the WITH ENCRYPTION option
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What about granting only EXECUTE permissions and not CREATE, ALTER or
DROP ?
|||hi,
bd wrote:
> What about granting only EXECUTE permissions and not CREATE, ALTER or
> DROP ?
every database user has implicit permission to script objects, so he/she
will be able to access the object definition and DDL
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Revoke View any database from public in MSSQL 2005

Hello Everyone,
I am running into an issue when I run the following query to revoke access
to all the SQL 2005 users from other DB.s
use master
Revoke View any database from public
Go
When the users logs in with SMSE he/she does not see the db under “databas
es”.
They can only access via QUERY.
Thanks,
-CarlosDoesn't it make sense, after all, you removed (REVOKE) the ability to see
(VIEW) any (that means ALL) database to everyone (PUBLIC).
Perhaps you had a different outcome in mind, and you would like some help.
But you're going to have to be more specific, It's a waste of time to try to
guess what you want as a final outcome.
Your question/request is not clear.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
> Hello Everyone,
> I am running into an issue when I run the following query to revoke access
> to all the SQL 2005 users from other DB.s
> use master
> Revoke View any database from public
> Go
> When the users logs in with SMSE he/she does not see the db under
> "databases".
> They can only access via QUERY.
> Thanks,
> -Carlos
>|||Arnie,
Sorry for not being so clear on this. I will try to explaing in a different
way.
We run a shared hosting enviroment and SQL 2005 out of the box let's users
view all the db's and logins on the server when using SMSE.
By revoking the view command the users should still be able to see their
DB's since their role to each is "DBO".
As I said on the post, they can run queriues against the db using SMSE but
the tool will not graphicaly show you the db as it shows Master and tempdb.
Perhaps this will also help you understand my issue a big better.
http://weblogs.asp.net/hosterposter.../17/443118.aspx
Thanks for the reply,
-Carlos
"Arnie Rowland" wrote:

> Doesn't it make sense, after all, you removed (REVOKE) the ability to see
> (VIEW) any (that means ALL) database to everyone (PUBLIC).
> Perhaps you had a different outcome in mind, and you would like some help.
> But you're going to have to be more specific, It's a waste of time to try
to
> guess what you want as a final outcome.
> Your question/request is not clear.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
> news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
>
>|||You mention they should see the databases "since their role
to each is "DBO".
The behavior will work as the article describes but I'm not
sure what you mean by their role to each is dbo. If you mean
adding the user to db_owner role, that won't do it. The user
needs to be mapped to dbo - as in be the owner of the
database. There is a difference.
Look at who the owner of the database is by executing
sp_helpdb 'YourDatabase'
or execute
sp_helpuser 'dbo'
-Sue
On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Arnie,
>Sorry for not being so clear on this. I will try to explaing in a differen
t
>way.
>We run a shared hosting enviroment and SQL 2005 out of the box let's users
>view all the db's and logins on the server when using SMSE.
>By revoking the view command the users should still be able to see their
>DB's since their role to each is "DBO".
>As I said on the post, they can run queriues against the db using SMSE but
>the tool will not graphicaly show you the db as it shows Master and tempdb.
>Perhaps this will also help you understand my issue a big better.
>http://weblogs.asp.net/hosterposter.../17/443118.aspx
>Thanks for the reply,
>-Carlos
>"Arnie Rowland" wrote:
>|||Sue,
Each user is the dbo to each database.
-Carlos
"Sue Hoegemeier" wrote:

> You mention they should see the databases "since their role
> to each is "DBO".
> The behavior will work as the article describes but I'm not
> sure what you mean by their role to each is dbo. If you mean
> adding the user to db_owner role, that won't do it. The user
> needs to be mapped to dbo - as in be the owner of the
> database. There is a difference.
> Look at who the owner of the database is by executing
> sp_helpdb 'YourDatabase'
> or execute
> sp_helpuser 'dbo'
> -Sue
> On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
> <CarlosCaneja@.discussions.microsoft.com> wrote:
>
>|||Lines: 1
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: c-24-8-112-204.hsd1.co.comcast.net 24.8.112.204
Xref: leafnode.mcse.ms microsoft.public.sqlserver.security:1263
DBO gets misunderstood sometimes so that's why I'm wondering
if that's not the issue. I just did a few tests and it works
fine on two instances I just tested in on. As long as the
logins were database owners, they could see the database.
Anything else, and they could not see the database.
If SomeUser is the login and user in question, the results
for sp_helpuser 'dbo' in that database are:
UserName: dbo
GroupName: db_owner
LoginName: SomeUser
The results for sp_helpdb 'YourDatabase list the owner as
SomeUser.
If that's your results, try changing the database owner
using sp_changedbowner and try changing it back to the user
you need to have own the database.
-Sue
On Wed, 27 Sep 2006 11:01:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Each user is the dbo to each database.
>-Carlos
>"Sue Hoegemeier" wrote:
>

revised attach files to new box

sql2k sp3
Box crashed. Uninstalled/ reinstalled sql. I have a backup
of the master db but not for the users db's. I will
restore from the master backup. But after that, is there a
way to attach the mdf's that werent cleanly detached?
TIA, ChrisR
Try first using sp_attach_db. This requires both mdf, ndf and ldf file(s).
If that doesn't cut it, you can *try* sp_attach_single_file_db, which doesn't require ldf. With much luck, SQL
Server can create the ldf file for you.
Above two are only documented to work if you cleanly detached the databases first. It might work for you even
if you didn't. If it doesn't, open a case with MS support and see if they have any suggestions (which will
probably result in possible data-loss and/or physical and/or logical inconsistencies).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message news:ca3f01c48a14$a0052fd0$a601280a@.phx.gbl...
> sql2k sp3
> Box crashed. Uninstalled/ reinstalled sql. I have a backup
> of the master db but not for the users db's. I will
> restore from the master backup. But after that, is there a
> way to attach the mdf's that werent cleanly detached?
> TIA, ChrisR

revised attach files to new box

sql2k sp3
Box crashed. Uninstalled/ reinstalled sql. I have a backup
of the master db but not for the users db's. I will
restore from the master backup. But after that, is there a
way to attach the mdf's that werent cleanly detached?
TIA, ChrisRTry first using sp_attach_db. This requires both mdf, ndf and ldf file(s).
If that doesn't cut it, you can *try* sp_attach_single_file_db, which doesn'
t require ldf. With much luck, SQL
Server can create the ldf file for you.
Above two are only documented to work if you cleanly detached the databases
first. It might work for you even
if you didn't. If it doesn't, open a case with MS support and see if they ha
ve any suggestions (which will
probably result in possible data-loss and/or physical and/or logical inconsi
stencies).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message news:ca3f01c48a14$a0052fd0$a
601280a@.phx.gbl...
> sql2k sp3
> Box crashed. Uninstalled/ reinstalled sql. I have a backup
> of the master db but not for the users db's. I will
> restore from the master backup. But after that, is there a
> way to attach the mdf's that werent cleanly detached?
> TIA, ChrisR

revised attach files to new box

sql2k sp3
Box crashed. Uninstalled/ reinstalled sql. I have a backup
of the master db but not for the users db's. I will
restore from the master backup. But after that, is there a
way to attach the mdf's that werent cleanly detached?
TIA, ChrisRTry first using sp_attach_db. This requires both mdf, ndf and ldf file(s).
If that doesn't cut it, you can *try* sp_attach_single_file_db, which doesn't require ldf. With much luck, SQL
Server can create the ldf file for you.
Above two are only documented to work if you cleanly detached the databases first. It might work for you even
if you didn't. If it doesn't, open a case with MS support and see if they have any suggestions (which will
probably result in possible data-loss and/or physical and/or logical inconsistencies).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message news:ca3f01c48a14$a0052fd0$a601280a@.phx.gbl...
> sql2k sp3
> Box crashed. Uninstalled/ reinstalled sql. I have a backup
> of the master db but not for the users db's. I will
> restore from the master backup. But after that, is there a
> way to attach the mdf's that werent cleanly detached?
> TIA, ChrisR

Returning role memberships FROM sql Server

A problem: I created some user defined roles in SQL SERVER 2000 and assigned users to different roles. Can you help me with a way (stored procedure etc.) to return the correct roles of my logged in users to my ASP.NET application.

Well if you have a table of logged in users you just have to do an Inner Join

SELECT *
FROM  LoggedInUsers liu INNER JOIN Roles r ON liu.role_id = r.id