Sa And Sso Role Separation
May 11, 2004Hi all
Do we have provision of separating sa and sso role in SQL server as we have in sybase? ( In such a case, sa shouldn't have any control on creating/modifying users/logins)
Thx
Wilson
ADVERTISEMENT
Separation Of Log And Data Files
Sep 15, 2004I'm kind of new to sql server (but experienced in Oracle) and I've got a couple of questions I wanted to bounce off you guys.
I'm implementing a SQL server cluster right now (2 node on Win2K3, shared EMC DASD for databases). We're at the very preliminary phase of this. I did an install and had my resource group set up with all of my disks on it. When prompted for the data file drive, I gave it one, but it put all the tlogs for the 'out of the box' database on that same drive as the data files (i.e. master, model, tempdb, etc.). The doc is a little vague in some of these areas (i.e. it says separate logs and data files on different disks, but then never actually tells you how to do that).
Now, I know how to specify the default paths for data and transaction logs for any NEW database I create and that's not a problem. However, my question is, how do I 'move' the tlogs from the databases created during the install? I've tried a detach, move tlog to separate physical drive and then reattach the db, but whenever I do this, SQL server wants to create a new tlog for the db on the same old drive as the datafile. I also can't delete the original tlog from a particular database even after I've created an additional tlog on another disk.
Any help is much appreciated. I'm more or less looking for the strategy any of you might take to set up this initial phase.
Upper Case Separation
Mar 26, 2008I am new to SQL and, unfortunately, actually do my work in Access 2003, but I have a question.
This is an example of what the data I am working with looks like:
AALADIN
AA-TACH EH65X, EH65V (V-Twin)
AA-TACH w/Robin 20 & 20.5 h.p. OHV
AA-TACH w/Wisc.-Robin EY21
ABI CONTRACTOR w/Honda 20 h.p. (V-Twin)
The all caps text strings at the beginning of the field need to end up in a separate field than the mixed strings, and the mixed strings need to stay together. The field length varies, as do the lengths of the all caps text strings. There are a lot of records, so I would be interested to know if there was a way to proceed without manually editing each line.
The
Separation Of Data And Code - Is It Possible?
Jan 23, 2008We have an environment/application where we have separate databases for different clients. Each database is pretty much a copy of the other - i.e. same tables, same stored procs, and a few reference tables whose data content is the same across all the dbs - except for that client's data in the user tables.
The issue we have is one of maintenance and growth. We wanted separated DBs so the amount of data in one would not result in performance issues for another client among other things (security and geographical separation for example). However, now that we are adding more clients the deployment team has to apply changes (schema chagnes, stored proc modifications) to each and every database. Does SQL (2005 SP2 in our case) support a concept of shared resources like schema, stored procs, user functions, and even a few tables but separate out the data for other tables? That way the schema, reference data changes have to done in one place...
Thanks.
Schema Separation Of Data
Jul 18, 2007I just want to verify my understanding of schemas...
Using separate schemas to segregate data is only a logical mechanism. Separate schemas do not physically segregate data onto different groups of pages or files. Partitioning is the mechanism for physical segregation of data.
Is that everyones understanding as well?
MSSQL 2005, New Hardware - HD Separation
May 13, 2008Afternoon.
I have a client who is in the market for a dedicated SQL box. It's a smaller company of 50 users. The primary task of this machine is a backend for Sharepoint 2007. At first I'm stuck using MSSQL 2000 for a few months until the budget can be approved to upgrade them to 2005.
I intend to spec this machine out for 2005, the question I have at the moment pertains to the HD configurations.
How would you recommend rigging that up?
Mirror for system, Mirror for the data store, mirror for transaction/temp files?
Put everything on a Raid-5 and forget about it?
I also have a question about Quad Core processors. How does MS sql's licensing work on that?
SqlDataSource? What Happened To The Separation Of UI And Business Logic?
Mar 16, 2007I guess I just don't get the reasoning behind the new SqlDataSource control. Haven't we just spent the last decade or so evangelizing and learning how and why to separate business logic from the display in VB 6 and VS.NET? In this age of programming for disparate devices (desktop, mobile, PocketPC, etc.) when this separation makes more sense than ever, why is MS pushing us to go back to putting our logic and data access rules and objects back in the display? It doesn't make sense. Why would anyone do this? And why would all the experts and MVPs at ASP.NET, DevX, 4GuysFromRolla, etc., go along with this?
View 4 Replies View RelatedSql 2005 Database Role Vs Application Role ?
May 18, 2007Ive 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
How Do I Return All Rows Value In A Single Column Delimit With Comma Separation?
Oct 23, 2007Dear all,
I have a table like:
State -- Customer
TN -- AAA
TN -- AAA1
TN -- AAA2
Delhi -- BBB
Delhi -- BBB1
Delhi -- BBB2
Mumbai -- CCC
Mumbai -- CCC1
Mumbai -- CCC2
Maharashtra -- DDD
Maharashtra -- DDD1
Maharashtra -- DDD2
I want to show the output in a single query like:
State -- Customers
TN -- AAA, AAA1, AAA2
Delhi -- BBB, BBB1, BBB2
Mumbai -- CCC, CCC1, CCC2
Maharastra -- DDD, DDD1, DDD2
How do i do this in a single query
Can Pivot query will help in this situation. Please explain with a sample query
Thanks
gopalan@sofist.com
okugops@hotmail.com
Integration Services :: Schema Separation - Accessing Multiple Databases
Nov 8, 2015We have a system that uses 3 databases, one for Membership db standard MS membership only the application has access to that data, one with User Data which we would like to make multi-tenant using Schema-Separation, and a third read-only reference db which is Common Market data for all users.we anticipate Tenant numbers in the thousands.Current we have multiple queries which create joins between the Main db and the Reference database using something like
Selec S.*, M.ScheduleDate, M.substation from Sites S left outer join Market.dbo.MarketUnit M where S.MarketUnitID = M.MarketUnitID
i'm planning to have a new schema for each Tenant on the Main Database, so I would create a Schema T1 for the first customer, a user T1User with access to T1 schema. and grant T1User access to Market.dbo. My First question is are there any concerns about the above T1User setup? My second question is, are there any tools which would automate the setup of the multi-tenant with schema separation, or should I just script the whole Main Database schema creation and replace schema name globally and then execute the script?
My Third question, how about upgrade and updates... currently using VS to compare dev/qa/prod database to identify changed which need to be promoted, and pushing updates... this could be a big pain to promote code to thousands of Schemas. grantedwe will likely keep the overall number of schemas spread over different SQL servers.
Can't Make Database Role A Member Of Another Database Role In 2005.
Jan 9, 2006In 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 RelatedApplication Role, App Role
Jan 23, 2008 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
Adding A Database Role To A Database Role
Feb 29, 2008Hi,
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
Which Role Do I Have ?
Jul 14, 2000Hi , I'm looking for a way that SQL-S7 tells me, what db-role I have.
I want to avoid that the server generates an error message when I'm not
authorized to access an information. So I could give the users only that
information they are authorized for.
May be there is a stored procedure or a template ?
Or is there an other way for controlled checking whether I have the rights
or not ?
Thanks for help
Role Being Ignored!
Apr 14, 2004When I assign a role to a user, the user is not actually getting the permissions defined in the role. I am forced to give the permissions directly to the user..any idea why it's ignoring the role which is assigned?
Thanks.
New Role
May 2, 2008I have set up all new Roles assigned different views, table access to each user i set up.
Whilst setting this up - i could do script to generate the script.
Is there a way to get this script after the effect - some roles i forgot to generate script.
I know im being lazy - i could write the entire script out but thought maybe there a way to get this .
Thanks
DB_DDLAdmin Role In SQL
Oct 28, 2007Hello:
I have read that giving a User the DB_DDLAdmin role in SQL might causes problems with ownership chains in the future. Since the User will have ownership to all objects created, what preventive measures can one take to help avoid any problems which might loom in the distant future due to ownership chains?
Thank you,
-H
Public Role
May 13, 2002I am new at this and we encountered a problem. Can names in the public role be deleted? We have some names that need to go - however the delete option does not high light?
I would appreciate your help..
To Delete A Role...
Aug 30, 2001Hi Everybody,
Experts........
i am using Sql server7.0
I want to Delete existing Role How Can I Delete it.
Anybody can please Tell me Immediately...
I tryied Sp_DropRole But it display's message successfully
completed but role of that name is still there...
Thanx a Lot in Advance.....(after solving this problem)
(Mohanlal)
DBA Role And His Rights
Oct 30, 2001Hi everybody,
The below I posted on SQL 2000 Forum about a week ago.
Any new thoughts................
I would like to get an input from as many people as possible on the following:
In our organization DBA is responsible for 5 servers ( currently NT 4/SQL 7)and is a part of group of a 5 people including manager and 3 developers.
DBA currently has a FULL access to every server.
In a few months we will be replacing the existing system with Windows 2000/SQL 2000.
LAN group will give to DBA only a read rights for the Windows 2000 environment, saying that the AUDITORS, both internal and external, require that. In other words, if DBA needs to run a command prompt, move files from one directory to another in Production environment, he has to fill the request to LAN, so LAN group would do that.
So I guess the main question(s) is:
What is the degree of involmment of DBA with Operating system?
Is DBA suppose to be an NT administrator ( I dont think so, since DBA has a lot of other thing to do?
If DBA accidently makes an unwanted changes to the Operating System, who should be blamed for ( not personally, but in more general terms) and would it be an extra argument to take write rights away from a DBA?
What auditors saying about that?
Thanks a lot in advance,
Andrei
What Role Should Be Given To The End User?
Oct 25, 2000Hi 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.
Server Role
Jan 10, 2003Hi!
What server role should be assign for individual to create and execute DTS packages.
Thank you,
Elena.
Db_owner Role
Mar 2, 2007Hi All,
Question:
Why would the user other than sa need the db_owner rights?
DBA Role And His Rights
Oct 24, 2001Hi everybody,
I would like to get an input from as many people as possible on the following:
In our organization DBA is responsible for 5 servers ( currently NT 4/SQL 7)and is a part of group of a 5 people including manager and 3 developers.
DBA currently has a FULL access to every server.
In a few months we will be replacing the existing system with Windows 2000/SQL 2000.
LAN group will give to DBA only a read rights for the Windows 2000 environment, saying that the AUDITORS, both internal and external, require that. In other words, if DBA needs to run a command prompt, move files from one directory to another in Production environment, he has to fill the request to LAN, so LAN group would do that.
So I guess the main question(s) is:
What is the degree of involmment of DBA with Operating system?
Is DBA suppose to be an NT administrator ( I dont think so, since DBA has a lot of other thing to do?
If DBA accidently makes an unwanted changes to the Operating System, who should be blamed for ( not personally, but in more general terms) and would it be an extra argument to take write rights away from a DBA?
What auditors saying about that?
Thanks a lot in advance,
Andrei
Application Role
Aug 18, 2004I am confused . What is considered an application and how SQL would know ?
If I have a web site accessing SQL VIA IIS will SQL Server treat it as an application ? How about MS Excel ?
Also , If I was to use the application of Power builder , using app role , how do control which user can use the app ?
Thanks
Processadmin Role
Oct 5, 2005I'm trying to allow my developers the ability to modify/execute their jobs and dts packages in production....without giving away the security farm so to speak.
Is the processadmin role a possibility?
BOL and the net only seems to say this role allows user to "manage process"...duh.
Your thoughts and advice would be great appreciated.
Sysadmin Role
Jan 30, 2006Hi All,
Is it possible to give a user a sysadmin role and then deny some of the privileges?
I am a junior dba, I should be able to view only everything that the sysadmin can see, i.e. db properties, logins, packages, jobs etc.
SysAdmin Role
Feb 10, 2004Hi
I'm new to SQL Server. I have created a databased named Sample and
I hae created the user with login named "Sman".
SMan owns some tables and sp's. I'm able to access the tables and SP's when I was logged in as Sman in Query analyser. I have given a Sysadmin privilege to Sman then I'm not able to access the tables and sp's when i try to login with Sman.
ie, Select * From tabl1 is not working But
Select * From Sman.tabl1 is working. I dont know Why is it so?
Can any one help me!
Thanks in Advance
Role Permissions
Apr 11, 2008Hi Guys,
How to copy one user role permissions to another role in same databse using stored procedue(sp)? Can any one help how to write sp.
Thanks.
Help With ROLE Creation
Jun 16, 2008I am a promotional DBA and need help with some code a developer wrote that errors out.
He is trying to create a role and to my knowledge there is nothing wrong with the syntax but it gives an incorrect syntax error.
CREATE ROLE 'PPS' IDENTIFIED BY 'stressboy';
error: Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'ROLE'.
we tried it with ' ' & "" and without either. I'm sure this is an easy one for you all. Thanks.
Server Role
Mar 24, 2008what views table that I can use to view the server roles on the instance?
thanks,
Role Nesting
Dec 27, 2005Hi,
I am developing the security in a sql database, and i am doing so in a hierarchical way. In the white paper Row and cell security it says that we must add the child role as a menber of the parent role, but when you are in the role section you can not add roles as menbers of another role, so what i did was give the parent role ownership over the child role, it seemed logical.
So i build a admin
|
boss
|
worker
|
subworker
Nested Role
Now after giving users to those roles i was good to go and try my hierarchy security, i used the view that is in the white paper cell and row security which the code is the following:
SELECT ID, Label
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))
But when i runned this view dont matter which users in which role ist always giving me an output of every single line in the table, the problem seem that he is giving me out the IS_MEMBER(MarkingRoleName) = 1 always as true like the user was in every single role.
What am i doing wrong?
Thanks.
Setting Up A Role
Sep 25, 2007
Database Name
ADVENTURE WORKS
Tables humanresources.employee
I like to set up a New User TRACEY
Who can read this table and see gender
When i create the Role (VIEW_EMPLOYEES )
I would only like TRACEY to be the user
The owner is confusion me here (is this dbo or humanrsources)