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 Complete Forum Thread with Replies
Related Forum Messages:
Adding A Member For Database Role
Hi, I have a login 'testuser' on test server. This login was 'db_datareader' for database called 'invent_test' on test server. I deleted the whole database and restored the production backup as 'invent_test' database. Now I am trying to modify login 'testuser' by adding it as 'db_datareader' for 'invent_test' again but it gives me an error that 'this user or role already exists for current database'. I can not see 'db_datareader' or even 'invent_test' options selected in login properties for 'testuser'. Also I can not find 'testuser' in database roles -->db_datareader member list. Is this a bug with sql server 7.0? Or how should I give this user priviledges to read? I dropped the login and recreated it. Server is also rebooted meanwhile so there is no possibility for cashe problem. Still same error is there. Is there any thing that I am missing here? I really appreciate your help. I am very frustrated now.
View Replies !
Adding A Member For Database Role
Hi, I have a login 'testuser' on test server. This login was 'db_datareader' for database called 'invent_test' on test server. I deleted the whole database and restored the production backup as 'invent_test' database. Now I am trying to modify login 'testuser' by adding it as 'db_datareader' for 'invent_test' again but it gives me an error that 'this user or role already exists for current database'. I can not see 'db_datareader' or even 'invent_test' options selected in login properties for 'testuser'. Also I can not find 'testuser' in database roles -->db_datareader member list. Is this a bug with sql server 7.0? Or how should I give this user priviledges to read? I dropped the login and recreated it. Server is also rebooted meanwhile so there is no possibility for cashe problem. Still same error is there. Is there any thing that I am missing here? I really appreciate your help. I am very frustrated now.
View Replies !
Adding A Member For Database Role
Hi, I have a login 'testuser' on test server. This login was 'db_datareader' for database called 'invent_test' on test server. I deleted the whole database and restored the production backup as 'invent_test' database. Now I am trying to modify login 'testuser' by adding it as 'db_datareader' for 'invent_test' again but it gives me an error that 'this user or role already exists for current database'. I can not see 'db_datareader' or even 'invent_test' options selected in login properties for 'testuser'. Also I can not find 'testuser' in database roles -->db_datareader member list. Is this a bug with sql server 7.0? Or how should I give this user priviledges to read? I dropped the login and recreated it. Server is also rebooted meanwhile so there is no possibility for cashe problem. Still same error is there. Is there any thing that I am missing here? I really appreciate your help. I am very frustrated now.
View Replies !
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 !
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 !
Find Out If Current User Is Member Of A Role
I need a stored procedure to find out if the current user is a member of a certain role. I want to pass the role name and return a bit to tell whether he is a member or not. I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.DECLARE @usr varchar(32) SET @usr = USER EXEC sp_helpuser @usr But if the current user is a member of more than one role it returns multiple rows. So if I could do something like: DECLARE @grpName varchar(32) SELECT * FROM (EXEC sp_helpuser @usr) WHERE GroupName=@grpNameIF rowcount > 0 THENRETURN 1ELSERETURN 0END IF I know that doesn't work so how can I do this?
View Replies !
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 !
New Database Role
Hi, I have a database with 500+ tables and 1000+ views and equal no of stored procedures. I have a sql server login id which I have included in the following roles: db_datareader db_datawriter now the issues is.. i want to revoke delete permission from certain tables. Any revoke or deny do not affect the behaviour i.e. even after successfully executing revoke/deny statements on these tables for DELETE statement.. user is able to DELETE. pl advise the best way to revoke delete permission from a few (half dozen) tables out of the total. regards, D2
View Replies !
Database Role Expansion?
I have set up 3 database roles for an application database. The delima is one role "role_AdminUser" need to add and drop logins, and need to change the role for some members. They don't have the dbo rights. How could I expand the current database role without having to use server role to exec the sp_addlogin, (some sp resides in Master)? thanks D
View Replies !
Restore Database Role??
Can a role be created so that a user only has restore capabilities to only a specified database? IF that is not possible can he/she only have backup and restore capabilities without granting him the ability to backup or restore any other databases?
View Replies !
Database Role Question
I need to create a login that has read write to a database and also has the ability to only create and delete columns in all of the tables in a database. Is there a particular role I can use to get these permissions? If not is there a way to create this specific role that is needed. Thanks.
View Replies !
Database Role Permissions
Hello. I've moved a database from 2000 to 2005 and in 2005 I cannot see the permissions for this Database Role that I created. In 2000 you just right-clicked, selected properies and clicked on permissions. Am I going mad here or is this not an option in 2005. If not, how do I see the permissions that have been given to the role? The 2005 database I refer to is on standard edition SP2 Thank You
View Replies !
Create Database Role Via T-SQL
I have created the functionality to dynamically create databases and am now trying to figure out how to create database roles using T-SQL. I keep finding information about the sp_addrole stored procedure which is the first step, but how do you go about defining what permissions this role has via T-SQL? Thanks
View Replies !
Database Role Properties
database role properties/ permissions when i click permissions it takes forever to pop up the window. Anyone know why and how do i make it faster. http://www.sqlserverstudy.com
View Replies !
Database Role Permissions
Can we assign serverrole to a database role. For example i have a new role created as A Now can i add A to any of the server role.. Please advice.. Thanks, ServerTeam
View Replies !
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 !
Copy Permissions Of A Database Role
Brief description of the problem:My production server has about 50 databases and various permissions aregranted to public role on all these databases. Because of this any newuser added to any database gets unnecessary access to objects by virtueof being a member of public by default. I would like to fix this flawin the way the server is setup as below:1. Setup a new database role called NewRole on all 50 databases andcopy permissions to NewRole from public.2. Add all existing users to the new Role.3. Remove all permissions from public.Any suggestions on scripting this task are welcome.
View Replies !
Duplicating Permissions Of A Database Role
thanks for reading. i would like to create a new database role that has exactly the same permissions as an existing database role. the combinations of permissions are complicated enough to make this a time consuming task worthy of a script of some sort. any suggestions? i'm running SQL Server 2000 and can't find anything about copying roles unless it's through DTS from one DB to another. not sure if this is applicable to basically duplicating and renaming a role in one database. in case you're wondering why anyone would want to do this you may not be surprised to know that it's a government thing. some policy about differentiating between 2 roles -- even though they currently have exactly the same permissions, the similarities might change in the future. thanks in advance for your input.
View Replies !
Connecting To A Database From VB Using An Application Role
I know how to create an Application role in SQL server 7. Now how do I connect to the database from VB using that Application Role? I can't find anything about this topic anywhere. Is this the purpose of an Application role or am I way off? Thanks for the help Steven Abt StevenA@grsgroup.com
View Replies !
Add Role To Analysis Services Database
Does anyone know if there is a way to use a SSIS task to add a role to SSAS cube? At the moment I use Management Studio, Right Click "Roles" under the cube and go through New Role wizard, but I'd like to be able to include this in a SSIS package instead. Thanks Richard
View Replies !
Way To Retreive Database Role Permissions
I want to allow administrators of the program to change the permissions of the database roles. Is there a way to retreive the the specific permissions granted an denyed to a user and a database role. For instance, a store procedure that you call, pass it the database role and you get whether SELECT is allowed on table1, or if UPDATE is denied on table2, etc.
View Replies !
Create Database With Application Role...
Hi, I want my application to create database and I do the following things: 1)Create application role 2)Grant create database to application role 3)Activate application role 4)Create database and I get the answer: CREATE DATABASE permission denied in database 'master'.
View Replies !
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 !
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 !
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 !
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 !
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 Replies !
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 !
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 !
Application Role, App Role
Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) for the given connection string Dim connstring As String = "Data Source=Northwind;Initial Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;" Dim cmd As SqlCommand Dim param As SqlParameter Dim cookie As Byte() Dim cn As New SqlConnection(connstring) If (cn.State = ConnectionState.Closed) Then cn.Open() End If Please help.. Thanking you, Nirmala
View Replies !
SSIS Database Transfer Error - &&"Role Exists&&" Even Though DB Is Being Overwritten In Task.
Can't get over this error, and net searches reveal other postings similiar, but no answers. SSIS database transfer task (with overwrite) from SQL 2k source to SQL 2k5 destination fails with: Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [RFRSH_USER] " failed with the following error: "User, group, or role 'RFRSH_USER' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}). The Execute method must succeed, and indicate the result using an "out" parameter. The error seems the same regardless if the destination DB exists or not! Anyone have a solution?
View Replies !
&&"Create Role&&" Error On Transfer Database
I am trying to copy a database from SQL 2000 to 2005. I keep receiving "CREATE ROLE ... already exists in the current database" error message. I already deleted the target database but keep receiving this error message. I don't know if it is pertinent but my first execution failed for a different reason. I forgot to increase the errors threshold and my database contains external stored procedures that will fail when copied. Thanks for any assistance that you may provide.
View Replies !
Add A Login To A Database Role (was &"Very Confusing&")
This stuff makes no sense what so ever, In the Books on Line of MSSQL2000 In "Adding a Member to a SQL Server Database Role" There is this "Note : When you add a Windows NT 4.0 or Windows 2000 login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically, even if that Windows NT 4.0 or Windows 2000 login cannot otherwise access the database." I mean how can anyone add a login to a database role without making the login a user of the database.:shocked: Also if it worked, a new fancy feature to add new logins??:eek: Plz help:S
View Replies !
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 !
Application Role And SQL Express (2005)
Hello, Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)? Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005 Thanks John
View Replies !
SQL Server 2005 / Securityadmin Role / SSMS
Experiencing a little weirdness with SQL Server Management Studio...Have created a login, that has fixed server role "securityadmin".The login can create/drop logins, add/remove them as users indatabases, etc.The login can change another login's password, using SQLfrom a query window (e.g. alter login bob with password='abc')However, the login *cannot* change another login's password,using SSMS GUI (e.g. "Security", "Logins", double-click "Bob",change password, say "OK"). The error text is:Change password failed for Login 'Bob'. (Microsoft.SqlServer.Smo)Additional information:An exception occurred while executing a Transact-SQL statement orbatch.(Microsoft.SqlServer.ConnectionInfo)Cannot alter the login 'Bob', because it does not exist or you donot have permission.(Microsoft SQL Server, Error: 15151)Has anybody else experienced this?
View Replies !
Web Application And Role Management In Sql Server 2005
Hi every body I'm developing a web application and i like to use the sql server 2005 role management features istead of developing a role management package in my program, I can do it on my tables and othe database items but I have no idea about using database access rights in my web pages to permit some one viewing or updating a web form... Is there any system table or system stored procedure showing access rights in my data base? or is there another idea to do this?? by Thanks Javaneh
View Replies !
SQL Server 2005 Application Role's Problem
Hi all. I have developed a VB6 program which will activate an Application Role to UPDATE and INSERT some tables in SQL Server 2005. My program will login with a username, say USER and then run with the rights of the Application Role which will SELECT, UPDATE and INSERT the following tables: 1) Table A 2) Table B 3) Table C The USER login is a restricted user which has only SELECT permission to Table A, B and C. I encounter a problem in which my program can only UPDATE table B and table C but Table A. I have double checked the settings of Table A, Table B and Table C. Theirs are all same. Fyi, the column which can't be updated is of type "datetime". Once i grant USER login UPDATE permission to Table A, my program works perfectly in which it can UPDATE all the tables including Table A. I have tried for almost 2 days but am still clueless. Any ideas? Thank you so much.
View Replies !
Sending SQL 2005 Dbmail When Using Application Role
I am attempting to use dbmail from an application that logs in to my database using an application role. Since the application role does not exist outside the database, I created an spSendEmail in the database with "execute as login='mailagent'" in it: I set the database to trustworthy, created a 'mailagent' account and added it to the msdb database with the databasemailuser role rights. Email works just fine on the server when I use the execute as login='mailagent' to fire off the msdb..sp_send_dbmail. But from the .NET application, I get the error: "Cannot execute as the server principal because the principal 'mailagent' does not exist, this type of principal cannot be impersonated, or you do not have permission." When I run my spSendEmail stored procedure from the calling database, I get the same error.
View Replies !
Problem Adding Securityadmin Role To Sa In 2005
Hi Guys, I'm trying to add the securityadmin server role to the sa user but have been unable to due to an error occuring. The error says "Add member failed for server role 'securityadmin'. Cannot use the special principal 'sa' Microsoft SQL Server error 15405." I assumed that the sa account would already have the required permission to perform most operations on the server but I have been asked to try assiging this server role to sa by a support guy for our third party product. Has anyone got any ideas why this error is occuring? I have been looking on loads of forums but cannot find anything relevant to me. Thanks in advance for any help!
View Replies !
|
TRACKER
