How To Drop A User Defined Database Role In 2005?

Mar 9, 2007

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 3 Replies


ADVERTISEMENT

Retrieving User Defined Role Name

May 4, 2004

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 2 Replies View Related

Drop Role, User, Login

Jul 14, 2006

Okay I figured out how to determine if stored procs and funcs exist before dropping them.

How do I do the same for ROLE, LOGIN, USER?

I want get rid of annoying messages in my scripts when trying to drop something that doesn't exist.

Server 2005 and Server Express 2005

Thanks



View 3 Replies View Related

Using User Defined Data Type (UDT) In A Sql Server 2005 Database + Window Application.

Feb 18, 2006

Hi,

I am using VS2005 C# + sql server 2005 Express edition.

I need to using a database that uses my own defined data types to define its tables columns.

I already have designed a Database projact and create the new UDT as follows:



Create a new Database project in the Visual C# language nodes.


Add a reference to the SQL Server 2005 database that will contain the UDT.


Add a User-Defined Type class.


Write code to implement the UDT.


Select Deploy from the Build menu.

Now I need to ask some quistions:

1- When I try to add a new query to a table that contains my new data type in its columns,if I try to exexute the query the next message appears:

'Execution of user code in the .Net framework is disabled. Enable "clr enabled" configuration option'.

How can I doing that??

2- I need to use that database - which has the new data type - in a traditional ' Visual C# Windows Application' instead of 'Database', but:

when I try to add a new Data Source that contains the tables that have the new data types in its definitions, the next message appears:

'<AyaDatabase.dbo.MyNewUDTTable>

User-defined types(UDTs)are not supported in the Dataset Designer.'

So, how can I resolve that problem??

please help me.

Thanks in advance for any help.

Aya.



View 4 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob

View 2 Replies View Related

Can't Make Database Role A Member Of Another Database Role In 2005.

Jan 9, 2006

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 1 Replies View Related

Sql 2005 Database Role Vs Application Role ?

May 18, 2007

Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?

thanks

View 2 Replies View Related

Adding A User/role To All User Database

Jan 23, 2001

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 1 Replies View Related

Granting Permission To A Database User To Alter Database Role

Sep 5, 2006

I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??

View 1 Replies View Related

Get The Currently Logged In User's SQL Role In A Different Database

May 14, 2008

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 10 Replies View Related

Database Role/User Query

Sep 21, 2006

Anyone have a tsql query that will give me a listing of database roles and their users already put together?

View 1 Replies View Related

Creating Databases Under MS SQL 2005 Server (SP-2): Dbo User Role

Jun 9, 2007

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 6 Replies View Related

Create User - Permit In Database Role

Jan 7, 2004

Hi,
I hv an application which is using ASP.net. The connectionstring in web.config is
<appSettings>
<add key = "constring" value = "Initial Catalog=mydatabase;Data Source=mypc-pc;User ID=User1; Password=password1"/>
</appSettings>"

Then, i hv created a user in SQL Server 2000 which is User1. What should i put for the database role? db_owner or just db_datareader and db_datawriter?

pls help.

Thnx

View 4 Replies View Related

User Or Role Already Exists In The Current Database

Oct 9, 2000

Hi!
I've restored a backup from server A in server B. In server A I used to have a db_owner, called sitebase. After restoring the backup in server B, I created the user sitebase again and tried to grant him db_owner through Server Manager but I got the error: user or role already exists in the current database.
How can I avoid this situation and grant db_owner to him?
Thanks,
Fábio

View 1 Replies View Related

User Not A Choice When Trying To Add To Db_owner Role For Database

Mar 14, 2008


How do I get a particular user to be a choice under the db_owner role for a particular database?
The user is listed under logins and even shows to be the db_owner for the database under the database access tab of the login properties. This is SQL 2000.
Thanks,
David P.

View 1 Replies View Related

Will User's Permission Be Overriden By Database Role?

Dec 26, 2006

in SQL server 2005, Database User's permission will be overriden by the database Role's permission or ottherwise? For example, a userA is owner of table AA so it has all permisions on table AA but the user is a member of GroupB but group B has no permission to access to Table AA. What happen on User A?. has it permission to access to table BB or not? How can I find document or example about this?
Please help me, thanks so much

View 3 Replies View Related

Determining Database Role Of Querying User

Dec 7, 2007

I know that I can use:

IS_MEMBER('MyRoleName') = 1

to determine if a user is a member of a predetermined role, but how do I ask what the role name of the querying user is instead? Basically I want to do something like:

SELECT * FROM CityTable WHERE City IN (SELECT City FROM CitySecurity WHERE SelectGroup = ROLE_NAME)

