Moving User & Roles
Apr 2, 2001I saw an article I believe on this site, now I can't find on HOW TO MOVE USERS AND ROLES FROM ONE SERVER TO ANOTHER. DOES ANYONE KNOWS WHAT IS THE BEST WAY TO DO that?
View 3 Replies
ADVERTISEMENT
Add User/Set Roles In Code And Read Roles
Jan 28, 2004Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?
I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.
After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?
Moving Database Roles
Nov 12, 2001On 11/10/01 I posted a question on how to move passwords from one sql2000 box to another. The response to this question worked perfectly.
I also need to copy over the user or database access information from one box to another. I tried to use dts but it only copied over the users and the database roles but did not copy over the Database user properties which allows the user access and places the user into security groups.
Any help would be appreciated.
Thanks again.
Steve
SQL Server 2012 :: Query To Find User Who Last Modified User Roles / Access?
Dec 6, 2013I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
View 3 Replies View RelatedUser Roles
Oct 20, 2005Hi all,
I've created a training database, and added a user to it. Now I am trying to figure out what database roles I need to give him. Can I get away with public only, or he will need the db_owner role? Thanks.
User Roles
Apr 3, 2008I have a sp that my users need access to run and currently their roles are set up as users with db_datareader, db_datawriter. I'm having trouble getting them access just adding permissions to the tables being used here. What is the best method to allow them access to only the neccessary roles to run this sp?
ALTER PROCEDURE dbo.spi_CallList
(@strAgent nchar(4), @MEA int)
AS
DECLARE @Msg varchar(255)
declare @SQL varchar(4000)
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList1')
DROP VIEW qs_CallList1
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList2')
DROP VIEW qs_CallList2
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList3')
DROP VIEW qs_CallList3
SET @SQL = 'Create view qs_CallList1 as
SELECT top 100 percent tsd_Claim.clinsnum AS [Ins#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByIns
FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat))
LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID
WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''')
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null)
AND ((tsd_Claim.clfromdos)<GetDate()- ' + cast(@mea as varchar(12)) + '))
GROUP BY tsd_Claim.clinsnum
ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC'
EXECUTE(@sql)
SET @SQL = 'Create view qs_CallList2 as
SELECT TOP 100 percent tsd_Claim.clinsnum AS [Ins#],
tsd_Claim.clpid AS [Account#],
Sum(tsd_Claim.cloutstandingamt) AS OutstndByAcct
FROM (tsd_Claim
LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat))
LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID
WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''')
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null)
AND ((tsd_Claim.clfromdos)<GETDATE()-' + cast(@mea as varchar(12)) + '))
GROUP BY tsd_Claim.clinsnum, tsd_Claim.clpid
ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC'
EXECUTE(@SQL)
SET @SQL = 'UPDATE tsd_Patient SET tsd_Patient.PPriority = Null WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + '''))'
EXECUTE(@SQL)
set @SQL = 'truncate table priority'
EXECUTE(@SQL)
set @SQL = 'INSERT INTO Priority ( Agent, [Account#] )
SELECT tsd_Patient.PAGENT, tsd_Claim.clpid AS [Account#]
FROM ((tsd_Claim LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID)
INNER JOIN [qs_CallList1] ON tsd_Claim.clinsnum = [qs_CallList1].[Ins#])
INNER JOIN [qs_CallList2] ON (tsd_Claim.clpid = [qs_CallList2].[Account#])
AND (tsd_Claim.clinsnum = [qs_CallList2].[Ins#])
WHERE (((tsd_Claim.cloutstandingamt)>0.99))
GROUP BY tsd_Patient.PAGENT, tsd_Claim.clpid, [qs_CallList1].[Ins#],
[qs_CallList1].OutstndByIns, [qs_CallList2].OutstndByAcct
ORDER BY tsd_Patient.PAGENT, [qs_CallList1].OutstndByIns DESC , [qs_CallList2].OutstndByAcct DESC'
EXECUTE(@SQL)
set @SQL = 'Create view qs_CallList3 as
SELECT TOP 100 Percent tsd_Patient.PAGENT, tsd_Claim.clpid AS [Account#], tsd_Claim.clticketnum AS Claim,
tsd_Claim.clfromdos AS DOS, tsd_Claim.cloutstandingamt AS Outstnd, tsd_Claim.clins AS Insurance,
tsd_Claim.clinsph AS Phone, tsd_Patient.PGLNAME, tsd_Patient.PFNAME AS [First], tsd_Patient.PLNAME AS [Last],
tsd_Patient.PSSN, tsd_Patient.PDOB, [qs_CallList1].OutstndByIns, [qs_CallList2].OutstndByAcct,
[qs_SQLClaimStatusLastEntryCL].Pat
FROM (((tsd_Claim LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID)
INNER JOIN [qs_CallList1] ON tsd_Claim.clinsnum = [qs_CallList1].[Ins#])
INNER JOIN [qs_CallList2] ON (tsd_Claim.clpid = [qs_CallList2].[Account#]) AND (tsd_Claim.clinsnum = [qs_CallList2].[Ins#]))
LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat)
WHERE (((tsd_Claim.clfromdos)<GetDate()-' + cast(@mea as varchar(12)) + ')
AND ((tsd_Claim.cloutstandingamt)>0.99)
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null
Or ([qs_SQLClaimStatusLastEntryCL].Pat)=''0''))
ORDER BY tsd_Patient.PAGENT, [qs_CallList1].OutstndByIns DESC ,
[qs_CallList2].OutstndByAcct DESC ,
tsd_Claim.clinsnum DESC,
tsd_Claim.clpid,
tsd_Claim.cloutstandingamt DESC'
EXECUTE(@SQL)
set @SQL = 'update tsd_patient set tsd_patient.ppriority = priority.priority from priority where tsd_patient.pid= priority.[account#]'
EXECUTE(@SQL)
User && Roles Backup
Jun 27, 2007Hi,
Can you please tell how to backup the users and their access permission from a database before restoring it.
Balavenkatesh.
What Rights/roles To Use For A DB User?
Feb 20, 2006Hi, SQL experts.
I'm
new in the SQL Server Express Configuring Stuff and I've a problem or
more exactly I don't have any idea about configuring an secure and
hacking save user for a SQL Express DB.
What server roles/server
permissions/endpoint permissions/database permissions are neccessary to
have an rw access SQLE user? Any articles available? Any suggestions?
Scenario is a Personal Webpage for my Family & Friends located in my house
- ASP.Net Starter Website: Personal Website
- 2 MDF DB's: ASPNETDB.MDF & PERSONAL.MDF (included with the Starter Website)
- Development on DEV PC; Deploy on SERVER PC
HW:
- SERVER PC with Win2k (fully patched)
- DEV PC with Win XP Sp2 (fully patched)
- LAN 100Mbit w NAT FW
- WAN Cable Modem 128/1500
SW:
- APACHE 2.0.55 with latest aspnet_mod
- ASP.NET 2.0
- SQL Server Express
- SQL Manager 2005 Lite
- MS Web Developer Express
What I've done so far:
- APACHE: configured & working
- aspnet_mode: configured & working
- SQLExpress: configured & working
- SQLBrowser: configured & working
- TCP/IP Protocol for SQLE: configured & working
- Mixed authentication for SQLE: configured & working
- ASPNETDB.MDF: attached, configured & working
- PERSONAL.MDF: attached, configured & working
- SQL Manager 2005 Lite: configured & working
- SQLE users: created one for ASPNETDB and one for PERSONAL
(with SQL Manager Lite; Sorry MS!!! your SQL Server Mgmt Studio Express sucks!)
- WEBSITE: changed connection string from local to remote
(local doesn't work, because the LocalSystem User on Win2K, which is
used by the SQLServer, has no User Profile. Also you cannot change the
Service User, so it won't work with the local connection string, but
with an attached and remote accessible DB it works just fine)
- WEBSITE: runs locally and remotely on DEV PC with remote DB's and custom users.
But
I'm concern about the two users I've created, to access the two DB's.
I've no idea, what rights/roles aso they need to use the DB for remote
(for development) and local (for production) access and to be secure?
Thanks for any help, ideas and suggestions.
Alex
SQL 2012 :: Moving SSIS Solution To Another User - Reset User Encryption Key
Oct 22, 2014I have an SSIS package built by another developer, and now that I'm running it under my login the passwords won't save. The solution and packages are setup with ProtectionLevel EncryptSensitiveWithUserKey, but how do I get the User Key to reset so I can now save passwords? I can re-enter them, but whenever I enter hte password and test it then click OK it still has the red arrow next to the connection as if there's an error. I can create new connections and those passwords save fine, but with 40-50 items in this package I hate the thought of having to go into each and change the connection.
I tried changing the package and solution to DontSaveSensitive then rebuilding and closing then reopening, I hoped there was some option to reset the User Key just as if I created the solution. If this option doesn't exist why?
Reading Membership Of All Roles For A User
Feb 22, 2000I have several roles on a database and want to find out to which roles
a certain user belongs to. What's the SP to get this information?
Background:
I have a table where I store some application specific permissions for
the different roles. I use the same grant/deny/revoke logic like in
SQL server. These are permissions that cannot be mapped to a SP or a
table.
In a query I want to get all the permission records for the role that
a user belongs to.
Thomas Schoch
Login, User And Roles Backup
Nov 16, 2000I am wondering is there anyway I can backup only Login name, Users and Roles, Not database, stored procedure, Views and Trigger. If some one can suggest there is a way that will be really great
Thanks in advance.
Is There A Quick Way To Get A List Of Roles A User Is A Member Of?
Jul 23, 2005What I'm looking for is a list of roles a particular user is a memberof.the closest I've found so far is sp_helprolemember without anyarguements. but this gives me all the roles and all the users. I wantthis same list filtered on a specific user.something like sp_??? 'user'
View 2 Replies View RelatedUser Define Roles And Cross Database Queries
May 28, 2003We maintain a few applications that query multiple databases on our server. We also have groups of users that multitask using different applications.
I've always created a Role in each database for every application.
Wondering if someone is in two Roles at once will we have security conflicts?
Create User Roles In Reporting Server 2005
Jan 30, 2007Hi,
I would like to create the folders Sales, Orders and Credit under the Home folder.
The Sales folder contains a set of reports that should be accessible to a group of users, similarly Orders folder contains a set of reports that should be accessible to a group of users.
I do not have facility to create user groups in SQL Reporting Service. I create a user group in my machine (from Control Panel) but Reporting service is not able to view this group.
I am able to add individual users to a folder, but I would like to check if I can create a group and then add users to this group.
Any help in how to resolve this issue.
Cheers,
IS_MEMBER Not Working With User Defined Database Roles
Mar 1, 2007I am not sure why this is not working it clearly states in the MSDN that it should "
'role'
Is the name of the SQL Server role being checked. role is sysname and can include the database fixed roles or user-defined roles but not server roles.
"
I have seen many questions revolving around this issue on this site and on the net about this but know one can answer it
I have created a new user defined database role called testrole with any owner
then created a new sql login and user (Sql Authentication)
add the user to the database role testrole
check IS_MEMBER and it returns 0
try this with a fixed database role and I get the desired result of 1
this is simple and should not be such a problem for every one
unless I am doing something wrong
Please help
Thanks
Setting User Roles Without Using Reporting Manager - Using Setpolicies In Visual Studio VB
Oct 25, 2007I have created an web reference called ReportingSerivce 2005. i am trying to set an user to have browser rights without going into Report Manager.
But I can't get the SetPolicies function to work correctly. Any ideas?
Code written in VB.
Dim rs As ReportingService2005
rs.Url = "https://wa.hrconnect.treas.gov/reportserver/reportservice2005.asmx"
Dim Item As String = "/"
Dim Policies() As Policy
Policies(0) = New Policy
Policies(0).GroupUserName = TxtUser.ToString
Policies(0).Roles = New Role(0) {}
Policies(0).Roles(0) = New Role
Policies(0).Roles(0).Name = "Browser"
Policies(0).Roles(0).Description = "May view folders and reports."
rs.SetPolicies(Item, Policies)
How To View Permissions Of User-defined Database Roles In Management Studio?
Dec 20, 2005As 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!
Schemas, Users, Logins, Database Roles, Application Roles
Mar 5, 2006Hello,
I am new user of SQL Server. I have some problems with these words. I want to make my database works in my specified permissions. I will specify permissions with schemas and these schema wants an owner. I want this owner should be my user. When creating a user it needs a valid login. I am selecting my login and it occurs and error says this login has an different user. I am specifying permissions with roles. But i can't make association all of them. I hope i told my problem to you as well. If you explain these words to me and tell me how can i do my database's works with my own schemas, users and roles i'll be grateful. Thanks for advices.
Happy coding...
SQL Server Roles && ASP.NET 2.0 Forms Authentication Roles
Jun 16, 2006Apologies if my post does not fit into this forum. I initially tried the SQL Server Data Access forum but I now think my question is more security related.
Is it possible for a web user who has been successfully authenticated with forms authentication to be authorised to use a SQL Server 2000 role depending on a particular ASP.NET 2.0 role that they have been authorised to use? I understand that that I can assign a SQL Server 2000 role to the ASPNET or NETWORK SERVICE account but this will grant access to anonymous web users to the database role. I can ensure that I only call stored procedures which access sensitive data in web pages that are in restricted by ASP.NET roles. However, it would be nice to also restrict stored procedures via the ASP.NET 2.0 Forms Authentication roles.
If this is not possible have you got any bright ideas how I could restrict access to stored procedures who are anonymous web users.
Many thanks,
Mark
SQL Server Roles, Windows Groups && ASP.NET Allow Roles
May 6, 2007I'm developing an ASP.NET2.0 application which accesses a SQL Server 2005 Express database. I plan to use integrated security for access to the database.
I'm confused about the relationships between Windows groups, the ASP.NET web.config file <allow roles=.../> and SQL Server roles.
I would like to create a Windows group to which I can assign multiple users and grant that group access to a Web Site using windows authentication and also grant that windows group access to the database my web application uses.
I have gotten the combination of Windows Authentication to the web site and to the database to work for a specific windows user but I am having trouble determining the combination of database security entities I must create to allow access to my database by members of the windows group.
For a Windows user:
1. Create Windows user
In SQL Express
2. CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE =
3. CREATE USER FOR LOGIN
4. CREATE ROLE
5. EXEC sp_addrolemember <role-name> <user-name>
For a Windows group, what would be the equivalent commands necessary to grant a windows group access to my database? Specifying the Windows Group name in sp_addrolemember does not appear to be sufficient even though the documentation states that a windows group name is a valid value for the member name argument.
Fixed Database Roles Vs Application Roles
Aug 24, 2006After reading Books Online, I am still confused with Database Role vs Application role.
My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.
If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?
Copying/Moving User Accounts...how?
Sep 27, 2006Ok. So we just moved over one DB from our original server to another server. The DB copied over fine.
Our next step is trying to see if there is a way to move accounts. Is that possible?
We have about 40 accounts (using SQL authentication) on the original server. Is there a way to move or copy those accounts to the new SQL server that is holding that same DB?
Haven't found anything in the Enterprise manager, so im thinking it will be something to do in the SQL analyzer.
My lack of knowledge in SQL is not helping me much here.
I appreciate the help.
Moving Some User Databases To New Server
Apr 10, 2008Hi!
I have a SQL 2000 server with a lot of databases (about 100) and I would like to move a great deal of them (+/- 50) to a new SQL 2005 server. All the databases I would move has the same word in the database name. Let's say the all include "Customer" in the Database name.
It would be a huge job to detach and attach every db manually. Is there a method that can detach db's based on a query againts the master db with a select on the name of the db?
If so, I also need to attach the db's on the new server with a script after moving the files to a new location.
Any good ideas?
Login Failed For User After Moving App To Web Server
Jan 17, 2006My app works fine in development server. After moving to web server, error occured as bellow. Can someone help me? I use vs 2005 and SQL server is windows authentication mode.
Login failed for user 'domain_nameREPORT$'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'domain_nameREPORT$'.Source Error:
Line 348:
Line 349: If MyTable = Nothing Then
Line 350: MySQLDataAdapter.Fill(MyDataSet)
Line 351: Else
Line 352: MySQLDataAdapter.Fill(MyDataSet, MyTable)
Oracle Predefined Roles Vs Ms Sql Roles
May 3, 2005Hi! Can anyone say which ms sql server predefined roles are similar to the following oracle predefined roles: dba, connect, resource. I already know that sysadmin in MS SQL Server is the same as DBA in Oracle but what about the rest?
Thanks a lot.
How To Decide On Server Roles And DB Roles
Mar 2, 2006I am in the process of locking down the SQL Server in an environment that is considered to be in production (pilot stages) and there is no staging or test environment that mirrors it. I need assistance in determining the server and database roles to assign to existing logins, most of which currently have sa and dbowner rights. Because it is not a development environment, I need to be sure that downgrading the server and/or database level permissions will not break any functionality.
I'm starting with the logins that have the SA fixed server role. These logins need to be able to install applications that require the use of a backend database, which will be stored on SQL Server. In addition, through the installation process a new login/password for the newly created database(s) is normally created. For the existing logins with the SA fixed server role, will downgrading to the securityadmin and dbcreator roles be sufficient to facilitate those needs, or are those too much/ too little? And should any user account ever be granted the SA role? If so, what questions could I ask to determine this need?
Since these install process for these applications usually prompt to install using SA or local system account to authenticate to SQL to create the new database(s), that account should have securityadmin and dbcreator roles to create the database and its tables, as well as add a new login to that database.
Please address this question, keeping in mind that the logins will only be performing the described actions, installing apps using SQL Server as the backend database and adding a login to that database (which may or may not be done during the installation process).
Thank you,
nu_dba
Db Roles / Server Roles
Apr 15, 2007Hi,
I'm looking for some guidance/help regarding setting up a sa - lite account in SQL 2005. I need to give another admin rights to create/monitor maintenance plans, backup and restore databases, monitor performance/logins, but NOT be able to have any rights on several tables (and of course not being able to set user permissions).
I've tried using server and db roles but haven't been able to determine how to give someone w/o full sa rights access to maintenance plans.
If you can think of soemthing, please let m eknow.
Jenn
Assigning Roles To Roles
Mar 10, 2004I have MS SQL Server 2000 DB.
I have created a User and created some tables for the same.
I created a Role named A and granted Select Permissions for few tables to that roles.
When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.
Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.
Transact SQL :: How To Get Moving Total Like Moving Average
Nov 10, 2015I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
View 3 Replies View RelatedFailed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed
Dec 7, 2006This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
Roles
Sep 21, 2001I need to grant select/viewing on a information_schema for a programmer. how do I grant this without granting server role "System Administrators".
Thanks,
Jason
Roles
Sep 21, 2001I need to grant select/viewing on a information_schema for a programmer. how do I grant this without granting server role "System Administrators".
Thanks,
Jason