Hi,
Is there any way that calls to sp_addrolemembe r and sp_droprolememb er
can be enabled for non database owners and non sysadmin members?
This would be very helpful for an application I'm in the middle of
developing, in which users have the right to view some data and edit
some data in a set of tables. The data is pulled up in a set of views
(using SQL Server 7 with an Access 2000 front-end). Depending on an
initial selection that the user makes, s/he should be able to either
read or edit the data.
The solution I hoped to use would run a stored procedure, that amongst
other things would add and/or remove the user to/from a data_read and
data_edit role, depending on the initial selection s/he made.
Any suggestions?
Much thanks!
Oren Bergman 3 4909
[posted and mailed, please reply in news]
Oren (or**@gdblegal. com) writes: Is there any way that calls to sp_addrolemembe r and sp_droprolememb er can be enabled for non database owners and non sysadmin members?
Books Online says about permissions for sp_addrolemembe r:
Only members of the sysadmin fixed server role and the db_owner fixed
database role can execute sp_addrolemembe r to add a member to fixed
database roles. Role owners can execute sp_addrolemembe r to add a
member to any SQL Server role they own. Members of the db_securityadmi n
fixed database role can add users to any user-defined role.
So, if the users are members of the roles that owns the role they
want to add/drop members from, they should be able to do it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Ernland -
Thanks for the response.
Another solution that was suggested to me, without using SQL's roles
is as follows:
- Create 2 views for the data - a read only and a r/w.
- Create a table to track the various users' permissions.
- Pull up the view corresponding to the users choices and permissions
(in the permissions table).
This way the users don't have to have extra permissions (can a role
have more than one owner in any case? If not, this would complicate
the solution you suggested). The permissions table could have certain
defaults making it easier to add new users to it.
All the best,
Oren
Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>... [posted and mailed, please reply in news]
Oren (or**@gdblegal. com) writes: Is there any way that calls to sp_addrolemembe r and sp_droprolememb er can be enabled for non database owners and non sysadmin members?
Books Online says about permissions for sp_addrolemembe r:
Only members of the sysadmin fixed server role and the db_owner fixed database role can execute sp_addrolemembe r to add a member to fixed database roles. Role owners can execute sp_addrolemembe r to add a member to any SQL Server role they own. Members of the db_securityadmi n fixed database role can add users to any user-defined role.
So, if the users are members of the roles that owns the role they want to add/drop members from, they should be able to do it.
Oren (or**@gdblegal. com) writes: Thanks for the response. Another solution that was suggested to me, without using SQL's roles is as follows:
- Create 2 views for the data - a read only and a r/w. - Create a table to track the various users' permissions. - Pull up the view corresponding to the users choices and permissions (in the permissions table).
This way the users don't have to have extra permissions (can a role have more than one owner in any case? If not, this would complicate the solution you suggested). The permissions table could have certain defaults making it easier to add new users to it.
A role can only have one owner, but that owner may be a role, so it would
be possible to use that solution.
However, the view solution you present appears to be more palatable. It
confines the solution to user tables/views, and requires no special
configuration. If I understood your requirements correctly, this seems
to be the best solution.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ben M. |
last post by:
Greetings all,
This should be an easy task, and Im sure it is, but as many times as I have
tried, I cant seem to get this to work properly.
We changed ISPs recently from a shared host to a co-located server, and our
former host was nice enough to send us a backup of our old SQL2000 database
(about 5MB).
I went into Enterprise Manager, created an empty database with the same name
|
by: Phil |
last post by:
Hi all,
I need some help to access an SQL db on another machine. I am using VB.NET
and remoting to make a client/server connection...although I don't think
this is relevant to the question.
I have been asked to help with a small db project that will reside on our
office server and have approx. 5 users.
I have installed a copy of MSDE on my development machine and plan on doing
|
by: Daniel Bass |
last post by:
I setup a asp.net project running on http://localhost/ which connects to a
database on another server running sqlserver...
I was able to connect to the database and create my application no problem.
The sqlserver database server had windows 2000 without any SP's or updates.
This was no problem and even though the OS hadn't the .net framework
installed, everything worked fine.
the requirements for that sqlserver database server have...
|
by: Ober |
last post by:
I'm having trouble with my security model, Application_Start, and accessing
my database.
My ASP.NET app is only going to be running in an intranet environment (not
on the public Internet). The production environment will have installed
everything locally (i.e., IIS, SQL Server, .NET Framework, etc., all on a
Win2k Server).
|
by: jj .NET |
last post by:
I have several sites and membership databases on the same test
computer. They all have different names, so there is no problem.
However, now my Website Administration Tool won't create the ASPNETDB
database for me correctly. It creates the database, but doesn't add
the correct schema. When I attempt to use the aspnet_regsql tool, I
get the following error:
=================
Setup failed.
| |
by: fireball |
last post by:
how to try-catch sp_addrolemember?
|
by: levimc |
last post by:
I know that that topic may be old to you but I looked at other more-
than-two-year-old topics
related to mine. However, I didn't find them working for my project
at all because its errors return back to me everytime.
The error I have on that project said:
"An unhandled exception of type 'System.EntryPointNotFoundException'
occurred in TestSysaudit.exe
|
by: Limunski Magarac |
last post by:
Hi all :)
My apologies if I posted in the wrong groups, but I just jumped
in MS SQL waters, so any guidance will be appreciated.
What I'm trying to do is the following process:
present operator with a web page (script)
once filled with db name and username, script will create
..sql file
|
by: sqshymnky |
last post by:
Hello all,
I know html but am sort of lost when it comes to PHP. I don't really know where to even begin to explain my problem.
I am working on a site that deals with real estate. I have created a form on a basic html page( real estate listing page). This form calls a mail.php page to send the contents of the form to the person to whom it is supposed to go (property owner). I setup a test and it all works fine. But here is where the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |