New to SQL so please be patient. I'd like to give my developers "See All/Do Nothing" rights to my production server. I want them be able to see everyting that I do, as SA, but not be able to change anything. Is this possible?
Thanks for the help...Art
Hi, 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
How can I generate a list/view of all users/groups with appropriate roles and rigths?
View 3 Replies View RelatedHello everybody,
I have a database, where all tables and stored procedures look like this:
dbo.table
dbo.sp
Is this naming because of the db owner creating the tables and stored procedures?
The db runs correctly in my asp.net aplication when i use a user that is db owner, but i want it to run with a 'normal' user that is in public role and has all rights except db_owner...
How can i solve my problem!
Using the user without db_owner rights brings in my application the error:
The EXECUTE permission was denied on the object 'myStoredProcedure', database 'myDatabase', schema 'dbo'.
Thanks for help!!!!
Steven****
--------------------------------------
Solution:
I have to give the User without DB_Owner Rights the schema permissions for dbo objects! This only works in sql server 2005!
The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\localhost
eports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:
Title-Reporting services configuration manager
Error-There was an error refreshing the UI. bla bla bla
A WMI error has occurred and no additional error information is availiable
Title-Reporting services configuration manager
Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla
A WMI error has occurred and no additional error information is availiable
then when he's in sql server 2005 surface area configuation
Title-Surface Area Configuration
Error-Access denied (system.management)
Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS???
Please help!!
Thanks for any time anyone has taken to review this thread!!
Hello,
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...
Can 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?
Apologies 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
I'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.
After 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?
Hi! 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.
I 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
Hi,
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
I 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.
I'm really new to MSDE so I just want to know...Is SQL Server 2000 a prerequisite for MSDE particularly for developers? If yes, then is it(SQL Server 2000) a prerequisite for clients?
The thing is, I can't find a tool where I can create databases after installing MSDE...the only way I can think of is also installing SQL Server 2000 and create the database from there...
Hi,
I've just released Nautilus v1.0.6. I would apreciatte your opinion and comments.
With Nautilus you can:
-search tables without tipying the exact name.
-browse records, filter and locate records
-"Pivot" records to see it's related records (folowing the Foreign keys).
-Generate scripts: create table, insert into, delete, update.
-Build dynamic diagrams.
and many more features.
http://sourceforge.net/projects/nautilus/
Thanks, Marcos.
OMNI GROUPJoin Bytes!T-SQL/ CONTRACT TO PERM/ ATLANTAI'm looking for 1-2 TSQL developers for a 4 month contract to hirerole in Atlanta, GA. Ideally the candidate would be local or regional and aUS Citizen with 2-4 years of Microsoft TSQL development experience.Salary in the $55,000--$60,000 or so. annual range. Contract rate would beApproximately $36 hr W2 or 39hr 1099. One is responsible for their ownexpenses.Once perm, the company offers full benefits.If interested and qualified, answer the six questions below. EmailanswersWITH a resume to: Join Bytes!1. TSQL experience-------------------yrs2. MS SQL experience---------------yrs3. OO Languages----------------------4. Citizenship Status------------------5. Availability--------------------------6. Contract Rate ---------------pr hr..
View 1 Replies View Relatedwhere can i find pay rates or salaries for ssis developers?
thanks in advance.
I will have a group of developers creating objects in a database. Currently they all
create them with the SA account. I would like to have them mapped to the dbo or some method
that when they create objects all objects are dbo.object name and not h1fed99.object name.
Is there a good methor or practice to handle this
I'm currently trying to obtain an aggregate function from a table to provide a cumulative monthly value. I would like to create a view to obtian this from the underlying table i.e month=1 ,month=1,2 , month=1,2,3 Etc.
month sales
1 10
2 0
3 20
month cummulative_sales
1 10
2 10
3 30
Anyone got any ideas ???
Always looking for people who are strong with microsoft technology for new oppurtunities in the Seattle area. If you are good, we can help you find your ideal next position.
don't be shy. shoot me an e-mail.
thanks
Pat Copeland
Hey,
I am looking for mid to senior level SQL developers for some sweet companies here in Seattle. A chance to work in a dynamic envirnoment with bleeding edge Microsoft technologies. Are you good? then we can help.
shoot me an e-mail.
patc@snowdogs.com
thanks
pat copeland
snowdogs
we are for the people.
I am looking for strong SQL developers and/or DBA's for some really sweet companies here in Seattle. Anyone interested that is good and wants to hear about new oppurtunities? I would love to help you out.
don't be shy just shoot me an e-mail.
This may be an intuitively obvious question, and if so, I apologize.
Speaking in terms of best practices here, what is the preferred method for allowing developers to work with databases in SQL Server? If they use Management Studio, then they have access to tools for perhaps messing with other databases, etc. that they should not (assuming it is not correctly secured). Likewise with Enterprise Manager. I can see where they would use Query Analyzer, but these two are now combined in Management Studio.
I'm just inquiring into how other DBA's do this. I realize there are DBA's that are developers and vice-versa, but what if there is a clear line of distinction and you want to keep the tools used in these roles separate?
Thanks in advance.
Kevin
Are you new to SQL Server 2005? http://aspalliance.com/1455 These are the frequently asked questions in SQL Server 2005. Let me know your comments.
Uday Denduluri
Software Engineer
Refer my articles at http://aspalliance.com/author.aspx?uId=62740
Hi,I have just started a business and I was wondering if I could get someadvice about product selection. I am currently buiding my websiteusing asp.net connecting to sql server database.My question: Am I only required to purchase a SQL Server DevelopersLicence? I won't be hosting the site so I think this is correct butthought I should check.Cheers,Jack
View 1 Replies View RelatedI just bought a copy of SQL Server 2000 (with service pack 3a) that Iintended to install and run on XP Pro. My concern is that the requirementslist on the box do not include XP or XP Pro. Will SQL Server 2000 developeredition run on XP Pro?
View 3 Replies View RelatedHi Everyone,
Were looking from some feedback, thoughts, comments, suggestions on a permissions issue in our SQL Server 2005 environment. We have a Development (stand-alone server) and Production (activepassive cluster). Both running SP2. We're looking for the best way to manage permissions for the developers without giving them dbo privileges.
As background information, here's a note / response from a developer outlining the permission they need.
-----------------------------------------
"Our team needs enough permissions on DB database to:
Create and modify DB object such as tables, stored procedures, keys, triggers, views, ...
Be able to execute stored procedures and other basic DB objects
One of the issue I had when working on DatabaseA which was never resolved was that I could create tables, but had no rights to modify them once created.
I could modify them via scripts, but not via graphical interface which is a pain.
I think we should have full permissions to dev DB other than to drop DB, and modify system DB settings.
So that we can work within the created DB shell completely."
-----------------------------------------
Previous to supporting the current SQL Server 2005 environment, the developersapp owners had db_owner for their databases. Definitely not a best practice as with 2005 db_owner is ablke to delete databases. Moving away from this, we thought we would scale them back and only grant them the following database roles:
db_datareader
db_datawriter
db_ddladmin
With these roles, they were able to create the tables, views, stored procedures, etc...... but problems arose when they tried to modify the objects they created as well as execute stored procedures. It's going to be a pain to grant permissions on each stored procedure. More of a mystery is allowing them to make design changes within Studio Manager. Given they can already do this using scripts but not the GUI.
So there is an issue with using GUI tools to design the tables.
Here's a thread we found that tries to explain the reason why they can't use the GUI to make design changes.
Even if you have CREATE TABLE permission, there are limitations to the modifications you can make. Remember, as you modify an existing table or design a new one, your work can induce attendant modifications in other tables. For example, if you change the data type of a foreign-key column, the corresponding column in the primary-key table will be automatically modified by the Visual Database Tools. If you do not own the primary-key table, and you are not logged in as the system administrator, database owner, or a user that is a member of the db_owner role, your modification will fail.
When we created the accounts, we assigned the default schema to be 'dbo'. This was thought to simplify administration of the objects. In SQL Server 2000, this was a best practice. Now, in SQL Server 2005, they changed everything with the user schema separation.
So we have a developer (devuser) - created with default schema (dbo). Any object that are being created are owned by dbo. (ie... dbo.table1, dbo.storedproc1, etc..) So dbo owns the objects, not the user.
Does anyone have any ideas? Do we create a new schema and assign permissions? Do we create a new fixed database role for the developers? I'm curious as to how others are assigning permissions to developers on development and production servers.
Thanks,
Rob
What is the best practice in managing an SSIS development with multiple developers.
For example, we have 2 guys that will be developing various SSIS packages for a common data warehouse. We are most likely going to divide up the unit of work by the tables that need to get loaded. So, one guy will develop packages to load tables 1 thru 5 and the other guy will develop packages to load tables 6 thru 10.
Should each person work within his own project? (Not sure what a difference between a solution and a project is, so I may be thinking of a solution.) If so, can multiple people open the same project? I don't think you can.
I'm very new to SSIS and this would be the first time I'm doing something like this. I haven't even deployed a package to the server yet so I'm not even sure what implications/consideration I have before I take on this development effort I'm about to.
Does SQL Server 2005 Developers Edition come with Visual Studio Pro 2008? I know it did with Visual Studio Pro 2005.
If not, if I purchase SQL Server 2005 Developers Edition, can I install on the same machine as VS Pro 2008?
Will there be a Developer's edition to SQL Server 2008?
I realize I can't put databases I build into production with just the developer edition.
thanks
james
Is it possible to use SQL express to create a web application that is developed by more than one user? It seems to be registered on the <machinename>/SQLEXPRESS but the ASP.NET tool in visual studio seems to always want to create a sql express version in its app_data folder.
How can i set up a database such that 2 or more people can use that for developing the application. No doubt that SQL express with be suitable eventually, but initially we need to access the same database for development, but be on multiple machines. Can this be done, if so how?
Hi,
I have problems with the concept of user-defined messages.
I'm migrating from sybase ase to sql2005 and found out that there is only one global pool of messages.
Do you give every project on this server a dedicated range of error-numbers?
Another problem: only the sysadmin oder serveradmin can call sp_addmessage.
In my environment the developers own their databases and used to be able to create their own messages.
Now I try to let the developers call sp_addmessage by calling a "wrapper-procedure" which gets the needed rights.
create procedure sp__addmessage(@msgnum int, @severity smallint, @msgtext nvarchar(510), @lang sysname )WITH execute as OWNERASselect 'I am ' + suser_name(), user_name()exec sp_addmessage @msgnum, @severity, @msgtext, @lang gogrant execute sp__admessage to testusergo
When executing it as normal user (without sysadmin/serveradmin-roles) the output is:
I am sa dboMsg 15247, Level 16, State 1, Procedure sp_addmessage, Line 18User does not have permission to perform this action.
Isn't it strange?
Do you have a different idea how users/developers can create messages?
Thx
I like to get a feedback from other DBA's out there, I have a user with a consultant that need's Enterprise Manager installed on the user's desktop
in order for them to develop code for the user.
Is the above a good reason to give out to the user the Enterprise Manager
tools?
Please give me what is acceptable tools for the user/consultan.
Thank you
Jorge