Hello
how can i get current logged windows user name in tsql. I know with windows aut it is possible how can i get this when sql auth.
Is there any system function or any special codeing required ?
regards
Chikuu
Hi all
I've been having a really difficult time finding out how to run a query which selects records from an SQL database for the current logged on user. I've tried a whole manner of different approaches but here is where I am at now:
<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
If Not Page.IsPostBack Then
ds_meobservingcolleagues.SelectParameters(0).DefaultValue = User.Identity.Name
End If
End Sub
</script>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ds_meobservingcolleagues">
</asp:GridView>
<asp:SqlDataSource ID="ds_meobservingcolleagues" runat="server" ConnectionString="<%$ ConnectionStrings:StPaulsDatabase %>"
SelectCommand="SELECT [observeTeacher], [observeBy], [observeReason] FROM [Observations] WHERE ([observeBy] = @observeBy)">
<SelectParameters>
<asp:Parameter Name="observeBy" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
This is not resulting in any error. But it is not returning the records that it should be either.
Please can anyone help? I'm sure it shouldn't be this difficult to do
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
1 Has the current user been granted login access to the trusted SQL Server?
2 Has the current user been granted access to a specific database?
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?
Thanks,
Sean
Hi Guys,
I have an Intranet web application and I have to use windows authentication. They are 4 main users group to access the application, each with different set of permission on my store procedures.
I have created a windows group for each of those group in my windows environemnt. My DB Connection String in web.config is set to Integrated Windows Authentication ( Integrated Security=SSPI)
I have done all configuration based on this article: http://msdn2.microsoft.com/en-us/library/ms998292.aspx
I have 4 different database Roles in my database and 4 different database users. I also add NT AUTHORITYNETWORK SERVICE as a member of each of those database Roles.
Now I get connected, fine and set permission on the database roles.
1 thing I want to know, How Can I get the Windows Group name in the database, which has the same name as my SQL Login and Database Roles or User
I tried all SUSER_SNAME() and ... they all return NT AUTHORITYNETWORK SERVICE
I want to get AppPowerUser Role or windows group name. I think I should be able to because I am using windows authentication.
Any help is appreciated.
Mehdi
Hello Everyone,
I am backing up my all the databases (around 50 servers and 500 database) using scheduled jobs. I backup my transaction log files at every two hours of interval and databases on daily basis.
I have set option of "Write to Windows Application Event Log" - When job fails during the creation of job in the "Notification Tab" of Create New Job.
As per documentation SQL server automatically records some of the events to windows application event log. After succesful completion of Log backup and DB backup, event gets logged in Windows Application Event Log. And when job fails, I get two events logged in the Windows Application Event Log file, one due to my setting and other automatically by SQL Server.
Due to this Windows event log file is growing much faster and I have to clear it in every 3-4 days.
My quesiton - Is there a way such that to get only failures event logged in Windows Application event log file and any successful backup jobs should not go to Windows application event file?
Can any suggest some idea what should I do?
I am using SQL Server 2000 with SP3 on Windows 2000 Advanced server. Some of my databases are still using Version 7.0/6.5 on NT.
Thanks.
Dear GroupI wondered whether there's a function or script that will show mewhether a user is currently logged-on to a MSSQL 2000 database? I'musing SQL Authentication.Thanks very much for your help & efforts!Have a nice day!Martin
View 2 Replies View RelatedHi,
I wanted to know How can i find out that with which user i am logged in. Is there any command to find out the same.
( like in oracle, we have command -- showuser, which will return the logged in user of your session).
Thanks in advance.
-- Chetan
Hi There
I am in the process of cosolidating sql servers.
One of them literally has hundreds of sql logins, obviously i need to clean these up, however i a not sure how to determine when a user last logged in ?
How can i find out the last login date of a user ?
Thanx
Hi!
I would like to select the 10 latest logged in users and then display them in a gridview-control....
But Iam not good at SQL, so I would really appreciate if someone could help me to write the select-part for this!!
(I use the database 'ASPNETDB.mdf', which is automatically created by VVD 2005 Express Edition)
NOTE: I am talking about roles in my sql server - NOT in asp.net. I need to create a stored procedure that retrieves the roles that the currently logged in sql user has for a different database. I have the code that gets the roles for the user, but it only works if the user is in the database. I want to be in one database, and get the roles for a different database. I have tried using USE DATABASE, but this is not allowed in a store procedure.
View 10 Replies View RelatedI can get a list of all databases in SQL Server using
sqlText = "select name from sysdatabases order by name".
How can I get such a list for the currently logged in user only?
Thanks in advance
Harold Hoffman
How do I refer to the user logged on to the database?
I can refer to a table as dbo.tblApptDaily,
but how do I refer to
[current user].tblApptDaily?
Create Procedure "prcCreateApptDaily"
as
Create dbo.tblApptDaily
(ID int primary key, ApptTime datetime,
book1 numeric, book_Status1numeric default (0), PN1 varchar (50), ApptID1
int,...LastID int)
Hello.I realize that this question has been asked before, but I still can't get it to work. Below are some links that might be of help:http://forums.asp.net/p/1159666/1913519.aspx#1913519http://forums.asp.net/p/1161930/1924264.aspxhttp://forums.asp.net/p/1116601/1732359.aspx#1732359http://forums.asp.net/t/1104718.aspxhttp://forums.asp.net/p/1096290/1655706.aspx#1655706http://forums.asp.net/p/1110162/1707952.aspx#1707952 Basically, I need a DropDownList to display only projects for which the logged in user is assigned as leader. The [Projects] table contains an integer ProjectId, a string ProjectName, a uniqueidentifier ProjectLeader, and other fields. Can someone help me with the SQL query and code? * Here is the definition of the SqlDataSource: <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>" SelectCommand="SELECT [ProjectId], [ProjectName] FROM [Projects] WHERE ([ProjectLeader] = @Leader)" OnSelecting="SqlDataSource5_Selecting"> <SelectParameters> <asp:Parameter Name="Leader" Type="Object" /> </SelectParameters> </asp:SqlDataSource> * Here is the definition of the SqlDataSource5_Selecting method: protected void SqlDataSource5_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters("@Leader").Value = loggedInUserId; } where loggedInUserId is a global variable of type System.Guid. It has been evaluated in the Page_Load event to as: loggedInUserId = (System.Guid)Membership.GetUser().ProviderUserKey; Now the first problem I encounter is that when I run the page, the compiler complains and says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." The second problem is when I insert the line: SqlDataSource5.SelectParameters("Leader").DefaultValue = loggedInUserId; in page_Load. The compiler again says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." I've spent a long time trying to figure it out, but could not solve it. I would appreciate it if someone can help me out. Thank you very much.
View 11 Replies View RelatedI have created a SharePoint external list using SQL Server, the data source is SQL Server. Is there way that I could get the logged-in SharePoint user and stored in SQL Seever?
View 2 Replies View RelatedI need to get the computer details of the person that logged in. I have a client that needs me to write a computer asset logging system for them. This is a one table database that will keep all the computers in the company's details like serial number, bios serial number, ram size disk size, mother board info etc. This is a vast number of details and what they want to do is when for instance the ram changes e.g. taken out or added they need the program to update the table with the new information once the computer starts again.
View 4 Replies View RelatedI have created table called Login in sql server where i have column usercode, email and login_date (login_date is datetime type)So, i created web application using .net. whenever user logged in, i am allowing based userLoged table and  i am inserting into login table.
login table usercode email    login_date
001      a@gmail.com   2015-11-18 22:02:41.153
001      a@gmail.com   xxx
.
.
.
I have another table called userLoged where i have column usercode,email and web_accessÂ
UserLoged table usercode email     web_access
001     a@gmail.com  Y
Now, if a@gmail.com (001) is not logged in for 45 days, i need to update web_access to be 'N' how to know if he /she not logged for 45 days.
Assume I have a heirarchy like the following:
- John Smith
- James Jones
- Robert Allen
- Lisa Andrews
- Bob Thompson
Now, I have a report where whoever is logged in will only see data for themselves and those below them, so John Smith would see everyone including himself, but Lisa Andres would only see herself and Bob Thompson. James Jones would be able to see everyone except John Smith.
How do I go about implementing this code, for example in an asp.net page where one of the user's logs on to view the report. Currently, there is a T-SQL function that creates a user heirarchy table, but it is very slow and I am curious if SSRS 2005 has any new capabilities in handling this.
Thanks,
Saied
Is this level of security possible in RS 2005? if so how?
Any guidance would be appreciated.
Thanks.
How do I insert the UserName of the Logged in user into the DB field UserID. I created a web form page, and added a form view control set the page default to Insert Mode. The Page is used to insert data. I have a hidden field called UserID that I would like to capture the Logged in user. I have logged in to the default page with userID and password, added a new record, and the db table field UserID is empty. I have been trying to figure out what I am doing worng, but no luck, Please suggest the best way to do this. I have listed what I have done thus far?
I have added the login Control from Login and added it to the page as a hidden field thinking that I needed to have this field on the page to get this to work where you copy the value in the Loginname field to the useridTextbox on the formview1 insert template. I read on line where the method below is better. Please tell me what I am doint wrong.
System.aspx---------------------------------------------------------------------------------------------------------------1) <asp:FormView ID="FormView1" runat="server" AllowPaging="True" DataKeyNames="SystemID" DataSourceID="SystemSqlDataSource1" DefaultMode="Insert" Width="583px">2) <asp:TextBox ID="UserIDTextBox" runat="server" Text='<%# Bind("UserID") %>' Visible="False"></asp:TextBox><br />3) <asp:SqlDataSource ID="SystemSqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RCMISConnectionString %>" DeleteCommand="DELETE FROM [tblSystem] WHERE [SystemID] = @SystemID" InsertCommand="INSERT INTO [tblSystem] ([SystemID], [SystemDesc], [Inactive], [TimeStampEntry], [TimeStampUpdate], [UserID]) VALUES (@SystemID, @SystemDesc, @Inactive, GETDATE(), @TimeStampUpdate, @UserID)"4) <InsertParameters> <asp:Parameter Name="SystemID" Type="String" /> <asp:Parameter Name="SystemDesc" Type="String" /> <asp:Parameter Name="Inactive" Type="Boolean" /> <asp:Parameter Name="TimeStampEntry" Type="DateTime" /> <asp:Parameter Name="TimeStampUpdate" Type="DateTime" /> <asp:Parameter Name="UserID" Type="String" /> </InsertParameters> </asp:SqlDataSource>System.aspx.vb---------------------------------------------------------------------------------------------------------5) Partial Class MemberPages_RCM_frmSystem Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'UserIDTextBox.Text = Membership.GetUser().ProviderUserKey.ToString() End Sub Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted 'Set the UserID Value to the currently logged on user's ID e.Values("UserID") = Membership.GetUser().ProviderUserKey End SubEnd ClassThank you in advance,
Hi,
I have some sensitive and non-sensitive info/fields in one of my reports. Is there a way to hide/show the info based on the logged in user? or do I have to create separte reports for each type of info like one for sensitive and another one for non-sensitive info.
Thanks inadvace for your help!
We are using Windows authenication within our system, and I was wondering how it would be possible to determine if the user conected to the SQL SERVER instance was a member of a particular active directory security group?
Thanks.
We are looking at developing an SQL Server 2005 Database and I would like to use Windows Authentication rather than SQL Server Authentication to connect our client app.
In our development environment, we have two Servers, one being used as a file server and the other as an SQL Server. We have now set up a domain using the file server as the domain controller. (We had previously been set up to use a workgroup).
I have set up an active directory group called SqlDevelopers and added an active directory user called Jonathan to it.
On the SQL Sever, in management studio, I have set up a new server login which uses windows authentication called DomainSqlDevelopers. I used the GUI to verify I could see the domain and the group.
The default database is set to a test database on the server. A user in the test database is mapped to the DomainSqlDevelopers and given the Roles dbo, db_datareader, db_datawriter.
To test the log in, on the server, I logged out as administrator and in as Jonathan. I could successfully access the server through management studio using windows authentication.
However, if I log in as Jonathan on my client PC and try to access the SQL Server using management studio and windows authentication, I have problems.
The first time I try I will get a timeout error. If I try again will get either:
Login failed for user ''. The user is not associated with a trusted SQL Server connection
Or
Cannot generate SSPI context
I can€™t determine any pattern to which of the above errors I get.
However, if I log in as administrator on my client PC, I can connect to the server using management studio and windows authentication.
Sounds like Active Directory/Domain or other Network issue (Not really my area). I would be grateful for any help.
Thanks,
Jon
I have a user who cannot login to a .mdb when he is logged
in to Windows, on his workstation or any other.
No one can login to the .mdb when he is logged in to Windows.
He CAN login to the same .mdb if someone else is logged in
to Windows, either on his workstation or any other.
When the user logs in he gets the following error:
MS SQL Server Login
Connection Failed:
SQL
State: €˜28000€™
SQL Server Error 18456
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed
for user €˜domainusername€™
I have removed and recreated the ODBC Connection
while he is logged in. I have tried changing the trusted login to
administrator (in the window that pops up when he fails to connect).
He has the permissions he needs to the .mdb because he can
login under anyone else€™s profile.
I tried copying his profile to a brand new user and ended up
with the same result.
This leads me to wonder if there is some corruption in one
of his profile€™s .dat files or ???
This is the boss€™ account and he does not want to be given a
new username and profile€¦
Microsoft SQL Server
2000 - 8.00.760 (Intel X86)
Running on MS Windows 2003 Server Standard SP1
All workstations are XP SP2 with MS Access 2003 SP2 (from
Office Pro distro)
We use roaming profiles.
All systems within the same physical and logical network and domain.
Any help you can provide will be greatly appreciated.
Please let me know if there is any additional information you need.
Thanks,
Mike
Hello everyone,
In SQL Query I need to update a field to my ASP.NET app which has a variable User.Identity.Name() where it contains the Form Authentication of current logon user, SQL has the suser_name() function but this is not what I needed because it returns the user accessing the Database.
UPDATE [tblData] SET [UserUpdated] = User.Identity.Name() <= this failed because SQL doesn't recordnize the User.Identity.Name() function. Is there any method I can use to achieve this?
Thanks in advance.
Hi guys. I created a database "MyDatabase" using an AD Account "user01" which is a member of a domain group "GroupA". I have created a login in my SQL Server 2005 for this AD group. I have also added the said login as a user of my database. I created other AD users and put them in the same group. When I try to access the database using the other users, I get this message
The server principal "MyDomainuser02" is not able to access the database "MyDatabase" under the current security context.
This KB tells me that my situation is triggered by the first case in which the owner of the database is windows authenticated. I have already set the database to be TRUSTWORTHY. I haven't tried CERTIFICATES though. I believe I haven't done anything complex to warrant this solution.
I'm really lost with the myriads of principals and securables that SQL2005 has. All I want is just to execute a certain set of stored procedures. I can't do this because I can't even execute "USE MyDatabase". What are the permissions I need to give to "GroupA" ?
Hi,
I am u sing a script to create databse logins en users. Every thing works fine, but...
How can I set the default database for a user with TSQL?
ArtBrainFart
Am using SQL Server 2000, and am writing some audit triggers. How do I getthe the name of the user, eg. user_id or current_user. Just spent an hourlooking through the SAMS book for this, and can't find any reference.
View 5 Replies View RelatedI have an application that controls user logins, passwords, etc. at the front end for a SQL database. I am in the stage of migrating to SQL2005 and cannot get the TSQL code to allow a user to change their own password. Here's the background;
The ADMIN of the app is a Sysadmin on the SQL server and can create logins, set roles, etc. Assume the Admin creates a user TOM with a password of xxxx. This works fine using the create login statement from a Connect.Execute statement from my app like so;
"Create Login 'TOM' With Password 'xxxx', Default_Database = 'myDB', Check_Policy = OFF"
TOM will be setup with db roles as well
When TOM logins into my app, he will have to change his password at some point. The TSQL code I am using (which fails) is executed by TOM who has a connection to the SQL db because he is logged into the app.
"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx', Check_Policy = OFF"
At this point I get an error:
RunTime error -2147217900 (80040e14)
ODBC SQL Server Driver][SQL Server] Cannot alter
the login 'TOM', becuase it does not exist or you do
not have permission.
Obviously it exists since TOM is currently logged into the SQL Server. So if it's permissions related, what permissions does a user need to change his/her password? Or is there another way to do it?
Thanks in advance for your help.
CH
Hi,
I am using SQL server 2005 (express). I have a user like "test" with some password.
After login with this userid and password i tried to get the current user executing
Print CURRENT_USER but its displaying the result as 'dbo' instead of 'test'. In server roles for this particular login i have marked all the options.
Can anyone tell me why i am getting like this? What permission/setting i am missing?
Thanks
Shobha
Hi,
I have created a DataSet that queries a table containing users names.
Code Block
SELECT * FROM myTbl
WHERE UserName = @Username
How do I automatically set @Username to the current user?
Thanks.
After some help from the folks in the Security forum, I have some of the answer I need. Hopefully someone here can help me figure out the best method of using that information to find a final solution.
To start, here's a brief synapsis: I'm developing an App in ASP.NET 2.0, using forms-based authentication and the Login Control. I am able to login fine as far as I can tell, since I am able to proceed to the destinationURL without error. From there, I button click to the application in question. Here is where I have the problem.
I want to run a select query on a SQL table where a field = the current user's Username. I cannot find any examples of doing this. I have managed to figure out how to retrieve the current user's username via the Membership.GetUser method, but I can't seem to figure out how to apply that to my SqlDataSource Control and get a valid response.
a snippet of my control's code follows:
<script runat="server"> - This is run inline, not code-behindProtected memUser As MembershipUser
Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)memUser = Membership.GetUser()End Sub</script>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SWGToolsetConnectionString %>"SelectCommand="SELECT [CharacterName] FROM [Characters] WHERE ([UserID] = '<% =Server.HtmlEncode(memUser.Username) %>')"></asp:SqlDataSource>
If anyone can tell me what I can or should be doing differently, I would appreciate it.
I have a SQL 2005 question for you. In the SQL server Management Studio. When I right click on the database and use the SQL Server Import and Export wizard, I can import databases but cannot export them.
The error says:
The current user is not an owner of the selected database. This might cause some operations with this database to fail.
I use sa to loginto the server Management Studio, does anyone know how to create a database where the user and owner are the smae so I can export?
Is there a way to only return the data that the user is allow to see?
Example:
A student login into the Report Server and open the Class Report It will only display all the classes this student is in.