I'm doing this because the "CitySecurity" table contains security information from another system that has been imported into SQL Server, where the 'SelectGroup', as part of process, will be named the same as the SQL Server role.

Thanks,
Kayda

View 4 Replies View Related

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

Aug 15, 2007

 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 13 Replies View Related

SQL 2005 Cannot Drop User

Oct 11, 2006

Hello,

I am new to SQL 2005 and I am trying to drop a user from a database called prod. When I try to delete the user I get the following error message:

"The database principal has granted or denied permissions to objects in the database and cannot be dropped."

Any help would be greatly appreciated. Thank you.

View 17 Replies View Related

How To Run User Defined Function On Sql Server 2005

Dec 24, 2007

Dear all
I wants to run sql server user defined function when linked two server.
 
I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?
 
Regards
Jerminxxx

View 7 Replies View Related

User-defined Stored Procedures InsertCustomerin Northwind Database That Is Cached In Database Explorer:No New Values Inserted?

Jan 14, 2008

Hi all,

I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:

--User-defined stored procedure 'InsertCustomer'--

ALTER PROCEDURE dbo.InsertCustomer

(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30),

@ContactTitle nvarchar(30),

@Address nvarchar(60),

@City nvarchar(15),

@Region nvarchar(15),

@PostalCode nvarchar(10),

@Country nvarchar(15),

@Phone nvarchar(24),

@Fax nvarchar(24)

)

AS

INSERT INTO Customers

(

CustomerID,

CompanyName,

ContactName,

ContactTitle,

Address,

City,

Region,

PostalCode,

Country,

Phone,

Fax

)

VALUES

(

@CustomerID,

@CompanyName,

@ContactName,

@ContactTitle,

@Address,

@City,

@Region,

@PostalCode,

@Country,

@Phone,

@Fax

)
=================================================
In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code:
--Form_Kimmel.vb--
Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form_Kimmel


Public Sub InsertCustomer()

Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _

"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"

Dim connection As SqlConnection = New SqlConnection(connectionString)

connection.Open()

Try

Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)

command.CommandType = CommandType.StoredProcedure

command.Parameters.Add("@CustomerID", "PAULK")

command.Parameters.Add("@CompanyName", "Pauly's Bar")

command.Parameters.Add("@ContactName", "Paul Kimmel")

command.Parameters.Add("@ContactTitle", "The Fat Man")

command.Parameters.Add("@Address", "31025 La Jolla")

command.Parameters.Add("@City", "Inglewoog")

command.Parameters.Add("@Region", "CA")

command.Parameters.Add("@Counrty", "USA")

command.Parameters.Add("@PostalCode", "90425")

command.Parameters.Add("@Phone", "(415) 555-1234")

command.Parameters.Add("@Fax", "(415 555-1235")

