How Can One User View Other Users That Belong To A Database Role?
Nov 15, 2006
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 8 Replies
ADVERTISEMENT
May 20, 2014
I have been struggling with this one for awhile now.I have a domain group which only must view the steps and history of all agent jobs.I have added the group to the sqlagentreadergroup.I have created a new role and denied this role,add job,update job,delete job etc execute permissions.But the user still can change ,delete or create a new job.
All the groups and users in th new role,does not have sysadmin rights.
we have sql 2012 enterprise version
What else can i try.I need this for audit purposes.
View 7 Replies
View Related
Mar 3, 2006
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 1 Replies
View Related
May 31, 2007
Ideally, I'd like to have the reports use windows authentication, ie grab current user/password so long as user does not have to enter it manually. If that is not possible, then how do I make it so user does not have to enter this info manually? My theory is that I have IIS set up incorrectly for doing this, but I'm not familiar enough with either reporting services or IIS to find out.
View 1 Replies
View Related
Jan 23, 2001
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 1 Replies
View Related
Sep 28, 2007
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 Related
Sep 5, 2006
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 1 Replies
View Related
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.
View 10 Replies
View Related
Sep 21, 2006
Anyone have a tsql query that will give me a listing of database roles and their users already put together?
View 1 Replies
View Related
Jan 7, 2004
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 4 Replies
View Related
Oct 9, 2000
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 1 Replies
View Related
Mar 14, 2008
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.
View 1 Replies
View Related
Dec 26, 2006
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 3 Replies
View Related
Dec 7, 2007
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 4 Replies
View Related
Jun 5, 2007
Hi,
I want to know how to assign a normal user to take backup,Restore and attach and detach the database in sqlserver2000.
Please can anyone help me to find out which server role or database role will suites my requirements.
Regards
Md S.Hassan
View 1 Replies
View Related
Mar 9, 2007
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 3 Replies
View Related
Sep 20, 2001
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 2 Replies
View Related
Aug 17, 2000
Hello....
Does anyone know if two different database belong to two different applications stay in the same physical SQL server 7.0 with one server license would affect their performance?
Would the performance be better off if each database stored in different physical SQL Server 7.0 with different server license?
Thank you in advance!
View 1 Replies
View Related
Apr 9, 2008
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 1 Replies
View Related
Aug 18, 2006
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 1 Replies
View Related
Feb 2, 2007
Hi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel.
View 1 Replies
View Related
Nov 19, 2004
Hi folks, i create a procedure in master db that kill users spids. Giving EXECUTE permission on the procedure to users without giving processadmin role doesn't work! Any guidelines?
Howdy!
View 3 Replies
View Related
Feb 23, 2004
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 4 Replies
View Related
May 30, 2008
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 6 Replies
View Related
Jul 28, 2006
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 6 Replies
View Related
Jan 9, 2006
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 1 Replies
View Related
Dec 27, 2006
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 1 Replies
View Related
Mar 25, 2008
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
View 6 Replies
View Related
Jul 7, 2015
Does securityadmin Server level role can add, alter logins and corresponding users on all databases ?Â
If not what is the best role other thn SA to have to manager logins and users.
View 4 Replies
View Related
May 18, 2007
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) ?
thanks
View 2 Replies
View Related
Apr 17, 2008
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 2 Replies
View Related
Feb 21, 2006
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 1 Replies
View Related
Oct 12, 2015
When I create logins for SQL Server 2012, something strange happens. When I assign the sysadmin role to a login, the login loses the access to the network drives (for example when creating or attaching databases). The only possible place is the c:drive of the computer that is running SQL Server.
View 11 Replies
View Related
