Creating Databases Under MS SQL 2005 Server (SP-2): Dbo User Role
Good Day,
I am having a problem with creating databases in MS SQL 2005 Server. When I attempt to assign an User Mapping for my SQL user account to the newly created database, the "dbo" user and schema is already assigned. When I try to drop this user in favor of my own, I receive an error message: Cannot alter the user "dbo" (Microsoft SQL Server, Error: 15150). I am connected to my database engine through the "sa" account.
Regards,
Keith
View Complete Forum Thread with Replies
Related Forum Messages:
Selecting Only Databases In Which User Has A Role
I have a user in SQL Server 2000 with public, datareader and datawriter roles on several databases. I need to select all those databases, how can I do that. I have tried sp_databases but I get ALL databases. I also tried sp_MShasdbaccess but I still get all databases.
View Replies !
Creating Databases On WinXP SP2 With SQL Server 2005 CE RC
Hi, For various reasons a new desktop application I am developing is using the SQL Server 2005 Compact Edition Release Candidate to store local data. On my development machine (XP SP2, VS2005, SQL Server 2005 Developer Edition) the software is able to create the database file, and read and write to it fine. However on our test machine, which does not have any of the development tools the software is unable to create the database. A System.Data.SqlServerCe.SqlCeException is thrown. The message is: "SQL Mobile usage is restricted on this platform. To use SQL Mobile, you must install SQL Server 2005, Visual Studio 2005, or the Tablet PC SKU." Obviously this goes somewhat against the blurb for SQL Server CE. Is this something that will be fixed shortly? Or is it the intention that databases can't be created on machines that do not SQL Server or Visual Studio installed? (Incidentally the reason for creating the database on the fly is that we are encrypting it, using the users credentials, so that only they can open the database.) Thanks, Steve
View Replies !
Creating And Using SQL Server 2005 Compact Edition Databases
OK I think I am missing something here. I have installed the newly released SQL Compact Edition, Server Tools, and Tools for VS SP1. According to the documentation you can do the following to create a SQL Compact Edition DB: Creating a SQL Server Compact Edition database on the server In SQL Server Management Studio, open Object Explorer. In Object Explorer, click Connect, and then choose SQL Server Compact Edition. In the Connect to Server dialog box, select <New Database€¦> from the Database file drop-down list. In the Create New SQL Server Compact Edition Database dialog box, type a file path and file name for the new database file. You can optionally select the default sort order and choose whether you want to encrypt or password-protect the database. If you choose to encrypt or password-protect the database, type a password, and then click OK. Click Connect to connect to the new SQL Server Compact Edition database. The database is now displayed in Object Explorer. I see no reference to or any option to create or connect to a SQL Server Compact Edition database in SQL Mgmt Studio. I had SQL 2005 Mobile installed previously and this still shows as an option. What I am missing here? Is SQL Mobile now SQL Compact Editon? Thanks in advance, Jack
View Replies !
Creating Sql Server 2005 Express Databases Via VS2005 Std.
This post is an extension onto the one below.... Please read my question at the bottom... cheers --------------------------------------------------------------------------------------------------------------------------------------- Sept. 26th 2006 Hello all, I use Visual Studio 2005 Standard. I would like to know the best way that I can create Sql Server 2005 Express databases visually since VS2005 Std. does not allow me to do it. Any help is appreciated and thanks in advance, - Noble Bell I'd be interested in knowing how VS doesn't allow you to create a database. What error are you getting? There are two ways to create databases, depending on your goal: To just create a database on your server, do the following: Open the Server Explorer Right-click on Data Connections Click Create New SQL Server Database Specify Server Name and Database Name Your database will be created and you can start working with it. Embed a database in your project: On the Project menu, click Add New Item. Select SQL Database from the list and give it a name. Click OK This will run you thorugh a wizard to create the database. If your having problems doing either of these, you may not have SQL Server installed on your computer or VS might be pointing to the wrong Instance Name. Check out the Option dialog under Database Tools:ata Connection and verify that the correct instance is specified. - Mike Wachel - MSFT --------------------------------------------------------------------------------------------------------------------------------------- I am trying to create a similar project and I also recieve an error while trying to create a database... "An error has occured 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: Named Pipes Provider, error: 40 - Could Not Open A Connection to SQL Server." Now let me explain what i am trying to do ... I am trying to create a web interface for the data that I want to store in the SQL database. I have installed VS2005 & SQL Server Express 2005 on my local machine. I want to develope this project on my local machine and then transfer it to a server once i have finished the project. If i am doing this all wrong, please let me know !
View Replies !
A .NET Framework Error Occurred During Execution Of User-defined Routine Or Aggregate -While Creating A SQL SERVER 2005 Stored Prodecure In VS.NET 2005
Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SqlServerProject1.StoredProcedures.insertlogin(SqlString log, SqlString pas).No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[insertlogin]. ***************************************************************all i am trying to do is : creating a SP in VS using managed code and then trying to execute it. But every time i get the above error. If you can tell me how to edit connection string in this that would be very helpful. At present i am using : Using conn As New SqlConnection("context connection=true") I tried to do "" ALTER ASSEMBLY SqlServerProject1 WITH PERMISSION_SET=EXTERNAL_ACCESS""but i get this error "" Msg 10327, Level 14, State 1, Line 1ALTER ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.""" *********************************************************************Plz help
View Replies !
SQL Server 2005: Question About Creating A User For A Database
I'm not a DBA, so I don't know where to look for information on this. In SQL Server 2005, I can simply no longer create a user for the database I created. It asks that the user be associated with a login. That login is found in the Security folder of the database instance. The question is: what is this instance security folder for? I don't recall having to do this in SQL Server 2000. Any elaboration or links to such information would be appreciated.
View Replies !
How To Drop A User Defined Database Role In 2005?
Using Studio, I created a user defined database role but I can not delete it because "TITLE: Microsoft SQL Server Management Studio ------------------------------ Drop failed for DatabaseRole 'test1'. (Microsoft.SqlServer.Smo) ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138) I am quite annoyed because the "owned schema" is db_owner, which can not be unselected. Quite an innovation. How do I drop this relationship?
View Replies !
User With Server Role
I have a user with DBCREATOR Server role only. That user is able to create database but create table permission denied. how would I set permission on this user, so that this user can create databases and automatically becomes the DBOWNER of that database and can do any action on that database. Thanks,
View Replies !
Checking The User's Server Role
I would like to determine if a particular user has sysadmin serverrole. Is there a way to do this via the connection string? Currentlyour code checks if a login is valid using SQLDriverConnect, however weneed to be certain that the user can login and modify the schema.Is it possible to fetch a user's server role to determine if it has asysadmin server role?
View Replies !
Backup User - Server Role?
I wish to create a user that can backup any or all databases in our SQLServer 2000 Instance. I thought there would be a server role for thisfunction, however I can only find that after I grant access of adatabase to the user, then I can choose ds_backupoperator.I want to create a user that will have the ability to backup all thedatabases. I dont wish to have to come back to the server after a newtable is created and add the backup user to that table.I want SA w/o the full privilage...am I crazy?Any Suggestions?TIARobBackgroup: We currently have about 10 SQL servers, and adding more inthe future. I am using SQLBackup from Idera along with HP SurestoreTape library (60 slots,2- DLT8000 drives with 40/80 GB capacity) withArcServe from Computer Associates. I want to have this automated tobackup to file then tape, regardless of what databases get created.
View Replies !
How To Generate All Grants To A User Role? (SQL Server 2000)
Using SQL Server 2000 I need to extract all the grants made to a usercreated role so that I can recreate the role in another version of adatabase and there are more grants than I want to do by hand. Doesanyone have SQL or T-SQL to perform this task that they would bewilling to share?I tried searching the archives but either I missed the post or what Iwant isn't posted.Thank you.Mark D Powell
View Replies !
How To Generate All Grants To A User Role? (SQL Server 2000)
I had thought that I had posted this yesterday but since I cannot findit here we go again.I have searched the archives but either the code I want is not postedor I missed it.I have a user create role in a database that I need to reproduce inanother version of the database. Does anyone out there have the SQLor T-SQL necessary to generate all the grants to a role?-- Mark D Powell --
View Replies !
SQL Server 7 Error 15023, User Or Role Exists
SQL Server 7 i did a restore of a database, then tried to add the User login to it, but when i select database access, i get the followinf error :- "Error 15023, user or role already exists ! the user did exist on the Database, but when i select Database,Users or Database,Roles the User doesn't exist !! so i can't drop it ! any ideas ??
View Replies !
User Can See All Databases SQL 2005
I have a database user with rights on one database. If he connects with management studio 2005 he can see all databases. How to change that he can only see database where he has rights on viewing and/or changing? Henri ~~~~ There's no place like 127.0.0.1
View Replies !
Problems With The &"Walkthrough:Creating A Web Site With Membership And User Login&". SQL Server 2005
I'm trying to do the walkthrough from the MSDN library for greating member login in asp.net. I create the http-site, add a "memberpages" folder and go to the Website->ASP.NET Configuration menu option and click on the "security tab" upon which it tells me (after chewing for a while): "There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: Unable to connect to SQL Server database. " After looking around for a bit and not getting any wiser I decided it was time to come here. There are a few things I have noted that I thought might be useful for diagnosing the problem: If I put a GridView element on my start page and run it, it gives me a "System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITYNETWORK SEVICE'"-error.
View Replies !
Programmatically Adding A User To The System User Role
We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?
View Replies !
Adding A User/role To All User Database
Hi everyone, I try to add a db_role or a user to all my databases with one script. Although parsing doesn't report any problem I get a Syntax error during execution. I first select the database names into a #temp table which has two columns, ID and dbname. After that I use the following code: DECLARE @Count smallint declare @dbVarchar(20) SET @Count = 1 WHILE (@Count <=(SELECT MAX(ID) FROM #temp)) BEGIN Exec ("USE @db") EXEC sp_adduser 'test' EXEC sp_addrolemember 'my_role', 'testrole' SET @COUNT = @COUNT + 1 SET @db = (SELECT dbname FROM #temp WHERE ID = @Count) END It seems that the "Use @db" part has no value for the variable @db. Does anybody knows how to solve this?
View Replies !
URGENT: Problems With Guest User In SQL 2005 Restoring Databases From Sql 2000
Hi all. I don't know if this is the right forum to post this question, but here it goes. We have restored into sql 2005 the database backups made in sql 2000. We connect with trusted connection and application roles, and when trying to execute a transaction to another database (with the guest user), we get a permission error. Does anyone know if, apart from restoring the databases, we should do something else to get the guest user working the same way as with sql 2000 in the restored databases? Thanks in advance for your help.
View Replies !
Creating Databases Using The Sql Server Shiped With Visual Studio
Dear Reader, I'm having some problems writing T-sql statement in visual studio 2005 because i cant find either the query analyzer or enterprise manager in the version of sql server installed. Please kindly put me through that or do i need to create the database outside visual studio. Please will be greatful for this if u can help.
View Replies !
CREATING ROLE-PLAYING DIMENSION
how to create a role playing dimension in SSAS. Like If I have a period dimension table with a key named as per_key and fact table which has keys named as from_per_key and to_per_key .Now both the fact table keys need to be connected to the Period dimension per_key. gunja
View Replies !
Moving Some User Databases To New Server
Hi! I have a SQL 2000 server with a lot of databases (about 100) and I would like to move a great deal of them (+/- 50) to a new SQL 2005 server. All the databases I would move has the same word in the database name. Let's say the all include "Customer" in the Database name. It would be a huge job to detach and attach every db manually. Is there a method that can detach db's based on a query againts the master db with a select on the name of the db? If so, I also need to attach the db's on the new server with a script after moving the files to a new location. Any good ideas?
View Replies !
SQL Server 2005 / Securityadmin Role / SSMS
Experiencing a little weirdness with SQL Server Management Studio...Have created a login, that has fixed server role "securityadmin".The login can create/drop logins, add/remove them as users indatabases, etc.The login can change another login's password, using SQLfrom a query window (e.g. alter login bob with password='abc')However, the login *cannot* change another login's password,using SSMS GUI (e.g. "Security", "Logins", double-click "Bob",change password, say "OK"). The error text is:Change password failed for Login 'Bob'. (Microsoft.SqlServer.Smo)Additional information:An exception occurred while executing a Transact-SQL statement orbatch.(Microsoft.SqlServer.ConnectionInfo)Cannot alter the login 'Bob', because it does not exist or you donot have permission.(Microsoft SQL Server, Error: 15151)Has anybody else experienced this?
View Replies !
Web Application And Role Management In Sql Server 2005
Hi every body I'm developing a web application and i like to use the sql server 2005 role management features istead of developing a role management package in my program, I can do it on my tables and othe database items but I have no idea about using database access rights in my web pages to permit some one viewing or updating a web form... Is there any system table or system stored procedure showing access rights in my data base? or is there another idea to do this?? by Thanks Javaneh
View Replies !
SQL Server 2005 Application Role's Problem
Hi all. I have developed a VB6 program which will activate an Application Role to UPDATE and INSERT some tables in SQL Server 2005. My program will login with a username, say USER and then run with the rights of the Application Role which will SELECT, UPDATE and INSERT the following tables: 1) Table A 2) Table B 3) Table C The USER login is a restricted user which has only SELECT permission to Table A, B and C. I encounter a problem in which my program can only UPDATE table B and table C but Table A. I have double checked the settings of Table A, Table B and Table C. Theirs are all same. Fyi, the column which can't be updated is of type "datetime". Once i grant USER login UPDATE permission to Table A, my program works perfectly in which it can UPDATE all the tables including Table A. I have tried for almost 2 days but am still clueless. Any ideas? Thank you so much.
View Replies !
Creating User In SQL Server
i am a newbie in SQL Server. i want to add a new user to my database, whom i want to grant only insert, update, delete, select commands and some procedures/triggers. i need to log on to the database using that user from my asp.net application. i might have several users to log no to the system and for each user i need a set of tables in the databse. i want to wirte the procedures /triggers once for all the users in the database where they can access. i can write the procs under dbo schema, but i what about the tables? currently i am using SQL Express to test my database. i have Management Studio Express installed on my PC. S ]-[ /- | ]-[ /- N
View Replies !
SQL Server 2000 And 2005 - Application Role - Sp_setapprole
Hi All, Is there any limitation for setting password to an approle (like it should be 8 character long, should not start with numbers) ? If so, what are those limitations ? Does the same is applicable for sp_setapprole which uses the same password ? please confirm, for both SQL Server 2000 and 2005 versions. thanks in advance, Regards, Kailai
View Replies !
What Role Should Be Given To The End User?
Hi 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.
View Replies !
Problems Creating New User In MS Sql Server Express
Hi all, I'm trying to create a new user and a new db for my website in ms sql server express. I'm creating a new user, but when I try to login, i receive this error:error 18456. I've activated both windows and sql server authenthication. Can anyone help me or give me a link in which is explained how to create a user e configure the access?Thank You!
View Replies !
User/Role Check
Can anyone out there help me write a sp to determine if a user has a certain role? I'm trying to use "sp_helpuser @UserName". Can I declare a cursor with "EXEC sp_helpuser @UserName"? I'm not having success with this. What about SELECT [GroupName] FROM EXEC sp_helpuser @UserName WHERE [GroupName] = @GroupName? Again, syntax error. Is there already a better way that someone knows of?
View Replies !
Uploading SQL Express To SQL Server 2005 - Role's Causes Application Error With SSE Provider
I am very frustrated. Everything works on the local host but when I upload to server I can login to the admin role I created, but when I try to access pages that have role priveleges I get the following error: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. The ASPNETDB.MDF database was uploaded using the Database Publishing Wizard. Please help!
View Replies !
Can't Make Database Role A Member Of Another Database Role In 2005.
In 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 Replies !
Get The Currently Logged In User's SQL Role In A Different Database
NOTE: I am talking about roles in my sql server - NOT in asp.net. I need to create a stored procedure that retrieves the roles that the currently logged in sql user has for a different database. I have the code that gets the roles for the user, but it only works if the user is in the database. I want to be in one database, and get the roles for a different database. I have tried using USE DATABASE, but this is not allowed in a store procedure.
View Replies !
Cannot Use The Reserved User Or Role Name 'db_datareader'.
Hi, I have got a problem. When I try to access my database table Users, I get the following error: SELECT permission denied on object 'Users', database 'Users', owner 'dbo'. So I tried to grand this select command in MS Web Data Administration, but it doesnt work. When I try to grand db_datareader role to dbo, I get the following error [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the reserved user or role name 'db_datareader'. Does someone have an idea where could be a problem?
View Replies !
Retrieving User/Role Privileges - How ?
Hi, I need to read and subsequently modify the privileges (rights) of a certain SQL Server user / role from within a Visual Basic Program. Modifying seems to be easy using standard statements like GRANT/REVOKE. But what about reading all the rights a user has ? I have researched SQL-DMO, but didn't find what I'm looking for. Any idea ? Mike
View Replies !
Retrieving User Defined Role Name
Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver. Greetings, Godofredo
View Replies !
Remove User From Role Using Tsql
Hello, How can I remove a user from a role in tsql. I can add a user to a role by using exex sp_addroletomemer 'role', 'username' Please help me ? Regards Mirco p.s. I found the Procedure sp_dromrolemember Thanks
View Replies !
|
TRACKER