Console.WriteLine("Row inserted: " + _

command.ExecuteNonQuery().ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

Throw

Finally

connection.Close()

End Try



End Sub

End Class
==============================================


I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.

Thanks in advance,
Scott Chang

View 10 Replies View Related

Server And Database Role Requests For Normal User

Jun 5, 2007

Hi,

I want to know how to assign a normal user to take backup,Restore and attach and detach the database in sqlserver2000.

Please can anyone help me to find out which server role or database role will suites my requirements.

Regards
Md S.Hassan

View 1 Replies View Related

How Can One User View Other Users That Belong To A Database Role?

Nov 15, 2006

After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.

In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.

In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.

Any advice on how to duplicate the behavior from SQL2000?

View 8 Replies View Related

Can Not Drop User From Database

Oct 12, 2005

 I can not delete user from a database in sql2005 beta 3.

View 22 Replies View Related

How To Create User Defined Functions In Sql Server 2005

Mar 17, 2008

 Hi ,      how to create user defined functions in sql server 2005 to retrive data from multiple tables in asp.net....... Thank U! 

View 4 Replies View Related

How To Connect The User Defined Database Using Jdbc?

Mar 10, 2008

Hi,
I am working with the followings: jdk5 in eclipse3.3 and Microsoft sql server. For the jdbc application, i used to connect the database with the following code:
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con=DriverManager.getConnection("jdbc:microsoftqlserver://NODE1:1433","sa"," ");
The above code woks fine and I able to connect the database.

Here is my problem: The above code only connect the master database. But I want to connect the user defined databse(that is., other than master database).
Can anyone tell me the solution to solve the issue.

Looking forward to your reply,
Senthil.

View 6 Replies View Related

How To Fix &#34;Error 15023: User Or Role &#39;%&#34; Already Exists In The Current Database

Sep 20, 2001

I used a backup copy of our production DB (residing in our prod machine) to do a database RESTORE to our test DB (residing in our test machine). This step was successful.
However when I tried to access the test DB via Peoplesoft application, I am unable to logon. Only then did I notice that all the users, with the exception of "sa", were gone.
When I attempted to add a user via Enterprise Manager's Action - Add Database User, I get the message, "Error 15023: User or role '%' already exists in the current database.
What's the best way to fix this without resorting to copying the source server's master database (If i do this, I risk clobbering some other DB's that are present in the target server but not in source server)?
Any help you can provide will be greatly appreciated!!!

View 2 Replies View Related

User Defined Data Type Issues After Upgrade To 2005

Mar 13, 2008

Hi,

After upgrading to MSSQL 2005 we have stored procedures which get this error:

Msg 2715, Level 16, State 7, Procedure prc_sel_accruals, Line 37
Column, parameter, or variable #15: Cannot find data type umoney.

The user data type is defined in the database. I tried recompiling the SP, which suucceeds but then fails on execution.

The SP works under SQL2000. Is there anything we need to do after the conversion?

View 6 Replies View Related

How To Share User-defined Data Type In Different Database?

Nov 6, 2006

Hi,everyone.

I have defined a data type "OC_BUN_NAME" in database "CVPRO". I want to create a table tempdb.dbo.CVPRO in SQL SERVER 2005 system Database tempdb. But SQL SERVER 2005 DBMS gives a Error Messages:"Can not find the data type OC_BUN_NAME".

How can I do? How to use data types in the other database?

Please give me some advice. Thank you in advance.

View 7 Replies View Related

IS_MEMBER Not Working With User Defined Database Roles

Mar 1, 2007

I am not sure why this is not working it clearly states in the MSDN that it should "

'role'
Is the name of the SQL Server role being checked. role is sysname and can include the database fixed roles or user-defined roles but not server roles.

"

I have seen many questions revolving around this issue on this site and on the net about this but know one can answer it

I have created a new user defined database role called testrole with any owner

then created a new sql login and user (Sql Authentication)

add the user to the database role testrole

check IS_MEMBER and it returns 0

try this with a fixed database role and I get the desired result of 1

this is simple and should not be such a problem for every one

unless I am doing something wrong

Please help

Thanks

View 3 Replies View Related

[OT] User-Defined String Functions MS SQL Server 2005 Transact-SQL SQLCLR .Net

Feb 7, 2006

User-Defined string Functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net)

Ladies and Gentlemen,

I am pleased to offer, free of charge, the following string functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net):

AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side.
PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).

AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.

Plus, there are CHM files in English, French, Spanish, German and Russian.

Plus, there are versions for MS SQL SERVER, SYBASE ASA, DB2, Oracle.

More than 8000 people have already downloaded my functions. I hope you will find them useful as well.

For more information about string UDFs MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net) please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,29527

Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,29527

With the best regards.

View 1 Replies View Related

SQL 2012 :: Unable To Drop Database User

May 7, 2014

We are unable to drop a database user with the following error "Msg 15284, Level 16, State 1, Line 1

The database principal has granted or denied permissions to objects in the database and cannot be dropped."

On checking the database permissions we have the following permissions assigned to the user

classclass_descmajor_idminor_idgrantee_principal_idgrantor_principal_idtypepermission_namestatestate_desc
17SERVICE655360517SN SENDGGRANT
17SERVICE655370517SN SENDGGRANT
17SERVICE655380517SN SENDGGRANT
17SERVICE655390517SN SENDGGRANT
17SERVICE655400517SN SENDGGRANT

[Code] ....

How we can revoke the SEND permissions?

View 3 Replies View Related

SQL Server Express 2005 - How To Delete/drop A User Instance

May 23, 2008

Hi,

I have been working around with SQL Server Express 2005 on VISTA. I created a small C++2008 application which interface with SSE 2005. When running my application, it automatically created a user instance which I would like to get rid of. I just forgot from start to set "User Instance = False" in my connectionstring. (oups...)

I'm aware, from readings I did, that a couple of tables as well as log files, etc. are created under directory "utilisateurs...AppDataLocalMicrosoftMicrosoft SQL Server DataSQLEXPRESS". SQLEXPRESS is the instance name chosen during install of SSE2005.

Also using following SQL Statement:

select owning_principal_name, instance_pipe_name, heart_beat
from sys.dm_os_child_instances
I got following result:
owning_principal_name: PC-DE-STEPHANEStéphane
instance_pipe_name: \.pipe6172F4E8-622E-4A sqlquery
heart_beat: dead (at this moment...)

==> How can I make a good cleanup of all this as well as get rid of the user instance itself?


Thanks in advance for any help.

Stéphane

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved