How can I retrieve domain username for group login?

The title says it all. Given that I have created a login for a domain group, and a database user for that login. What I want to do is retrieving the domain username for the active user. USER_NAME retrieves the database username, suser_name returns (of course) NULL as this is not a sql user.

The goal is to use domain group logins, while still allowing for logging what user performed which action.USER_NAME retrieves the database username, suser_name returns (of course) NULL as this is not a sql user.

Really?

When I query suser_sname() while logged in under a windows domain account (using a windows domain group login) I get my DOMAIN\UserName.

Regards,

hmscott

How can I re-install the Northwind database in SQL Server Management Studio Express?Use SqlDataS

Hi all,

In my VB 2005 Express, I created a Windowds Form application "shcDataSet" that used 1 SqlConnection, 1 SqlDataSet and 3 SqlDataAdapters associated with the Northwind Database in my SQL Server Management Studio Express. The SqlConnection had "User Instance" in the following ConnectionString: Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwnd.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True. The 3 SqlDataAdapters were for the Northwind files "Customers", "Orders" and "Order Details" used in the SQLDataSet "AllOrders" with a AllOrders.xsd file. I ran the "shcDataSet" applicatyion and it worked fine. After the execution of "shcDataSet", I checked the Northwind database in my SQL Server Management Studio Express and clicked on "+" in front of Northwind database-I did not see any file showed up and I got the following error message: Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum) Additional information: one or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error:5173). If I executed the "shcDataSet" application again, I got a new error: SqlException was unhandled - Cannot open user default database. Login failed. Login failed for user 'myPC##\myName' ->daCustomers.Fill(AllOrders11, "Customers"). I have 3 questions to ask: (1) How can I re-install the Northwind database in SQL Server Management Studio Express? (2) When I use the .Fill Method, do I have to tell the SQL Server Management Studio Express to load and/or unload the Northwind files "Customers", "Orders" and "Order Details"? (3) After I executed the "shcDataSet", should I Upload the 3 Northwind files back to the SQL Server Management Studio Express? Please help and advise. Thanks, Scott Chang

this is a very common problem in SQL Server Express user intance. YOu are using user instance feature of SQL Server 2005 Express. If you are not intended to do it.. then change the connection string.

Refer this blog :

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Madhu

|||

Hi Madhu, Thanks for your response.

I am new in using the "User Instance" in SQL Server Express and VB 2005 Express. This is the first time I tried to learn it by copying the example from a book. I plan to apply the same technique to my own chemical database and applications for my data management. I do not understand what you said by "If you are not intended to do it.. then change the connection string". Please be specific and give me more details about your statement.

I read the blogs you mentioned and the MS Technical Articles "SQL Server 2005 Express Edition User Instances", "How to: Move Through a DataSet with the Windows Forms BindingNavigator Control", "Connecting to Data with Server Express/Database Express". etc. and I still do not get what you said and the User-Instances Connection, SSEUtil, instance_pipe_name, etc. Please answer my 3 questions stated in my last post in the easy terms for me.

Thanks,

Scott Chang

|||

Hi Madhu, Thanks for your response.

I am new in using "User Instance" and I do not understand what you meant by "If you are not intended to do it, then change the connection string". I want to use it and I just want to see what happened and whether the Northwind Database is in SQL Server Management Studio Express or not. This is the first time I learned the User Instance by doing the example of using User Instance example from a book. I plan to apply the User Instance tecjnique to my chemical database and data management applications in VB 2005 Express-ADO.NET 2.0 programming. If I loose my chemical database during the application of User Instance, it is not possible for me to retrieve the chemical database. I read the blogs you cited and the MS technical articles "SQL Server 2005 Express Edition User Instances", "Connecting to Data with Server Explorer/Database Explorer", "How to: Install and Troubleshoot Database Components for Samples", etc. I do not understand why SSEUtil and Instance_Pipe_Name are used in the connecting to User Instances. Please clarify what you meant by "If you are not intended to do it, then change the connection string" and answer my 3 questions in a easy-to-undersatnd way for me.

Thanks,

Scott Chang

|||

normally, Userinstance of sql server is used unknowingly. that is what i mentioned in my previous post. if you really want to use userinstance of sql server express ... then its ok.... This feature is only available in SQL Server Expess. This one kind of embeded datbase with your application. Handling of Normal and user instance database are totally different. Its already mentioned in above mentioned link. My question is, do you need to use this feature in your application ... this will ofcouse increase the managebility of your Visual studio application (no need to create the database explicitly when you distribute the application) but it has lot of limitations also

Madhu

|||

Hi Madhu,

I am lost completely now and do not want to do the User Instance any more - I just want to repair/restore the Northwind Database in my SQL Server Management Studio Express now. This morning, I downloaded the Northwind and Pubs Sample Databases from http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en/ and saved SQL200SampleDb.msi in my PC. I double-clicked the SQL2000SampleDb.msi file to install the Northwind database - it ran, but the Northwind database in my SQL Server Management Studio Express had no files to show, when I clicked the '+' in front of the Northwind node!!!? What is wrong in my PC? Do I have to delete the User Instance=True of the Connection String of my "shcDataSet" project when executed? Please help me in restoring/repairing the Northwind Database in my SQL Server Management Studio Express?

