Get The Currently Logged In User's SQL Role In A Different Database
May 14, 2008
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.
I try to add a db_role or a user to all my databases with one script. Although parsing doesn't report any problem I get a Syntax error during execution. I first select the database names into a #temp table which has two columns, ID and dbname. After that I use the following code:
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
Hi, I hv an application which is using ASP.net. The connectionstring in web.config is <appSettings> <add key = "constring" value = "Initial Catalog=mydatabase;Data Source=mypc-pc;User ID=User1; Password=password1"/> </appSettings>"
Then, i hv created a user in SQL Server 2000 which is User1. What should i put for the database role? db_owner or just db_datareader and db_datawriter?
Hi! I've restored a backup from server A in server B. In server A I used to have a db_owner, called sitebase. After restoring the backup in server B, I created the user sitebase again and tried to grant him db_owner through Server Manager but I got the error: user or role already exists in the current database. How can I avoid this situation and grant db_owner to him? Thanks, Fábio
How do I get a particular user to be a choice under the db_owner role for a particular database? The user is listed under logins and even shows to be the db_owner for the database under the database access tab of the login properties. This is SQL 2000. Thanks, David P.
in SQL server 2005, Database User's permission will be overriden by the database Role's permission or ottherwise? For example, a userA is owner of table AA so it has all permisions on table AA but the user is a member of GroupB but group B has no permission to access to Table AA. What happen on User A?. has it permission to access to table BB or not? How can I find document or example about this? Please help me, thanks so much
to determine if a user is a member of a predetermined role, but how do I ask what the role name of the querying user is instead? Basically I want to do something like:
SELECT * FROM CityTable WHERE City IN (SELECT City FROM CitySecurity WHERE SelectGroup = ROLE_NAME)
I'm doing this because the "CitySecurity" table contains security information from another system that has been imported into SQL Server, where the 'SelectGroup', as part of process, will be named the same as the SQL Server role.
After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.
In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.
In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.
Any advice on how to duplicate the behavior from SQL2000?
I used a backup copy of our production DB (residing in our prod machine) to do a database RESTORE to our test DB (residing in our test machine). This step was successful. However when I tried to access the test DB via Peoplesoft application, I am unable to logon. Only then did I notice that all the users, with the exception of "sa", were gone. When I attempted to add a user via Enterprise Manager's Action - Add Database User, I get the message, "Error 15023: User or role '%' already exists in the current database. What's the best way to fix this without resorting to copying the source server's master database (If i do this, I risk clobbering some other DB's that are present in the target server but not in source server)? Any help you can provide will be greatly appreciated!!!
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
Hi, 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).
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 ?
In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?
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)
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>
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
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.
I 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?
I 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.
I 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.
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.
We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?
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,
Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
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.
