Managing Job Roles?
Feb 7, 2001Does anyone know if it's possible to grant a user the ability to manage jobs in server agent besides giving them SA rights? I none of the server roles beside SA seem to be able to work.
Thanks
ADVERTISEMENT
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...
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?
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?
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.
Managing DATE ?
Jul 5, 2004Hi,
I have a page that shows some problem indexes (cards ? I don't know the word :/ ) and I want to show only those that are from the current day.
I'm using SQL server 2000. I have a date field inside my table (datetime type). So I tried to put another condition in my WHERE clause. This is:
WHERE something = something else AND mydate = DATEPART('dd', getdate())
or
WHERE something = something else AND mydate = DAY(getdate())
Both don't work..
I wonder if I can really use this in a WHERE clause...of if I'm using them correctly.
Managing SQL Data
Aug 16, 2004Hello,
I am currently working on a large project that will be developed using MS SQL. I have been considering two options when it comes the creation of the database. The first option is to create a sepereate table for each of my clients. The tables would be labeled by their Id number then the tables reqular name. Fore example : 781_Users.
The other option is to create a different database for each of the clients.
I just need the data to be seperated between clients, it cant mix. Please just give me your opinion on which option you would consider if you were operating a large project.
Thanks in advance!
Managing SQL Remotely
Sep 27, 2004Has anyone experienced any limitaions or concerns for managing SQL 2K remotely using remote desktop, pleased advise.
Thanks
Managing The Tempdb: Help
Jan 9, 2008Hello everyone. Have a little situation here. I am not normally the DBA at my work, but i've been poking around a little with a SQL Server 2005 implementation. I'm pretty new to this, so please pardon my lack of knowledge.
This particular server is running a lightly used SQL server, or so im told. As part of my Sys Admin duties, I logged into the box today for some routine checks, only to be notified that I was running out of disk space (which is another story).
After poking around awhile, I see that my tempdb is almost 15gb in size.
I don't have a lot of experience with SQL Server 2005. I've worked mostly with 2000. I did read a few things where tempdb has changed a bit in 2005? I wasn't sure.
So I have about 10gigs of space free on my HD (I'm adding more space tomorrow) and I need to figure out what to do.
I came across this thread here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64914
is that something I should do? I'm a little concerned I might run out of space overnight and not make it in time.
Anyone have some suggestions?
Thanks.
TCG
Managing Users
Jan 3, 2006Hello,Has anyone ever come across a reason why someone would manually create auser table incl. permission flags and not use the inbuilt user/rolesprovided by that database? The only reason that stands out for me is tomake the database that bit more portable?Thanks,Craig.
View 2 Replies View RelatedManaging MS SQL On Linux
Sep 12, 2006Hi everyone,I'm looking into moving my desktop system at work to Linux from WindowsXP, and one tool I've yet to find a suitable replacement for is MS SQLEnterprise Manager. I mainly need to query MS SQL databases fromLinux, and management tasks (monitor backups, services, etc) can bedone on the MS SQL servers themselves. THere was an application I usedto use on OSX which would connect to a variety of databases, includingMS SQL, and they had a Linux version of the client .. but can'tremember the name. But really any Sybase client that can connect to MSSQL with some ease will work.Thanks for any suggestions or ideas ...Alex
View 5 Replies View RelatedManaging Concurrency
May 15, 2007I want to centralize my previous standalone application. Previous application was using VB.NET and Access XP. Now I want to keep a centralized database (SQL Server 2005) and VB.NET 2005. At this point of time thousands of concurrent users will connect to the database at the same time.
In my application, when a ticket is being issued to a tourist, an SQL query finds the max(ticketno) for the current month from the main table and reserves this number for the current ticket. When the operator has finished entering information and clicks SAVE button, the record is saved to the main table with this ticket no. Previously there was no issue since the database was standalone.
I want to know how to block the new ticket no so that other concurrent users are not using the same number for saving a record. How to better tune the database for thousands of concurrent users at the same time? I also want that the other user must not get an error message when he attempts to save a record. It should be automatically handled by the database.
Managing Indexes
Sep 5, 2007Hello,I have an ASP.NET website which uses SQLServer 2000 as database. I am into tracking the customers who visit to the website. We are getting around 10000 users per day. When each page gives request, request details will be inserted into SQL tables.Table's I am using is indexed. I am in confusion that when these index will be regenerated ? Will it regenerated automatically when new row's are inserted ? or do we need to regenerate it manually ? And what is the difference on Clustured Indexing and Full text indexing ? Which one I should use for better performance ?Thanking youNavaneeth
View 6 Replies View RelatedError Managing
Jul 4, 2006Hi everyone,
When an error occured in a transaction and we can also create a message for this error ourself too by using @@error in order to print a message . However, I wonder that is it possible to really catch and handle the error ??
For example, when error occured it makes the transaction terminated. So how can we prevent transaction from terminating ??
Meanwhile, I also examine RAISERROR statement but it also does not prevent a transaction from terminating instead Ĺźt only provide an additional message for the error.
Managing SQL Server Remotely
Jun 26, 2007I've combed through SQL Help to find the answer to my question but I think it's telling me it can't be done. I work both from an office with my servers and from home. When I'm at home I would like to access my SQL server remotely using a tool such as MS SQL Server Management Studio. But it appears there is no way to access my SQL Server for management purposes using Management Studio over a remote internet connection. I can access the server using Management Studio while I'm on the internal office network but not from home.
Has anyone been able to do this or might recommend a third party tool as robust as Management Studio?
Thanks
SQLDatasource - Managing Programatically
Nov 25, 2005HiI'm trying to access the properties of my datasource programatically so I can change the stored procedure it connects to depending on the value of a querystringIn my page load I have
Dim myMode = Request.Params("mode")Select Case myMode Case "Category" sqldatasource1.SelectCommand = "productsbycategory" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure ******'Need to add querystring parameter Case "Wishlist" sqldatasource1.SelectCommand = "productsbywishlist" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure ******'Need to add Session variable parameter Case Else sqldatasource1.SelectCommand = "allProducts" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureEnd SelectThe problem that I have is that I need to pass a parameter to the first 2 procedures above, a querystring to the first and a session variable to the second.How do I add parameters programaticallyMany thanks
About Managing SQL Server 2005
Jan 2, 2006Hi, I just start using MSSQL server 2005, I already install vss studio
2005 so SQL server 2005 is already installed by default (express
edition).
But unlike SQL server 2000, I coultn't find any tools similiar to "sql
entreprise manager". I was able to create a new database only from
server explorer tools in VSS. But pretty strange that this database act
like a microsoft access database (creating a database file on my
app_data folder). (I am sorry I am new in SQL server 2005
ExpressEdition ).
My question :
1. is that (above) really how mssql server 2005 works?
2. how to create a new user for this database? (example user: sa password: "admin")
thanks
Managing Transactions And Locks In SQL 7
Apr 25, 2001Does anyone have any pointers or suggestions in explaining this topic?
The topics are what's including: Managing Transactions, SQL Server Locking and Managing Locks.
Web Interface For Managing SQL Server
Jun 11, 2001Hi all
Does any one know abt the web interface for managing SQL SERVER
I would like to create database,create tables create users, modify, insert data etc.. over the web
i belive there are interfaces are available on the net
can any body tell me from where i can download it
Thanks in advance
Prasad
Managing Excel Execution Through DTS
Oct 14, 2004We have a convoluted DTS package. The package is stored and scheduled to run on database server1. Within the package we:
• Make a connection to database server2
• Execute a ‘Process Task’ to execute EXCEL.EXE that is installed on server3
• Pass parameters through the ‘Process Task’ direct EXCEL.EXE to open a .xls file on server4
• When the Excel file opens, an auto_exec macro in the Excel file being opened imports a text file local to server4 AND directs Excel to save it with another name on server4.
The questions are:
1.Excel is not installed on server1 so how do we direct Excel to execute on server3 rather than server1 where the DTS package is being executed?
2.And how do we control the security context that executes Excel through this use of automation?
3.Other than potential CPU competition, are there any significant issues with having Excel installed on a dedicated database server?
SPROC For Managing Users
Feb 2, 2007I'm fairly new to SQL server, and I'm helping someone who has a fairly high employee turnover rate.
I would like to create an interface that would allow her to add/edit/delete users and their permissions without having to get into management studio. I'll build a form in the front end application that will presumably pass parameters to a SProc that will do the work.
Her users fall into two categories - full and read only. The tasks she'll need to perform are very simple.
1) Add new user with full access to all tables
2) Add new user with read only access to all tables
3) Change user access from read only to full
4) Change user access from full to read only
3) Delete user
Can someone point me in the right direction on this? Some sample code would be nice, but at the very least I'd like a better term to search by..
Searching for "stored procedure user permissions" brings up tons of hits that mostly deal with who can and can't run a sproc..
Managing SQL 2000 And 2005
Jul 10, 2007I currently use Enterprise Manager 8.0 with several SQL 2000 servers. I'm ready to work with my first SQL Server v 2005. Can I use EM 8.0 along side the new Management Console until I'm through the learning curve? I understand you cannot register a 2005 server with EM 8.0, but I assume you can register a 2000 server from the new Management Console without any problem.
View 1 Replies View RelatedManaging Package Configurations
Nov 5, 2007Hello,
I have a solution with a lot of packages, and each of them has configurations set on an SQL Server table. Right now the information in the table is outdated and I'd like to overwrite it with the new values on the properties of each package (and its elements). Is there another (faster) way to do this without having to disable/enable or recreate all configurations manually?
Thanks in advance
Managing SQL Server Agent
Apr 11, 2006Hi,
I would like to know what are the possibilities to
mange the Sql server Agent in the Sql Server 2005 express edition whit
the Sql server mangement studio express, because I can't see it .
thanks
Managing Large Database
Aug 11, 2007I am developing an office automation software for a government department. To start with, I have decided to first automate the salary section of the department. Because of some issues like, TCO, easy support and maintenance, scalability etc., I have decided to use SQL Server 2005 database with VB 2005 front-end.
I have planned the layout for this first part and found that in only employee salary table, 40,000 records per month will be stored. That comes out to be around 480,000 records annually. This is for one table alone, excluding data in other tables.
Since each section of the department will be integrated into this later, this application will become the backbone the department. The employee service, leave, salary, allowance, deduction and other records shall be maintained.
For this type of mission-critical application, I want to get some queries cleared:
(1) What backup strategy should be followed? I want to have schedule and maual backups both. Is there any way to have a mirror image on another server?
(2) What considerations to keep in mind in the inital stage of database design?
(3) How to keep the design scalable and configurable to meet future needs?
SQL2005, Managing Memory
Jan 24, 2008Hello~
I operate MsSql server with Maximum Server Memory.
The server has 2GB main memory and Maximum Server Memory is 1GB.
If sqlserv.exe use 1GB main memory, new query is not cached.
I think, sql server release pages in main memory to cache new query. but no release.
By above reason, performance of new query is not good.
How can sql server cache new query?
-- Memory Setting --
MIN : 0, MAX : 1GB
Because my English is poor, I am sorry.
Managing Transactional Replication
Jul 15, 2006Hi everybody,
I wish to manage the process of transactional replication between two SQL databases using a c# (or any other possible language, does not differ) written tool. More accurately, I have a database in my server, and I want to have the program to be run from any other computer and does the replication between the local pc(where the program is run) and my server. More to say is that the connection between the server and pc(s) is over the internet.
I would be so thankful if anyone would help me to solve the issue,
with regards
farshad
Managing PK/FK Relationships With The Tools
Aug 23, 2006Ah, why doesn't SQL Server Management Studio (SSMS) or Visual Studio's Server Explorer permit me to set PK/FK constraints on the tables? They're supported in SQL Everywhere (SQL Ev), but I don't see a way to set them up.
Why don't these tools support scripting the database tables to SQL? Is this planned?