Many Thanks,

Scott Chang

|||

post the connection string... if you don't want user instance.. yes you need to change the connection string...

For Connection string Refer : http://www.connectionstrings.com/?carrier=sqlserver2005

And also ... sql server 2005 sample datbase is Adventureworks... Northwind still you can use , but many new features like schema and all will not be available in that. you can download Adventureworks database from the link mentioned below

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

Madhu

|||

Hi Madhu, Thanks for your response.

1) I went to the CANtrol Panel => Add or Remove Program => removed the old Microsoft SQL Server 2005 Samples. Then I downloaded the new Microsoft SQL Server 2005 Samples from

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en (as you said).

I checked the SQL Server Management Studio Express and looked at the Northwind Database and I still did not see any files in the Northwind Database!!!? Please tell me how I bring the files of Northwind Database to the SQL Server Management Studio Express (i. e. how to repair it.).

2) I do not understand whay you mean by "if you don't want user instance.. yes you need to change the connection string...

For Connection string Refer : http://www.connectionstrings.com/?carrier=sqlserver2005". Please elaborate this matter if you can. Pardon me for asking this thing.

Thanks again,

Scott Chang

how can i print what i see after i expand a drill down?

I've created a report with drill downs that are hidden initially. While viewing the report in preview, I'll expand the drill down to see the data.

If I go and print the report, the report is printed as collapsed (which is the initial setting) rather than expanded (which i expanded in the preview).

Is there a way to print in the "what-you-see-is-what-you-get" kinda way expanding and collapsing drill downs?

Thanks in advance!

Try setting the InitialToggleState to Expanded in the properties section of that Drill down columns.

Hope this helps.....

- Deepak

|||

it odd, if i start out in expanded and then collapse it in the preview tab, the printing will follow along (meaning i can collapse 1 and print it as i see it), but if i start out collapse and try to expand it in the preview tab, the print doesn't follow?

anyone else have any ideas?

How can I pass a parameter value from VB.Net to Crystal Report?

Hi! I need your help...asap...

How can I pass a parameter value from VB.Net to Crystal Report 11?

I created a report from Crystal Report 11 which has a parameter field for querying the records and I created a form from VB.Net which allows the user to enter a value which will be used for the report but I don't how to pass a parameter value from VB.Net to Crystal Report.Try This...

dim Report as New ReportDocument

Report.Load("YourReport.rpt") 'This is your Crystal Report File

Report.SetParameterValues("PARAM1","Christine") 'PARAM1 should have exactly the same spelling as what can be found in your parameter list in your "*.rpt" file.

CRViewer.Reportsource = Report

How can I list NTLM permissions on a database

Hi folks,
does anybody know a tool or command/sql statement to list all permissions
which are set on a given database. The database has been created years ago
using network authetication. What I am looking for is something like dumpsec
for NTFS.
Thanks
AndreHi Andre,
Did you mean that you would like to list all permissions specified to the
database users on a given database?
You may try the following statement:
USE <database_name>
Go
select distinct l.name as grantee_name, p.permission_name from
sys.database_permissions as p join sys.database_principals as l
ON p.grantee_principal_id = l.principal_id
order by grantee_name
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles,
thanks a lot for your quick reply to my issue.
The SQL query you provided did not work. I assume it was meant for SQL
Server 2005. Sorry, in my post I forgot to mention the used version (SQL
Server 2000). However, after searching the internet for a way to dump the
permissions, I found DumpSQLSec. A nice handy tool which dumps a lot of
informations about a database table. See
http://www.sqlservercentral.com/columnists/cmiller/dumpsqlpermissions.asp
for details.
Again, thanks for your support
Andre
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> schrieb im Newsbeitrag
news:tFyfyC2zHHA.5836@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> I am interested in this issue. Would you mind letting me know the result
> of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Andre,
Thank you for your reply and the detailed additional feedback on how you
were successful in resolving this issue. This information has been added to
Microsoft's database. Your solution will benefit many other users, and we
really value having you as a Microsoft customer.
If you have any other questions or concerns, please do not hesitate to
contact us. It is always our pleasure to be of assistance.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

How can I list NTLM permissions on a database

