Non Database Owner Call To Sp_addrolemember Or Sp_droprolemember
Jul 20, 2005
Hi,
Is there any way that calls to sp_addrolemember and sp_droprolemember
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
View 3 Replies
ADVERTISEMENT
Oct 1, 2007
I have a trigger which requires dropping a member from a role and includes user transactions. However you cannot call sp_droprolemember from within a user transaction. Looking at the code for the procedure gives me a line
quote:
exec %%Owner(Name = @membername).SetRoleMember(RoleID = @roluid, IsMember = 0)
I can find no documentation on SetRoleMember or %%owner and attempts to run this line as an exec statement fails with "syntax error near '%'"
Nor can I find any way of dropping a member from a role using T-SQL or DDL constructs.
How do I drop a member from a role using T-SQL code and avoiding sp_droprolemember? And where do I find information about the %%Owner construct?
View 5 Replies
View Related
Jan 17, 2001
Dear All colleaques
Can any one tell me why I can not see Loc_ecomdev_des as a server user or a db user when viewing through EM, after executing this script.
EXEC sp_grantdbaccess 'ecomdevloc_ecomdev_des, 'loc_ecomdev_des'
GO
EXEC sp_addrolemember 'db_securityadmin', 'loc_ECOMDEV_des'
I can only see loc_ecomdev_des as a member of the db_securityadmin when I view properties of db_securityadmin role in that database.
Also what can I run in QA to display db_securityadmin members.
Saad
View 1 Replies
View Related
Dec 27, 2006
how to try-catch sp_addrolemember?
View 4 Replies
View Related
Mar 22, 2007
When I login localy (computer with sql server) I can call procedure sp_addrolemember but when I am loged in remotely (from client computer)and try to call the same procedure I get this message: 'User does not have permission to performe this action'.
View 1 Replies
View Related
Mar 27, 2006
I'm trying to handle some user management inside a trigger. When I call sp_addrolemember I get this error.
sp_addrolemember cannot be used inside a user-defined transaction
Is there any way to get around this error? I need to assign a custom role based on a variable inside this trigger.
Any help is appreciated.
View 5 Replies
View Related
Jul 9, 2007
I use sp_addrolemember inside a DML trigger and every thing goes well in my computer; I can insert or update into my table and sp_addrolemember is executed successfully, but my customer get this error every time he tries to insert or update into the table.
"the procedure 'sp_addrolemember' cannot be executed within a transaction"
Can anybody help me solve this funny error.
View 1 Replies
View Related
Sep 18, 2007
Hi-I have a sql server database, and am wring web apps to access it.I've created databases different ways, and ended up with different owners (eg dbo, nt authorityetwork services...)I also have connection strings using windows authentication, and some using a user name and password.I have read that using windows authentication is the best way to go, as far as security goes, but I have noticed some connectivity issues when I upload the site to the server, and test it remotely. What is the safest 'owner' of the database, and what's the safest way to connect?Thanks Dan
View 8 Replies
View Related
Aug 6, 2002
I want 'sa' to own all the user databases. Currently the database properties screen shows the owner as myself, 'al', but the list from the Users tab in EM for the database doesn't list me, but lists the dbo as login name 'sa'. DB_changedbowner 'sa' says:
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.
BUT, who really owns the database; if it's 'sa' then why am I showing up on the properties page as the owner???
Thanks,
AL
View 2 Replies
View Related
Dec 3, 2001
Any idea what is this error that I am getting when trying to change the db owner of the database as sa.Currently owner is different than sa.
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.
View 3 Replies
View Related
Jun 27, 2007
Hello,I'm using tableadapters in VWD 2005 Express to link our ASP.NET application to a SQL Server 2000 database. Initially, I used Database Explorer to drop tables into the DataSet object, which picks up the owner of the tables from the database.Recently, we had to change the owner of all objects in the database, including tables and stored procedures. When we run our application it chokes on stored procedures because the owner of the SP's has changed. My question is: how can I update tables and tableadapters in the DataSet to reflect the change in owner, without having to recreate everything in DataSet?
View 1 Replies
View Related
Jul 29, 2007
Hi,I just transferred my website and database (SQL 2000) to a new host who's SQL Manager doesn't support the previous username I had for the database.The previous owner of database tables etc was Database_master and now I want to change it to just Master.Please advise! Someone else designed the website for me, and honestly I have just some basic knowledge of databases and such.I would appreciate help and if possible with detailed steps.Many thanks in advance,Isje
View 2 Replies
View Related
Nov 11, 2007
I've got a GridView on my webform bound to a SQLDataSource (called sqlZKEWILL). Works fine as long as I hard-code the "dbo" database owner in the Select statement, as in:
select EXIDV, Carton_Count, Carton_Total, dbo.ZINSPECTION where EXIDV = @CartonID
The problem is that I need the database owner name to be dynamic. This will be a parameter that I read from another SQL table. For example, I'm doing this:
Select USERID, DatabaseOwner from tblUsers where UserID = 'Kimm'
I need to take the DatabaseOwner from this second Select statement, and use it as part of that first select statement.
On the CodeBehind page, I tried replacing "dbo" with the Database Owner field (which I saved in a session variable) with the following code:
Private Sub gvLineItems_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvLineItems.DataBinding
sqlZKEWILL.SelectCommand = sqlZKEWILL.SelectCommand.Replace("dbo", Session("DatabaseOwner").ToString.Trim)
End Sub
But I am still getting the error message: "Invalid object name: dbo.ZINSPECTION". It doesn't seem to recognize that I've replaced "dbo" with a different value.
Any thoughts on getting this to work would be appreciated.
View 2 Replies
View Related
May 17, 2008
Hello,
I'm trying to access the Database Diagram of a sql database I created but am getting the error:
"The database does not have a valid dbo user"
I also noticed that my NT group user was incorrect and this is the name being used as the owner of the database. How do i change this?
Thanks
View 3 Replies
View Related
Jan 10, 2001
Hi All,
I want to change the OWNER of the database to 'sa' and I executed the
following system stored procedure
EXEC sp_changedbowner 'sa'
and I get the following error message..
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.
Any ideas
Thanks
Reddy
View 1 Replies
View Related
Oct 18, 2000
Is there a stored procedure to change the database owner in SQL 7?
Thanks in advance,
Faustina
View 4 Replies
View Related
May 26, 2004
Dear Ladies & Gentlemen,
I need help here. I have problem on my Database own by 'unknown". Can I change the onwner of that Database.
Detail of SQL:
Micosoft SQL Server 2000
If anybody can help me, please reply this thread.
Regards
View 1 Replies
View Related
Dec 28, 2013
What is the purpose of a database owner i.e. the dbo.DBNAME that comes before the name of the database?
View 7 Replies
View Related
Nov 29, 2005
Hello,Does anyone know what procedure to run to show information such as theowner of a specific database for MSSQL2000? I want to make a script toloop through all the databases on a server and display owner and otherhelpful information.Best regards,Andreas
View 1 Replies
View Related
May 15, 2007
Our web application requires that when a new Organization is created,aseparate Database is to be created for the corresponding organization,so that each organization remains a separate entity with theircorresponding employees(This is our clients requirement, so that theycan later provide the backup of the database if the organizationdecides to move out). And for that what we intend to do is restore thebackup of database which already has the objects created in it.So weintend to restore a database for the new organization from the webpage.We are using SQL Server 2005 Enterprise Edition and theauthentication is SQL Server authentication, and the 'xy' login hasbeen assigned to the 'dbcreator' fixed server role only(This may benot relevant but just in case, the 'xy' login has been assigned'db_owner' fixed db rolein the main db. The web application uses this login to connect to thedatabase). When a new Organization is created, the database owner ofthe new DB should be xy, and the user mapping for the login to the'NewOrg' database should be 'dbo'. But in this case after restoringthe databasetemplate using the SQLSMO from the application(same thing happensafter restoring the database from query analyzer using the xy login)there is no user mapping to be seen and we are not able to connect tothe 'NewOrg' database using the xy login.I think most will suggest to use 'exec sp_changedbowner' to updatethe dbo, but for this we need sysadmin privileges, which we do nothave for the xy login.Please suggest the best way to go about this particularscenario(currently we are running the object creation script aftercreating the database but this is time consuming, so pls suggest onlydatabase restore method) and the also please suggest if any otherfixed server roles can be used without compromising security. Seemsthis is a design issue, pls also suggest if this post is to be made insome other appropriate groups.Thanks,MPL
View 6 Replies
View Related
May 12, 2006
I get this error when I try to view the database diagram. This is strange because it was about two weeks ago that I created and edited the database diagram. What is wrong with my login now as compared to when I did those edits?
The error suggests to use the ALTER AUTHORIZATION to change to a valid login. I am using windows authentication. If my login (the database ownere) is not the valid login then who is?
And what are the database diagram support objects that it suggests I install. I never heard of them, and where are they? Why didn't I need to install anything special when I worked on the diagrams two weeks ago?
Error message:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
------------------------------
BUTTONS:
OK
------------------------------
Does anyone have any suggestions?
dbuchanan
View 3 Replies
View Related
Mar 19, 2007
Hello,
I restored and renamed a client database from a backup file to my laptop (both SQL Express 2005) and noticed that no owner had been assigned. I set the db-owner to SA. This database will replace a local version of the same database that was created on my laptop. (The customer has entered data...)
When I start the dataset configuration wizard in VS2005 all tables appear twice in the list. Once with the original name and once with the prefix dbo. For example: Customer and dbo.Customer.
The tables with the dbo. prefix are marked with a Red Cross in the checkbox. Hovering over them display the error message: "Element .. in the dataset references an object missing from the database. "
The tables without the dbo. prefix are not marked. When I mark these tables VS2005 indicates that the table will be renamed to Customer1. The same issue applies to all the views in the database.
Is there a way to solve this problem without having to recreate the entire dataset? I'd rather not do that cause I added some queries to the dataset and they will be lost and have to be recreated.
Any help will be appreciated.
View 2 Replies
View Related
Jul 16, 2006
hi,
on 1 of my servers (actually, the dev. server I have setup up here at home), I must included the database owner everytime I select something or make a db call.
for example
select T.foo from K inner join T on T.id = K.id
must actualy be written like:
select dbo.T.foo from dbo.K inner join dbo.T on dbo.T.id = dbo.K.id
or else it wont work
what did I do wrong for this database owner thing to be a "must" when writing queries on this server.
spec:
sql 2000 sp4
View 6 Replies
View Related
Jul 29, 2007
I am getting this error while selecting the properties of one database.
Property Owner is not available for Database '[TEST]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
Suddently this error started coming ? why ?
View 10 Replies
View Related
May 27, 2007
Hi,
I use Sql server express 2005.I have two databases: db1 and db2.I use Studio Management. I connect with my administrator account 'admin'.
1) When i rightclick on db1 (and also on db2) and i take the properties / general, i see for both db: owner = servernameadmin .When i take properties / Files, i see for db1: owner = servernameadmin , but for db2, it remains empty.why and does it mean that db2 has no owner? But then, why do i see in properties / general : owner = servernameadmin?
2) i want to make 'admin' owner of db2 (for creating a database diagram), so in properties / Files, i click on the button with three dots.A windows "select database owner" appears, i click on button 'browse', but then all i see is:built-in administartorsbuilt-in userssa...
Whatever i choose (e.g; built-in administrators), i get the message: an entity of database cannot be owned by a role, group ... error 15353).
Why do i not get the account 'admin' in the browse windows and how to make 'admin' owner of db2?
Thanks for helpT.
View 3 Replies
View Related
Jun 6, 2008
Hi,
it has to do with my unfamiliarity with creating users and dbo privileges. I'm trying to grant access to an outside vendor to import their latest version of a database. I set them up with a user account that had db_owner privileges, however when they ran the dts process it copied all the data as new tables rather than overwriting the existing tables and the new tables were all associated with the user is created instead of dbo user. What do I need to do to facilitate this process?
View 1 Replies
View Related
Feb 23, 2001
I am in the process of cleaning up the security on one of our production databases. SA is already the owner of the database.
On this SQL server, there is a login that has access to this database that needs to be removed. When I uncheck this users access to this database I receive the following error message:
"Error 15183. The user owns objects in the database and cannot be dropped".
How can i find out what objects this logins has ownership of and how can I change the ownership of these objects to SA?
Thanks in advance,
Philip Talavera
View 1 Replies
View Related
Jan 14, 2005
Is there a way to add an existing user as an "owner" of a database programmatically using SQL or a SP?
Thanks.
View 4 Replies
View Related
Apr 13, 2007
I am using SQL Server 2005 and I was having a problem with the owner of a particular database. When I go into Database Diagrams for the database I get:
"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."
So I tried some different things (assigning different users as the owner) I was only able to assign one user as the owner (someone that is no longer working here), then I deleted that user (oops.). Now I still don't have an owner assigned AND I can't get into the database properties because I don't have permissions.
Any help would be appreciated.
View 4 Replies
View Related
Nov 29, 2007
Hi,
We have a situation where somehow several of the system databases are owned by an ID of a person no longer with the organization. This triggers a few questions: If I disable the user (it's a SQL account, not domain), will that cause any issue? What is the correct way to change the user of a system database?
View 2 Replies
View Related
Aug 3, 2006
I am trying to change owner of all tables in a database. How can I doit?
View 1 Replies
View Related
Jul 20, 2005
Dear Group,I have a table in the a database that I need to change the owner to dbo.How can I do this.Thanks in advance.Jeff Magouirk
View 1 Replies
View Related
Jul 31, 2015
I can't change the database owner..I noticed this when I went to create a database diagram and the error said the database does not have a valid owner.
So for that database I went to Properties > Files and sure enough, the owner is wrong. Why? I had changed the name of the PC recently, actually a couple of times. When I finally got the name right, I ran the following commands in the query window:
SELECT @@SERVERNAME to get the current server name
and then
sp_dropserver <old name>
[code]....
to change the server name.The owner in the Properties > Files is for a pc name that doesn't exist. But when I click on the '...' button to the right to select a new Database Owner I can't select the primary account on the PC.If I create a new database, it puts that old name in as the database owner.
I went down to <server>SecurityLogins and the old <pcname><username> is still listed, and I can't add the <newpcname><username> because it says that principle already exists . I'm afraid to make any other change without knowing for certain what to do.how to get a new database owner name on all the databases on this server.Do I delete that old Login and then try to add in the new name?
View 8 Replies
View Related
