How Can One User View Other Users That Belong To A Database 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?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Accessing Windows Users Assigned To A Database Role
Is there a way to find a list of Windows User accounts that are directly or indirectly (through Windows Group membership) assigned to a database role? I could put work in to CLR programming or using a Linked Server to Active Directory, but if there is a sys.* view available that can provide me this information directly it would be much easier. I'll be looking into this further myself anyway and posting an answer if I can find one, but if anyone has suggestions... well, thanks!
View Replies !
View Related
Adding A User/role To All User Database
Hi everyone, 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: DECLARE @Count smallint declare @dbVarchar(20) SET @Count = 1 WHILE (@Count <=(SELECT MAX(ID) FROM #temp)) BEGIN Exec ("USE @db") EXEC sp_adduser 'test' EXEC sp_addrolemember 'my_role', 'testrole' SET @COUNT = @COUNT + 1 SET @db = (SELECT dbname FROM #temp WHERE ID = @Count) END It seems that the "Use @db" part has no value for the variable @db. Does anybody knows how to solve this?
View Replies !
View Related
How To Show Only Records That Belong To The Logged In User.
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 Replies !
View Related
Granting Permission To A Database User To Alter Database Role
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??
View Replies !
View Related
Get The Currently Logged In User's SQL Role In A Different Database
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 Replies !
View Related
Create User - Permit In Database Role
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? pls help. Thnx
View Replies !
View Related
User Or Role Already Exists In The Current Database
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
View Replies !
View Related
Will User's Permission Be Overriden By Database Role?
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
View Replies !
View Related
Determining Database Role Of Querying User
I know that I can use: IS_MEMBER('MyRoleName') = 1 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. Thanks, Kayda
View Replies !
View Related
How To Drop A User Defined Database Role In 2005?
Using Studio, I created a user defined database role but I can not delete it because "TITLE: Microsoft SQL Server Management Studio ------------------------------ Drop failed for DatabaseRole 'test1'. (Microsoft.SqlServer.Smo) ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138) I am quite annoyed because the "owned schema" is db_owner, which can not be unselected. Quite an innovation. How do I drop this relationship?
View Replies !
View Related
How To Fix "Error 15023: User Or Role '%" Already Exists In The Current Database
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!!!
View Replies !
View Related
Sql DB BuiltInPower Users Role
I want to programmatically, using C#, provide BuiltInPower Users permission to a database. That is, I want any user who is logged into the computer where they belong to the Windows Power Users group to be able to log into Sql Server and to a database. I am able to do this nicely in the English version of Windows XP and Sql; however, the same procedure, for example, in the German version fails due to a change in spelling of 'Power Users' in german. In C# one is able to use the enum WindowsBuiltInRole.PowerUser which helps application specific permission issues, but how does one do this when using international XP versions connecting to Sql server. That is, how does one establish BuiltInPower Users permissions for SQL when using international versions of the operating system. Or maybe I am approaching this the wrong way.
View Replies !
View Related
New Role And Users Creations
Hello, We have 700+ databases on SQL server 2005, and we constantly adding more db.. I know, this is not the best... I have the same group of people who needs to have identical permissions on all existing and newly created databases... How can I acchieve that in efficient way? Ideally I would like to create new database role which has read and write access to all objects in all databases, and add users to this new role. Do those users need to be added to each database? How can I assure that new database will have role and users already added after the creation? Can I add it to the model and all other dbs will have it?
View Replies !
View Related
Can't Make Database Role A Member Of Another Database Role In 2005.
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?
View Replies !
View Related
Adding Users To A Role In Bulk
I have a need to add all the users listed in the sysxlogins table to the db_datawriter role. I wrote a proc that does this. It indicates that each user is successfully added to the role, but they aren't. If I look at the user in EM, they don't have that role checked. I've tried all the obvious stuff like close EM and re-open, etc...it doesn't help. Here is the proc CREATE proc sp_MyProc @DBName varchar(256) As Declare UID_Cursor Cursor For Select Name from master..sysxlogins Where Len(Name) = 7 Order By Name Open UID_Cursor Declare @Name as varchar(256) Declare @TempString as varchar(8000) Fetch Next from UID_Cursor into @Name While (@@Fetch_Status <> -1) Begin Set @TempString = 'sp_addrolemember ''db_datareader'', ''' + @Name + '''' Exec(@TempString) Fetch Next from UID_Cursor Into @Name End Close UID_Cursor Deallocate UID_Cursor
View Replies !
View Related
Who Cand Add Users To The Db_securityadmin Role ?
I have user which is a member of the db_securityadmin database role. It has no other permissions applied to it. When I login with this user, and try to add some other database users to the db_securityadmin role, I receive an error stating that I don't have the permissions to do this: User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247) Is this the normal behaviour, or is there something wrong ? I am using SQL Server 2005. From what I know, this works fine with SQL Server 2000, but it doesn't work as I expected with SQL Server 2005.
View Replies !
View Related
Get List Of Users Not In A Specific Role
ere is the make-up of the tables: [dbo].[PortalUser]( [PortalUserID] [bigint] IDENTITY(1,1) NOT NULL, ... [dbo].[Role]( [RoleID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](250) NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_Roles_IsActive] DEFAULT ((1)) ... [dbo].[PortalUserRole]( [PortalUserRoleID] [bigint] IDENTITY(1,1) NOT NULL, [PortalUserID] [bigint] NOT NULL, [RoleID] [bigint] NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_PortalUserRole_IsActive] DEFAULT ((1)) I'm asking to get a list of portalUsers that do not have a PortalUserRole records for the rolename I'm checking against. And don't ask me why the person who coded this is searching on rolename not ID. But this is how we're doing it for now.
View Replies !
View Related
Programmatically Adding A User To The System User Role
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?
View Replies !
View Related
User Can View All Database Names
I create a user then I create a database and assign my newly created user as owner When I login as this new user everything works fine BUT my newly created user can still see other database names even though he is not allowed to use them. How can I prevent him from seeing other database names. P.S. I use the GUI Thank you
View Replies !
View Related
Advice On Security Model For XML Web Services For Many Users In Active Directory Role
I am working on the security model for an application that will be used by 100s of users with a dedicated SQL 2005 database for this application and access via SQL XML Web Services. The client has asked to make it "open" during alpha testing such that anyone can access the web services without having to set them up first. Is there a way to do this? The best I can figure is to use mixed mode security and hard code a login and password. Any method using Windows authentication would require that I add every user at a minimum to the database. In production, all users will have an active directory role specified that determines if they should have access to the web services or not. However, it is my understanding that to use Windows authentication, I would still need to add each individual user at a minimum as a Login to the SQL Server, and under best practices also as database users with permissions granted to the endpoint. Am I correct in the above, or is there a more efficient way to achieve these results? Thanks -L
View Replies !
View Related
How To View Permissions Of User-defined Database Roles In Management Studio?
As part of our security project, I've done the following when logged in as 'sa': Created database roles 'dbrole1' within dbAccount Created login and user 'user1' and added user to be a member of 'dbrole1' Granted execute permissions on sp1 and sp2 to 'dbrole1' However, I didn't see the above permissions listed in SQL Server Management Studio - Database - Security - Roles - Database Roles - 'dbrole1' properties - securables Any ideas? Thanks!
View Replies !
View Related
Application Role How Query A View Whose Reference Table Is In Another Db?
Hi everybody. I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1 I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME() I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error: The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work? The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated. Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania
View Replies !
View Related
What Role Should Be Given To The End User?
Hi Everybody, The end users are using VB Applications, there they will be entering datas. Those datas will be stored in the SMS Database. My Problem is through which Roles (Fixed Server Roles or Database Roles) I should attach these end users. If it is a Fixed Server Roles, Other than sysadmin role in which role I should attach this end user. Like that other than DB_Owner in which role I should attach these end users to the Database Roles. Can anyone guide me please. thanks, Srinivasan.
View Replies !
View Related
Adding A Database Role To A Database Role
Hi, I have SQL server 2005 (Developer edition) installed and I want to add a database role to a database role. It is working on my SQL 2000 server,however, when I tried to do the same thing on SQL2005, It didn't allowed me. When I go to the Add role and then adding members to the role, the browse screen does not allow me the choice of object type Roles. It only shows me "Users". Can someone please help me with this and provide me some information of how / what should I set to get the Roles in object types list so I can add a role to a role. Thanks
View Replies !
View Related
User/Role Check
Can anyone out there help me write a sp to determine if a user has a certain role? I'm trying to use "sp_helpuser @UserName". Can I declare a cursor with "EXEC sp_helpuser @UserName"? I'm not having success with this. What about SELECT [GroupName] FROM EXEC sp_helpuser @UserName WHERE [GroupName] = @GroupName? Again, syntax error. Is there already a better way that someone knows of?
View Replies !
View Related
User With Server Role
I have a user with DBCREATOR Server role only. That user is able to create database but create table permission denied. how would I set permission on this user, so that this user can create databases and automatically becomes the DBOWNER of that database and can do any action on that database. Thanks,
View Replies !
View Related
Cannot Use The Reserved User Or Role Name 'db_datareader'.
Hi, I have got a problem. When I try to access my database table Users, I get the following error: SELECT permission denied on object 'Users', database 'Users', owner 'dbo'. So I tried to grand this select command in MS Web Data Administration, but it doesnt work. When I try to grand db_datareader role to dbo, I get the following error [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the reserved user or role name 'db_datareader'. Does someone have an idea where could be a problem?
View Replies !
View Related
Checking The User's Server Role
I would like to determine if a particular user has sysadmin serverrole. Is there a way to do this via the connection string? Currentlyour code checks if a login is valid using SQLDriverConnect, however weneed to be certain that the user can login and modify the schema.Is it possible to fetch a user's server role to determine if it has asysadmin server role?
View Replies !
View Related
Backup User - Server Role?
I wish to create a user that can backup any or all databases in our SQLServer 2000 Instance. I thought there would be a server role for thisfunction, however I can only find that after I grant access of adatabase to the user, then I can choose ds_backupoperator.I want to create a user that will have the ability to backup all thedatabases. I dont wish to have to come back to the server after a newtable is created and add the backup user to that table.I want SA w/o the full privilage...am I crazy?Any Suggestions?TIARobBackgroup: We currently have about 10 SQL servers, and adding more inthe future. I am using SQLBackup from Idera along with HP SurestoreTape library (60 slots,2- DLT8000 drives with 40/80 GB capacity) withArcServe from Computer Associates. I want to have this automated tobackup to file then tape, regardless of what databases get created.
View Replies !
View Related
Retrieving User/Role Privileges - How ?
Hi, I need to read and subsequently modify the privileges (rights) of a certain SQL Server user / role from within a Visual Basic Program. Modifying seems to be easy using standard statements like GRANT/REVOKE. But what about reading all the rights a user has ? I have researched SQL-DMO, but didn't find what I'm looking for. Any idea ? Mike
View Replies !
View Related
Retrieving User Defined Role Name
Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver. Greetings, Godofredo
View Replies !
View Related
Checking For User's Role Programatically
i wrote a script that attatches database to the SQLServer programatically, only problem is that i first want to know whether the user has permissions to attach db. i'm not sure which roles can attatch database, nor how to check what is the user's role. if anyone can help me generating a script (c# would be the best, but i'm not picky) returns true/false respectively. other directions and help would also be very appreciated 10x in advance! good day y'all
View Replies !
View Related
Drop Role, User, Login
Okay I figured out how to determine if stored procs and funcs exist before dropping them. How do I do the same for ROLE, LOGIN, USER? I want get rid of annoying messages in my scripts when trying to drop something that doesn't exist. Server 2005 and Server Express 2005 Thanks
View Replies !
View Related
Exporting User/Role Permissions
I am not a DBA so please be gentle... I am trying to export all of the user and role permissions out of several databases for auditing purposes. I see the Users and Roles listed under the Security tree view when I log into the database, but I do not see an option to export or query the permissions. In addition, we do not have any tables that reference user permissions in our databases. So, how would one go about exporting or querying this information? I've seen similar topics where they recommend querying sys tables to gather the info, but I don't see those tables either. Any help would be greatly appreciated. All my thanks! - Isaac Edit: I should add in that I am connecting to 7 and 2k DBs using 2k5 SMS. Not sure if that makes a difference...
View Replies !
View Related
|
TRACKER