Hi folks,
does anybody know a tool or command/sql statement to list all permissions
which are set on a given database. The database has been created years ago
using network authetication. What I am looking for is something like dumpsec
for NTFS.
Thanks
AndreHi Andre,
Did you mean that you would like to list all permissions specified to the
database users on a given database?
You may try the following statement:
USE <database_name>
Go
select distinct l.name as grantee_name, p.permission_name from
sys.database_permissions as p join sys.database_principals as l
ON p.grantee_principal_id = l.principal_id
order by grantee_name
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
thanks a lot for your quick reply to my issue.
The SQL query you provided did not work. I assume it was meant for SQL
Server 2005. Sorry, in my post I forgot to mention the used version (SQL
Server 2000). However, after searching the internet for a way to dump the
permissions, I found DumpSQLSec. A nice handy tool which dumps a lot of
informations about a database table. See
http://www.sqlservercentral.com/col...permissions.asp
for details.
Again, thanks for your support
Andre
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> schrieb im Newsbeit
rag
news:tFyfyC2zHHA.5836@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> I am interested in this issue. Would you mind letting me know the result
> of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Andre,
Thank you for your reply and the detailed additional feedback on how you
were successful in resolving this issue. This information has been added to
Microsoft's database. Your solution will benefit many other users, and we
really value having you as a Microsoft customer.
If you have any other questions or concerns, please do not hesitate to
contact us. It is always our pleasure to be of assistance.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

how can i insert id field automatic in table by stored procedure

i have a student table and i created a stored procedure to insert a new student in this table but student_id field wich i put it as primary key
got error because allready record has same value .
how i can know the last row's student_id value and input a new valid value in one stored procedure

thanksIf you make the Student_ID field an IDENTITY field, it will automatically increment for you.

how can i get the base tables underlying a given view object ?

i would like to know if i can, thru a query, find the base tables
underlying a given view object.
eg: somebody has created a view as
create view empMaster as
select * from emp1
union all
select * from emp2
now given the view empMaster, i want to know the underlying tables (i.e.
emp1,emp2)
thanks
Vivek T S
Member Technical Staff (Inucom)
try sp_depends viewname
HTH
"Vivek T S" wrote:

> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)
|||Vivek T S wrote:
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables
> (i.e. emp1,emp2)
> thanks
Or run sp_help on the view to see the actual SQL. sp_depends is a good
choice as well, but is not always accurate with stored procedures. Not
sure of the same problem with views.
David G.
|||exec sp_helptext empMaster
This will show the TSQL definition used to define the view.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)
|||David G. wrote:
> Vivek T S wrote:
> Or run sp_help on the view to see the actual SQL. sp_depends is a good
> choice as well, but is not always accurate with stored procedures. Not
> sure of the same problem with views.
Oops.. meant sp_helptext.. Thanks Kalen.
David G.
|||Thanks guys.
One more question. Can i do the same using say an ODBC program (which is my
real interest)
"Kalen Delaney" wrote:

> exec sp_helptext empMaster
> This will show the TSQL definition used to define the view.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
>
>

how can i get the base tables underlying a given view object ?

i would like to know if i can, thru a query, find the base tables
underlying a given view object.
eg: somebody has created a view as
create view empMaster as
select * from emp1
union all
select * from emp2
now given the view empMaster, i want to know the underlying tables (i.e.
emp1,emp2)
thanks
--
Vivek T S
Member Technical Staff (Inucom)try sp_depends viewname
HTH
"Vivek T S" wrote:
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)|||Vivek T S wrote:
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables
> (i.e. emp1,emp2)
> thanks
Or run sp_help on the view to see the actual SQL. sp_depends is a good
choice as well, but is not always accurate with stored procedures. Not
sure of the same problem with views.
--
David G.|||exec sp_helptext empMaster
This will show the TSQL definition used to define the view.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)|||David G. wrote:
> Vivek T S wrote:
>> i would like to know if i can, thru a query, find the base tables
>> underlying a given view object.
>> eg: somebody has created a view as
>> create view empMaster as
>> select * from emp1
>> union all
>> select * from emp2
>> now given the view empMaster, i want to know the underlying tables
>> (i.e. emp1,emp2)
>> thanks
> Or run sp_help on the view to see the actual SQL. sp_depends is a good
> choice as well, but is not always accurate with stored procedures. Not
> sure of the same problem with views.
Oops.. meant sp_helptext.. Thanks Kalen.
--
David G.

how can i get the base tables underlying a given view object ?

i would like to know if i can, thru a query, find the base tables
underlying a given view object.
eg: somebody has created a view as
create view empMaster as
select * from emp1
union all
select * from emp2
now given the view empMaster, i want to know the underlying tables (i.e.
emp1,emp2)
thanks
--
Vivek T S
Member Technical Staff (Inucom)try sp_depends viewname
HTH
"Vivek T S" wrote:

> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)|||Vivek T S wrote:
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables
> (i.e. emp1,emp2)
> thanks
Or run sp_help on the view to see the actual SQL. sp_depends is a good
choice as well, but is not always accurate with stored procedures. Not
sure of the same problem with views.
David G.|||exec sp_helptext empMaster
This will show the TSQL definition used to define the view.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)|||David G. wrote:
> Vivek T S wrote:
> Or run sp_help on the view to see the actual SQL. sp_depends is a good
> choice as well, but is not always accurate with stored procedures. Not
> sure of the same problem with views.
Oops.. meant sp_helptext.. Thanks Kalen.
David G.|||Thanks guys.
One more question. Can i do the same using say an ODBC program (which is my
real interest)
"Kalen Delaney" wrote:

> exec sp_helptext empMaster
> This will show the TSQL definition used to define the view.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
>
>