Embedding isolation level in SQL OLE DB connection string?

Is there any way to embed the desired connection isolation level in a SQL
OLE DB connection string? I saw that the property "Isolation Level" exists
on SQL OLE DB connections but couldn't find a way to get this into the
connection string (extended property didn't work)
For 3rd party products that take connection strings and SQL queries and run
with them, it would be handy to be able to control the isolation level in
the connection string. I can do it at the SQL level, but I want the
isolation level to be configurable and not have to parse out/re-write the
SQL based upon the configured isolation level.
Thanks,
Mike
Mike Jansen wrote:
> Is there any way to embed the desired connection isolation level in a
> SQL OLE DB connection string? I saw that the property "Isolation
> Level" exists on SQL OLE DB connections but couldn't find a way to
> get this into the connection string (extended property didn't work)
> For 3rd party products that take connection strings and SQL queries
> and run with them, it would be handy to be able to control the
> isolation level in the connection string. I can do it at the SQL
> level, but I want the isolation level to be configurable and not have
> to parse out/re-write the SQL based upon the configured isolation
> level.
> Thanks,
> Mike
You can change the isolation level from the default of READ COMMITTED to
any of the other supported levels by issuing a single SQL statement
after you connect and it will stay in effect for the life of the
connection.
SET TRANSACTION ISOLATION LEVEL
{ READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
David Gugick - SQL Server MVP
Quest Software

Eliminate a table

I want to eliminate a table but this it marks the message to me:
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table 'NOM_TABLE' because it is being used for replication.
This pertenecia to one, but no longer exists the replication, now which I want to eliminate it leaves to me in error, already tries many things but I cannot even eliminate it, I want to think that the SQL I leave to this table with some estatus on the replication that pertenecia, but as I can eliminate it, some reference.
bye
try
sp_removedbreplication <dbname>
if that doesn't work try
Exec sp_configure 'allow updates', 1
Reconfigure With Override
go
update sysobjects set replinfo = 0 where replinfo <> 0
go
Exec sp_configure 'allow updates', 0
Reconfigure With Override
go

Effective Permissions Error with Domain User

If I create a new Login at the server level for a domain group :i.e.,
MyDomain\Domain Users
Then I go add a user tied into this new Login to my database i.e., Northwind
or pubs to add a user tied to this login.
Why is it then when I go to Database Properties -> Permissions and select
Effective Permissions on this new user I get the following error:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot execute as the server principal because the principal "EGUIRES\Domain
Users" does not exist, this type of principal cannot be impersonated, or you
do not have permission. (Microsoft SQL Server, Error: 15406)
For help, click:
tSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
BUTTONS:
OK
The guest account seems fine for this but any manual entries I try that is
linked to my domain gets this error.
I would appreciate any light you can shed on this
Scott EguiresScott
Did you get these database from SQL Server 2000 by using a RESTORE command?
What is the compatibilty level of both databases?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
> If I create a new Login at the server level for a domain group :i.e.,
> MyDomain\Domain Users
> Then I go add a user tied into this new Login to my database i.e.,
> Northwind
> or pubs to add a user tied to this login.
>
> Why is it then when I go to Database Properties -> Permissions and select
> Effective Permissions on this new user I get the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Cannot execute as the server principal because the principal
> "EGUIRES\Domain
> Users" does not exist, this type of principal cannot be impersonated, or
> you
> do not have permission. (Microsoft SQL Server, Error: 15406)
> For help, click:
> EvtSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
> --
> BUTTONS:
> OK
>
> The guest account seems fine for this but any manual entries I try that is
> linked to my domain gets this error.
>
>
> I would appreciate any light you can shed on this
>
> Scott Eguires
>|||Yes I did. I set the database compatibility to 2005. WHat is interesting i
s
that Effective Permissions work for the Domain\Administrator group. I did a
server profile trace and found that it was calling the Execute As User. Thi
s
led me to the following clues:
When I run the following:
EXECUTE AS USER = 'MYDOMAIN\Domain Users';
I get the error:
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal
"MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
impersonated, or you do not have permission.
But if I run:
EXECUTE AS USER = 'MYDOMAIN\Administrator';
It works fine.
This leads me to believe it is some sort of permissions issue.
Any info would be greatly appreciated.
Thanks
"Uri Dimant" wrote:

> Scott
> Did you get these database from SQL Server 2000 by using a RESTORE command
?
> What is the compatibilty level of both databases?
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
>
>|||Scott
Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
the user a member of loca Administrator group on a computer when you run
SQL Server?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...[vbcol=seagreen]
> Yes I did. I set the database compatibility to 2005. WHat is interesting
> is
> that Effective Permissions work for the Domain\Administrator group. I did
> a
> server profile trace and found that it was calling the Execute As User.
> This
> led me to the following clues:
> When I run the following:
> EXECUTE AS USER = 'MYDOMAIN\Domain Users';
> I get the error:
> Msg 15517, Level 16, State 1, Line 1
> Cannot execute as the database principal because the principal
> "MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
> impersonated, or you do not have permission.
>
> But if I run:
> EXECUTE AS USER = 'MYDOMAIN\Administrator';
> It works fine.
> This leads me to believe it is some sort of permissions issue.
> Any info would be greatly appreciated.
> Thanks
>
> "Uri Dimant" wrote:
>|||No the 'MYDOMAIN\Domain Users' login is neither a member of the local
administrator
group nor a member of the sysadmin server role on the machine I am running
SQL Server.
"Uri Dimant" wrote:

> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>|||Scott
I tried tp reproduce the problem on my worstation SQL Server 2005 (Dev
Edition)
1) Restore a database from SQL Server 2000 which had an user called 'John'
(I changed compatibility level to 90)
2) Create a new Login 'NewJohn' and granted him an access to the database
(non sysadmin)
3) Right Click on the database --Properties --Permissions ( I sow
'John' user along with'NewJohn' )
4) Click on Effective Permissions ( I got the same error)
5) Click on Remove button and removed 'John' user
6) Click on Effective Permissions again and now it working
I think it is because that user "John" was transfered as a database user
(orphaned) without mapping to any login on the new server
MS has provided two stored procedures to move the logins with their original
SID but it worked fime on SS2K and I have not tried yet on SS2005
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:4F36A7F9-3108-4142-875A-2DB8CA46ABB3@.microsoft.com...[vbcol=seagreen]
> No the 'MYDOMAIN\Domain Users' login is neither a member of the local
> administrator
> group nor a member of the sysadmin server role on the machine I am running
> SQL Server.
>
> "Uri Dimant" wrote:
>|||It seems that you cannot execute as user on a Group. Which makes sense sinc
e
the text is
Execute as User. So this tells me I will never be able to get the effective
permissions for a group
through the SQL Workbench tool which is fine I suppose. I had an additional
problem in that I had
my service account was set to local and needed to set it to a domain user
with appropriate rights. Without that my Execute as user fails since it
cannot resolve the user in my domain.
Scott Eguires
"Uri Dimant" wrote:

> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>