Can I Administrate A Db Without Explicit Permissions On That Db?
May 8, 2008
Hi all,
I'm using SQL Server Express 2005, with mixed authentication mode.
I would like to connect, manipulate data and administrate db's on my server without explicit permissions on those db's.
I've created a new Login with SQL Server authentication and assigned it the public & sysadmin server roles BUT I can't even connect to db's where I don't have an explicit db role (db_owner, ddl_admin...)!
What am I missing?
How can I administrate a db without explicit permissions (db role) on that db?
Thanks,
Assaf
View 6 Replies
ADVERTISEMENT
Mar 6, 2013
The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
View 9 Replies
View Related
Jun 26, 2007
Hi All,
I am looking for a description of all explicit permissions for server. I can't find it anywhere, I checked BOL and the internet without any luck. Any ideas?
View 4 Replies
View Related
May 22, 2000
I am a student working on an Asp/SQLserver project, and I really need help!
I got permission to move my database from a test machine to my school's SQLserver. In Enterprise Manager, a teacher made an empty database and gave me permission to use it. Unfortunately, I'm not allowed to work directly on the server, so I were given the IP-adress of the SQLserver and a password. I have access to a SQLserver desktop version running on NT installed on my computer. The problem I have is :
How can I connect to the remote server and run sqlscripts to make the database?
The only server available in Enterprise Manager is local, and when I try to make a new server-entry there is nowhere to enter the IP-adress of the remote server, hence I get no connection!?!
If anyone knows how to solve this problem I would greatly appreciate all help!
Eirik
View 2 Replies
View Related
Feb 4, 2008
is anybody using this? it looks like a lot of trouble to return a formatted string. I am considering it for something. Any opinions?
http://technet.microsoft.com/en-us/library/aa226532(SQL.80).aspx
caution: you have to X out of the Do You Want to Download Silverlight popup.
View 12 Replies
View Related
Jul 23, 2005
i have a situation, where i need to group my information by a certainid, but that information which should already be grouped, i need toorder based on a number in one of the columnsexample:<ex id=2 listorder=1><description>desc 2</description></ex><ex id=1 listorder=2><description>desc 1</description></ex>in my order clause i first put order by the id, and it will group theinformation accordingly, but will put the lowest number id first, notcaring about the listorderbut if i order by the listorder column first, the information isn'tgroupd properlyi don't know if i made myself clear enough?hope someboyd can help methnx in advance
View 1 Replies
View Related
Jun 8, 1999
Hi ,
I've tried to switch MS-Sql/Server 6.5 on explicit_transactions
without success.
(set implicit_transaction on/off)
What is the exact syntax for doing that ?
Herve
(PS: Thanks Gregory for your quick answer )
View 1 Replies
View Related
Jun 3, 1999
Hi All,
I don't know MS-SQLserver internal system at all. I 've just used Oracle
a couple years ago and so in some cases (e.g using TP-monitor MTS or Tuxedo)
you can switch off the implicit transaction by using
the option AUTOCOMMIT ON/OFF.
How can switch off the implicit transaction system on MS-SQLServer ?
In advance thanks,
Herve
View 1 Replies
View Related
Sep 7, 2004
I created a SELECT statement that uses the FOR XML EXPLICIT clause so that SQL Server data can be exported to another system. Format thios system accepts is XML. What should I define within a DTS package so that this Select statement is executed and an XML file is created?
View 12 Replies
View Related
Feb 27, 2006
I have an cursor that loops 720 times.
Each FETCH does 6 INSERTS.
Each FETCH the 6 INSERTs a total of 200-300 records appended.
Would explicit transactions speed up the cursor?
In Oracle, I would keep track of how many times I've looped, and commit every nth time. I would put a catch remainder COMMIT at the end of the procedure.
I've got the IF @@TRANCOUNT > 19 COMMIT TRAN (about 6k records), but say I had <= 19 remaining at the end of the cursor, how do you get those to commit? I tried to put an IF @@TRANCOUNT > 1 COMMIT TRAN, but that didn't work.
What's a good @@TRANCOUNT to commit at in this case?
Thanks crew,
Carl
View 4 Replies
View Related
Jan 30, 2007
Hello,
In the SP below, I get the following error when I run it:
Explicit value must be specified for identity column in table 'Tests' when IDENTITY_INSERT is set to ON.
From what I've read of the INSERT method, it says you can add a variable value. So I don;t understand why it won't take mine.
CREATE PROC qaspAddTestRecord AS
DECLARE @aKey bigint
DECLARE @aTestSuiteId bigint
exec qaspGetNewID 'Tests', @aKey OUT
SELECT @aTestSuiteId= NewID FROM ID WHERE TableName='TestSuite'
SET IDENTITY_INSERT Tests ON
INSERT INTO Tests (TestSuiteId, TestIDInternal) VALUES (@aTestSuiteId, @aKey)
SET IDENTITY_INSERT Tests OFF
GO
--PhB
View 4 Replies
View Related
Jan 7, 2008
Hello all,I'm trying to generate some XML directly from MS SQL with thefollowing codeSELECT1 AS tag, NULL AS parent, NULL AS [GoogleCustomizations!1], NULL AS [Annotations!2], NULL AS [Annotation!3], NULL AS [Annotation!3!about], NULL AS [Annotation!3!score], NULL AS [Label!4], NULL AS [Label!4!name]UNIONSELECT2 AS tag, 1 AS parent, NULL, NULL, NULL, NULL, NULL, NULL, NULLUNIONSELECTTOP 503 AS tag, 2 AS parent, NULL, NULL, NULL, 'www.' + domainName, 1 -- score, NULL, NULLFROM tbl_auDomainNameUNIONSELECT4 AS tag, 3 AS parent, NULL, NULL, NULL, NULL, NULL, NULL, '_cse_ad-o6lgdody'FOR XML EXPLICITThe XML it needs to create is as following<GoogleCustomizations><Annotations><Annotation about="www.clickfind.com.au/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation><Annotation about="www.lookle.com/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation><Annotation about="www.sensis.com.au/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation></Annotations></GoogleCustomizations>It is currently creating<GoogleCustomizations><Annotations><Annotation about="www.10000steps.org.au" score="1" /><Annotation about="www.101fm.asn.au" score="1" /><Annotation about="www.aao.gov.au" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation></Annotations></GoogleCustomizations>I cannot get my head around how I can get the label <Labelname="_cse_ad-o6lgdody" />in each element. Does anyone know?Thanks in advance.
View 2 Replies
View Related
Jan 31, 2007
HiI have to create an XML file based on a SQL SERVER 2005 table.Everything works fine:SELECT 1 as Tag,
NULL as Parent,
Ex_Id as [Exam!1!Ex_Id],
NULL as [Ex_Title!2!!cdata]
FROM Exams
WHERE ex_State = 'P'
UNION ALL
SELECT 2 as Tag,
1 as Parent,
Ex_Id,
ex_Title
FROM Exams
WHERE ex_State = 'P'
order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata]
FOR XML EXPLICIT, ROOT('Exams')
BUT when i add another column, a text column, I get into trouble: SELECT 1 as Tag,
NULL as Parent,
Ex_Id as [Exam!1!Ex_Id],
NULL as [Ex_Title!2!!cdata],
NULL as [Ex_Situation!3!!cdata]
FROM Exams
WHERE ex_State = 'P'
UNION ALL
SELECT 2 as Tag,
1 as Parent,
Ex_Id,
ex_Title,
ex_Situation
FROM Exams
WHERE ex_State = 'P'
order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata], [Ex_Situation!3!!cdata]
FOR XML EXPLICIT, ROOT('Exams')
The problem is clearly the fact that i have to sort on the text column, plus the fact that this column requires the CDATA tag enclosurePlease help me....i'm desperate :(
View 1 Replies
View Related
Jan 19, 2004
Hi:
How can I force MsSQL to a explicit DateFormat.
Like: 'DD/YY'
View 4 Replies
View Related
May 20, 2008
performance wise what is the difference between following queries
Select * from A, B Where A.ID = B.ID
Select * from A INNER JOIN B ON A.ID = B.ID
(ID is the primary key)
View 6 Replies
View Related
Apr 24, 2006
I am developing an integration process between two databases. One ofthem is a SQL Server 2000 and the other is using MSDE 2000. Theintegration process is done in C# (VS2003).The main database is the SQL Server, the MSDE will contain a reallysmall subset of the data found on the main. To help diminish the amountof time taken to develop an integration process between thosedatabases, the same structure are found on both side. The onlydifference, when I insert data in the MSDE from the SQL Server, I setthe IDENTITY_INSERT to ON and use the same IDs found on the SQL Server.I can insert one set of data without problem, but from there, if I tryagain, I will always receive the "Cannot insert explicit value foridentity column in table ... when IDENTITY_INSERT is set to OFF." Isaw on Microsoft website the article ID 878501; I noticed I was usingMSDE sp3, I upgraded to SP4... and I still have the problem.I know, when I call the update function on the sqldataadapter, theadapters contain the IDENTITY_INSERT ON and it's set to OFF after theinsert. The "Cannot insert..." error is the only one I received.Can anyone help me on that issue? Take note that this approach wasused because of customer requirements; the size of the database alsocauses some problem (over 200 tables) and we decided to use the samestructure on both side to minimize the support time.
View 3 Replies
View Related
Sep 15, 2005
for some unknow reasons.. my store proc stop working.. and i got an error.. i have installaed the latest SP4 for SQL server 2000 and still have the problem !any ideas why ?? Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."CREATE procedure LCMS_Modules_Add
@PageID int, @ModuleDefID int, @Panename nvarchar(32), @Title nvarchar(128), @Admins nvarchar(256)
as
insert into LCMS_Modulesvalues(@PageID, @ModuleDefID, 99, @Panename, @Title, '0;', @Admins, 0, '', 'Center', '', '', '', 1, 0)GO
View 1 Replies
View Related
Jul 12, 2006
I have a DTS that pulls a file from a networked drive (generally using the u: designation) for part of its process. Recently, the server has been dropping network drive connections, so sometimes the DTS will fail (I'm not certain why it's dropping the connections, nor do I have the access or control to figure out such.)
I've been told that I should change the DTS package to use an explicit path (i.e., \serverfolder1folder2file.mdb instead of u:folder2file.mdb) to work around this problem. However, Enterprise Manager doesn't seem to like using this for the "File name" attribute in the connection properties. (This is an Access connection, if that matters.)
Is there any way to get the explicit path (probably the wrong terminology here) to work with the connection properties?
View 2 Replies
View Related
Jan 31, 2006
I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>
Here's my code:
SELECT 1 as Tag,
NULL as Parent,
@p_school_desc as [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULL as [grouptype!1!scheme!element],
NULL as [grouptype!1!typevalue!element],
NULL as [description!1!short!element],
NULL as [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destination as [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_code as [description!2!short!element],
@p_term_description as [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT
I'm receiving the foollowing error:
Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.
Is there anyway to have two different child notes off of the same parent node?
Thanks in advance!
View 1 Replies
View Related
Jan 31, 2006
I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>
Here's my code:
SELECT 1 as Tag,
NULL as Parent,
@p_school_descas [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULLas [grouptype!1!scheme!element],
NULLas [grouptype!1!typevalue!element],
NULLas [description!1!short!element],
NULLas [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destinationas [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_codeas [description!2!short!element],
@p_term_descriptionas [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT
I'm receiving the foollowing error:Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.
Is there anyway to have two different child notes off of the same parent node?
Thanks in advance!
View 1 Replies
View Related
Apr 16, 2008
Hi,
Is it possible to format any kind of XML file with the FOR XML EXPLICIT mode in SQL Server 2005? I am asking because I am trying to format my file in a way that I could get 2 or 3 elements on my parent or child level and put some attributes (icon path for example) in these 2nd element or 3rd element. I am unable to format it that way. Let me show you the way that I would like to have my XML file formated :
<?xml version="1.0" encoding="UTF-8" ?>
<rows>
<row id="14" sort="parent">
<cell icon="../../images/TreeGrid/folder.gif">Operations</cell>
<row id="14.13" sort=”child”>
<cell icon="../../images/TreeGrid/Red.gif">% SP</cell>
<row id="14.13.1" sort=”sub-child”>
<cell icon="../../images/TreeGrid/Red.gif">% SP Sub</cell>
I want to have all my parent, children, sub-children to have these attribut but I am having difficulty putting one in the “Cell” tags. And I really think that my problem resides in the specifying of my column and their directives but I do not know how to fix it. See my icon path is commented here. I have to put the [row!2!cell!xmltext], but it is not working.
Here is the way that I am building my Select query.
SELECT
1 as tag,
NULL as parent,
NULL AS 'rows!1!', ----Root rows tag
ID_metric as [row!2!id],
Description_english as [row!2!cell!Element],
----Icon as [row!2!cell!xmltext],
Sort as [row!2!sort],
ID_metric as [row!3!id],
Description_english as [row!3!cell!Element],
--Icon as [row!3!cell!xmltext],
sort as [row!3!sort],
ID_metric as [row!4!id],
Description_english as [row!4!cell!Element],
--Icon as [row!4!cell!xmltext],
sort as [row!4!sort],
ID_metric as [row!5!id],
Description_english as [row!5!cell!Element],
--Icon as [row!5!cell!xmltext],
sort as [row!5!sort],
FROM #buildData
WHERE HLevel = 0
UNION ALL
SELECT
2 as tag,
1 as parent,
NULL AS 'rows!1!',
ID_metric as [row!2!id],
Description_english as [row!2!cell!Element],
--Icon as [row!2!cell!xmltext],
sort as [row!2!sort],
ID_metric as [row!3!id],
Description_english as [row!3!cell!Element],
--Icon as [row!3!cell!xmltext],
sort as [row!3!sort],
ID_metric as [row!4!id],
Description_english as [row!4!cell!Element],
--Icon as [row!4!cell!xmltext],
sort as [row!4!sort],
ID_metric as [row!5!id],
Description_english as [row!5!cell!Element],
--Icon as [row!5!cell!xmltext],
sort as [row!5!sort],
FROM #buildData
WHERE HLevel = 1
UNION ALL
SELECT
3 as tag,
2 as parent,
NULL AS 'rows!1!',
ID_metric as [row!2!id],
Description_english as [row!2!cell!Element],
--Icon as [row!2!cell!xmltext],
sort as [row!2!sort],
ID_metric as [row!3!id],
Description_english as [row!3!cell!Element],
--Icon as [row!3!cell!xmltext],
sort as [row!3!sort],
ID_metric as [row!4!id],
Description_english as [row!4!cell!Element],
--Icon as [row!4!cell!xmltext],
sort as [row!4!sort],
ID_metric as [row!5!id],
Description_english as [row!5!cell!Element],
--Icon as [row!5!cell!xmltext],
sort as [row!5!sort],
FROM #buildData
WHERE HLevel = 2
UNION ALL
SELECT
4 as tag,
3 as parent,
NULL AS 'rows!1!',
ID_metric as [row!2!id],
Description_english as [row!2!cell!Element],
--Icon as [row!2!cell!xmltext],
sort as [row!2!sort],
ID_metric as [row!3!id],
Description_english as [row!3!cell!Element],
--Icon as [row!3!cell!xmltext],
sort as [row!3!sort],
ID_metric as [row!4!id],
Description_english as [row!4!cell!Element],
--Icon as [row!4!cell!xmltext],
sort as [row!4!sort],
ID_metric as [row!5!id],
Description_english as [row!5!cell!Element],
--Icon as [row!5!cell!xml],
sort as [row!5!sort],
FROM #buildData
WHERE HLevel = 3
ORDER BY [row!2!sort], [row!3!sort], [row!4!sort], [row!5!sort]
for XML explicit
My actual XML Result is this :
<rows>
<row id="14" sort="parent">
<cell>% SP</cell>
<row id="14.13" sort="child">
<cell>% SP </cell>
<row id="14.13.1" sort="child">
<cell>% SP cild </cell>
Is it possible to customized anything with For XML Explicit?
Anybody that can help my with that or indicate me where I am doing wrong? Thanks a lot.
View 11 Replies
View Related
Jul 9, 2014
SQL 2012R2 Std. I have a sql statement to retrieve data in XML format. I have to use EXPLICIT because I have to use tag <CDATA>
I would like to store the XML returned into 1 column containing the XML tags.
For example:
#tmpXML
row 1: <DOC><NET_INFO net_ID=34><NOTES><![CDATA[description]]></NOTES></NET_INFO></DOC>
row 2 <DOC><NET_INFO net_ID=35><NOTES><![CDATA[description2]]></NOTES></NET_INFO></DOC>
select *
into #tmpXML
FROM (SELECT 1 AS Tag
[Code] ....
But I keep getting an error message:
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
I have tried a couple of other ways but continue to get an error message.
View 3 Replies
View Related
Oct 25, 2006
Hello Everyone,
I am trying to insert a new record into a sql table and I get the following error message:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'tbl_MLS_ReportCriteria' when IDENTITY_INSERT is set to OFF.
Here is the code:
USE MLS
INSERT INTO dbo.tbl_MLS_ReportCriteria (CriteriaID, Name, DisplayName, DetailedInstructions, TypeID, Source, DefaultValue, ValueField, DescriptionField, IncludeAllOption)
VALUES ('65', 'Summary3', 'Tertiary Summarize By', NULL, '2', 'SelectOptions', 'Company', NULL, NULL, '0')
What do I need to change and or look for?
TIA
Kurt
View 9 Replies
View Related
Mar 31, 2008
Is there any difference between explicit inner join and implicitinner joinExample of an explicit inner join:SELECT *FROM employeeINNER JOIN departmentON employee.DepartmentID = department.DepartmentIDExample of an implicit inner join:SELECT *FROM employee, departmentWHERE employee.DepartmentID = department.DepartmentID
View 11 Replies
View Related
Sep 19, 2005
I gave a developer rights to the Public role on a SQL Server 2000 database. The Public role only has explicitly set select rights to the system tables and one user table. There are no other explicit rights set. The developer was able to open a table that had no rights set in enterprise manager and change data. Is this possible?
View 1 Replies
View Related
May 30, 2008
Hi
What is SQL Server 2005 default behavior with statement like this:
SELECT * FROM dbo.Products
Will it take any shared locks on Products table? Is it different than:
BEGIN TRAN
SELECT * FROM dbo.Products WITH NOLOCK
COMMIT
TIA
View 2 Replies
View Related
May 6, 2008
Hi,
Is it possible to explicitly insert a value into an identity column??
View 1 Replies
View Related
Nov 1, 2006
Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii
characters.
But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"
DECLARE @XMLMessage XML
SET @XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.
Let me know if something dose'nt make sense
any help is appreciated
Thanks
View 1 Replies
View Related
Aug 2, 2006
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
View 5 Replies
View Related
Dec 14, 2004
Cannot insert explicit value for identity column in table 'tblUsed' when IDENTITY_INSERT is set to OFF.
:confused:
View 1 Replies
View Related
Jul 20, 2005
Could some database expert post some information or link about that?thanksJanusz
View 1 Replies
View Related
Jun 21, 2006
I€™m using triggers for some more advanced integrity check. The problems is that the same trigger can be run from explicit transaction (this is when I start transaction from .NET) and as autocommit transaction ( very rare, only when we do some maintenance directly with SQL statements).
Currently if I want to rollback transaction from trigger I only issue RAISERROR statements, then .NET application catches this error and generates rollback. But the problem is if trigger is raised from some SQL statements outside .NET application (normally some maintenance work direct from SQL manager ) in that case error is generated but there is no rollback.
Is there any way to distinguish if transaction in trigger is explicit or autocommited, because for autocommited transaction I also need use ROLLBACK TRANSACTION?
I€™m using SQL 2005!
Best regards
edvin
View 6 Replies
View Related
