Adding Users To A Role In Bulk
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
ADVERTISEMENT
Jul 28, 2015
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
View 5 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
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
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
Nov 23, 2005
I want to add bulkadmin permission to my applicatio role. Is it aposible.My windows account havo only public permission on database.I'm using application roleEXEC sp_approlepassword 'MyRole', 'password';Therefore I want to BULK some data with BULK INSERT command.Error is:The current user is not the database or object owner of table'tablename'. Cannot perform SET operation.Thanks in advance.
View 1 Replies
View Related
Jul 20, 2005
Hello,I am trying to load a simple tab-delimited data file to SQL Server. Icreated a format file to go with it, since the data file differs fromthe destination table in number of columns.When I execute the query, I get an error saying that only sysadmin orbulkadmin roles are allowed to use the BULK INSERT statement. So, Iproceeded with the Enterprise Manager to grant myself those roles.However, I could not find sysadmin or bulkadmin roles using theEnterprise Manager. From what I read from my books, I thought thesewere fixed server roles and that they would be there.So I have a few questions:1) How do I create a user account/role that can issue BULK INSERTcommands?2) Why is BULK INSERT considered a dangerous operation that itrequires special privileges? What are its implications? I have acouple of books that say that a user should be aware of itsimplications before using it, but they don't actually describe whatthose implications might be.3) It seems that I can load the data file using BCP utility, withoutsuch privileges. If so, what is the difference?Thanks!
View 2 Replies
View Related
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
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
Nov 8, 2000
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 2 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
Aug 9, 2007
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 3 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
Feb 29, 2008
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 4 Replies
View Related
May 25, 2001
Have small network of NT workstations, A PDC and BDC. We have one SQL database on the PDC and two others on the BDC. I added a new machine to the network, added users to that machine. When I try to add the new user, (Not Machine) to the database on the BDC, using security, add new user, I get the following message: Microsoft SQL-DMO ( ODBC SQL Statement 42000)
Error 15401: Windows NT user or group "domainnameusername" not found Check the name again.
The user 01 is listed on both the PDC and the BDC. I am totally baffled by this. Any help would be appreciated.
Stephen
View 1 Replies
View Related
Sep 26, 2007
Created a report in SSRS 2005 on a test windows server 2003.
I have admin rights on this server.
I can access it by http://localhost/reports
and then click on the report itself to view the data.
Now I would like my colleague to access this report on this server.
He does NOT have admin rights.
I would like him to view the report from his browser by typing:
http://servername/reports or something simiilar.
How/where do I add him to have access to the report?
Thanks
View 1 Replies
View Related
Feb 14, 2008
Writing to tables created by regular users on MSSQL2005
I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much
View 3 Replies
View Related
Oct 7, 2004
More of a SQL Server problem than a ASP one but hope you can help me :)
Im tryin to do my first data based application using ASP.net and silly me I forgot to add users when I created then database so now Visual Studio can't access it.
Can someone tell me the syntax to add/create a user to MSSQL database so I can then grant them privelidges to the tables.
thanks very much
Tom
View 1 Replies
View Related
Dec 3, 2003
Hi everyone out there in ASP.NET land.
Have a quick question...
How do I add a role and/or a user through code (Specifically SQL Statements). If not through SQL, then maybe VB.NET?
Thanks ahead of time,
Denvas
View 2 Replies
View Related
Mar 5, 2000
Hi there NT Server,sqlserver 7.0, Integrated Security. I used "sp_attach_db" to attach a database to the sql server. The database has 3 standard roles with members in each of those roles. But i am not able to add a user to any of the roles through Enterprise Manager(No users are listed, except the other 2 roles). Also when i click "users" under the Database in EM, no users are listed except "dbo" with sa login. Luckily since some members are there in the roles, they are able to connect to the database. Also i am not able to add the user bcos the user exists in the public role and i get an error message "user already exists in DB". The user has logged into the NT and everything is fine. I copied the .mdf and .ldf files from a "Standard Security" Server.
View 1 Replies
View Related
Apr 20, 2007
Is there a certain way of adding users to access a linked server? Ilinked to an Oracle server but somehow, I'm unable to add any users,even after trying in EM. It'll allow me to select the local account,enter a remote userid/password, click apply save, but doesn't seemto hold the info.Please help!Thanks,Danny
View 1 Replies
View Related
Nov 9, 2007
Hi,
I€™m looking for a way to add users to view reports in SSRS. We have set up SSRS to use forms authentication and have been running with no problems for a few months now. When we add new users to the aspnetdb we also have to go into management studio and give them permission to view reports. (Currently all users have the same permission which is set in the root folder policy in SSRS)
We have now set up a .NET application which allows our client to manage their users online, however they don€™t have the knowledge or access to Management Studio in order to permit new users to access reports.
Is there a way, through SP or .NET script to programmatically add users to the policy / security descriptor in SSRS. I have already been playing with the XML Description in the SecDesc table of the ReportingServices database. This seems to contain all the necessary data, but modifying doesn€™t work. In the same table is a Binary SecDesc field which I suspect has complementary data to go with it. Is there a way of generating this data?
Any help with this, or another method will be greatly appreciated.
Sean.
View 9 Replies
View Related
Oct 28, 2015
I have to give three users read access to a database.  My manager told me to map them to a User called "zxDatabase.Read.ug". I scripted the User and got this...
CREATE
USER[zxDatabase.Read.ug]
FORLOGIN[zxDatabase.Read.ug]
I don't see a Login at the server level named zxDatabase.Read.ug, so this is confusing. Where is this login in SSMS?
How do I map my three users to user zxDatabase.Read.ug. Or am I not understanding correctly? Do I just add there windows login to the database as Users and configure them the same as zxDatabase.Read.ug
View 4 Replies
View Related
Jul 21, 2006
I do not understand the sequence in which to add logins/users. A coupleof things to note. I can not use vbscript - it has to be done using thesqldmo objects (or in a sql script).The sequence I use now is:1- create login, set default db2- add login to sql server3- set db owner4- create user5- add user to master db6- add user to tempdb - blows up hereError 'Microsoft SQL-DMO: [SQL-DMO]This object is already in acollection.'I need the user in 3 dbs. Any help greatly appreciated. Thinking aboutit...I bet by adding it to the master db that it is automatically addedto tempdb so I will try to add it to the 2rd db and see if that works.Thanks!-Will
View 2 Replies
View Related
Nov 9, 2015
I am in the process of rolling out a pair of SQL 2014 servers. I have setup an Availability Group, Listener and databases. It's my understanding that I will be giving the listener name to our developers so that they can do their work. In testing, I noticed that If I am using Studio Manager and connected to the the AG using the listener name, when I setup a user in security the user is only added to the active primary node. Is there a way to add a user to both servers in one shot instead of having to install on both servers?Â
View 5 Replies
View Related
Mar 26, 2008
I am sure that I am missing something which is why I am in need of the communities assistance...
I have installed SQL Server 2005 Express Edition. I can log in with the Windows Authenication and the SQL Server Authentication using the 'sa' account. This is all good...
What I want to be able to do is add 1 to 3 more users so they can gain access to the database I have created to run queries and add data. This is the part I am confused about. How do I get additional users added??
Thanks in advance for your time and assistance!
View 3 Replies
View Related
May 22, 2007
Running SQL Server 2005 x64 Enterprise on Windows Server 2003 X64 Enterprise and I am getting the error below when I add users to the permissions on the SRS HOME folder with browser rights.
Response is not well-formed XML. (System.Web.Services)
Unexpected end of file has occurred. The following elements are not closed: Message, MoreInformation, detail, soap:Fault, soap:Body, soap:Envelope. Line 11 position 30230. (System.Xml)
I need to add more users to reporting services and have never experienced this before.
View 3 Replies
View Related
May 20, 2008
Developers need to view and modify jobs on some servers without being sysadmin. We've added the developers to the following MSDB roles:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
Technically they should be able to view and modify jobs through Management Studio when being added only to SQLAgentOperatorRole. However they receive the following error when clicking on the Jobs folder regardless of the msdb role.
Execute Permission Denied on Object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Our original two SQL 2005 servers do not produce this message, but all other instances do. All servers, but one, are SP2 so the service pack is not an issue. Any thoughts?
Thanks, Dave
View 3 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
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