Showing posts with label access. Show all posts
Showing posts with label access. Show all posts

Thursday, March 22, 2012

Database Size - how to get

Could anybody remind please, how can I get the current database size having
the full access to this database and server? What's the SQL query?
What else helpful about the current database can I get using queries? Load,
statistics, what else?
Thanks,
Just D.Check out sp_spaceused in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Just D." <no@.spam.please> wrote in message
news:D_o%d.57888$xt.46304@.fed1read07...
Could anybody remind please, how can I get the current database size having
the full access to this database and server? What's the SQL query?
What else helpful about the current database can I get using queries? Load,
statistics, what else?
Thanks,
Just D.

Monday, March 19, 2012

Database Schemas and "This statement has attempted to access data whose access is restricte

Hello.

In reference to my post yesterday which got removed when i edited this one: After trying a few things i realized that the reason i was getting the error "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function." was because a CLR function was executing a stored procedure, which for whatever reason i completely disregarded the fact that functions cannot call stored procedures. After removing that i am faced with the second issue of my assembly being restricted.
I now get the error "This statement has attempted to access data whose access is restricted by the assembly" I am not certain what is causing this, but i think it has something to do with the database security i am using. I created a schema whose owner is dbo. All my database objects are in this schema. The assembly's owner is also dbo. However accessing my database objects in my custom schema will give me the error talking about access restricted by the assembly. Does anyone have any idea why my assembly cannot access data that is owned by the assembly's owner?

Thanks!It looks like you are missing either
DataAccess=DataAccessKind.Read
or SystemDataAccess=SystemDataAccessKind.Read
in your SQLFunction attribute.

Could you please provide your code.

Thanks,
-Vineet.
|||

I am including the attribute but stll get the error, plus did try the SystemDataAccess attribute. This is the code:

[SqlFunction(DataAccess = DataAccessKind.Read)]

public static SqlString GetDisplayName(SqlGuid id)

{

if (id.IsNull) return SqlString.Null;

string itemTypeFullName = String.Empty;

using (SqlConnection connection = new SqlConnection("context connection=true"))

{

string sql = @."SELECT [itemtype-full-name] FROM [ItemType] WHERE [id] = '"+id.ToString()+"'";

using (SqlCommand command = new SqlCommand(sql, connection))

{

try

{

connection.Open();

itemTypeFullName = (string)command.ExecuteScalar();

|||I got the same error "This statement has attempted to access data whose access is restricted by the assembly".

I specified DataAccessKind.Read and SystemDataAccessKind.Read and that helped

[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read, SystemDataAccess=SystemDataAccessKind.Read)]

Database Schemas and "This statement has attempted to access data whose access is restricte

Hello.

In reference to my post yesterday which got removed when i edited this one: After trying a few things i realized that the reason i was getting the error "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function." was because a CLR function was executing a stored procedure, which for whatever reason i completely disregarded the fact that functions cannot call stored procedures. After removing that i am faced with the second issue of my assembly being restricted.
I now get the error "This statement has attempted to access data whose access is restricted by the assembly" I am not certain what is causing this, but i think it has something to do with the database security i am using. I created a schema whose owner is dbo. All my database objects are in this schema. The assembly's owner is also dbo. However accessing my database objects in my custom schema will give me the error talking about access restricted by the assembly. Does anyone have any idea why my assembly cannot access data that is owned by the assembly's owner?

Thanks!It looks like you are missing either
DataAccess=DataAccessKind.Read
or SystemDataAccess=SystemDataAccessKind.Read
in your SQLFunction attribute.

Could you please provide your code.

Thanks,
-Vineet.
|||

I am including the attribute but stll get the error, plus did try the SystemDataAccess attribute. This is the code:

[SqlFunction(DataAccess = DataAccessKind.Read)]

public static SqlString GetDisplayName(SqlGuid id)

{

if (id.IsNull) return SqlString.Null;

string itemTypeFullName = String.Empty;

using (SqlConnection connection = new SqlConnection("context connection=true"))

{

string sql = @."SELECT [itemtype-full-name] FROM [ItemType] WHERE [id] = '"+id.ToString()+"'";

using (SqlCommand command = new SqlCommand(sql, connection))

{

try

{

connection.Open();

itemTypeFullName = (string)command.ExecuteScalar();

|||I got the same error "This statement has attempted to access data whose access is restricted by the assembly".

I specified DataAccessKind.Read and SystemDataAccessKind.Read and that helped

[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read, SystemDataAccess=SystemDataAccessKind.Read)]

Database Schemas and "This statement has attempted to access data whose access is restr

Hello.

In reference to my post yesterday which got removed when i edited this one: After trying a few things i realized that the reason i was getting the error "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function." was because a CLR function was executing a stored procedure, which for whatever reason i completely disregarded the fact that functions cannot call stored procedures. After removing that i am faced with the second issue of my assembly being restricted.
I now get the error "This statement has attempted to access data whose access is restricted by the assembly" I am not certain what is causing this, but i think it has something to do with the database security i am using. I created a schema whose owner is dbo. All my database objects are in this schema. The assembly's owner is also dbo. However accessing my database objects in my custom schema will give me the error talking about access restricted by the assembly. Does anyone have any idea why my assembly cannot access data that is owned by the assembly's owner?

Thanks!It looks like you are missing either
DataAccess=DataAccessKind.Read
or SystemDataAccess=SystemDataAccessKind.Read
in your SQLFunction attribute.

Could you please provide your code.

Thanks,
-Vineet.
|||

I am including the attribute but stll get the error, plus did try the SystemDataAccess attribute. This is the code:

[SqlFunction(DataAccess = DataAccessKind.Read)]

public static SqlString GetDisplayName(SqlGuid id)

{

if (id.IsNull) return SqlString.Null;

string itemTypeFullName = String.Empty;

using (SqlConnection connection = new SqlConnection("context connection=true"))

{

string sql = @."SELECT [itemtype-full-name] FROM [ItemType] WHERE [id] = '"+id.ToString()+"'";

using (SqlCommand command = new SqlCommand(sql, connection))

{

try

{

connection.Open();

itemTypeFullName = (string)command.ExecuteScalar();

|||I got the same error "This statement has attempted to access data whose access is restricted by the assembly".

I specified DataAccessKind.Read and SystemDataAccessKind.Read and that helped

[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read, SystemDataAccess=SystemDataAccessKind.Read)]

Sunday, February 19, 2012

database question

I have an Access application that links to SQL Server tables via an ODBC link.

I execute the following query in the Access application,

SELECT customerName From customers Where stateCode = 'CA'

My question:

Who (Access, SQL Server or the ODBC provider) selects based on the state code?

Does SQL Server only return customerNames where stateCode = 'CA'
OR
Does SQL Server return ALL rows and let Access select the customerNames where stateCode = 'CA'
OR
Does SQL Server return ALL rows and let the ODBC provider select and return only the appropriate rows to Access?

Thanks for your helpThe cleanest way to determine this is to run the query while SQL Profiler is running, and see what gets passed to SQL Server. My guess is that Access only asks for the rows where stateCode is CA.|||Agree with D.R. However, from what I remember it depends on the ODBC driver. If the remote server (in this case SQL) implements a specific part of the ODBC capabiliies then it will do the query on the server, otherwise the client has to do it. I'm pretty sure SQL will implement that capability.

Friday, February 17, 2012

Database Permissions

Hello All,
I have a need to set up database security on our QA and Production servers
in the following manner:
IT Managers - Read/write access. Ability to view/start/stop scheduled jobs
not owned by them (all jobs are owned by sa).
Team Leads - Allow them to create/drop/alter stored procedures and functions
only. Otherwise, read-only access to all other objects
Developers - Read-only access to all objects.
For the IT Managers, I have a couple of options. 1) Give dbo permissions,
which will give them everything but the ability to view/start/stop jobs. I
won't give them sysadmin rights.
For the Developers, it's pretty easy. db_datareader permissions,
db_denydatawriter permissions.
For the Team Leads, I have not come up with anything bullet-proof. If I
give db_ddladmin rights, it allows them to modify data regardless of any
explicit deny permissions I put on any objects.
Does anyone have any suggestions?
Thanks,
David Grau
Database Administrator
Surprise & DelightDavid
1. Create ITManagers Group and add it to sysadmin server role.
2. Create TeamLead Group
a) Don't make it a member of sysadmin server role
b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
TeamLead
Take a look at creating Roles in the BOL as well
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
> Hello All,
> I have a need to set up database security on our QA and Production servers
> in the following manner:
> IT Managers - Read/write access. Ability to view/start/stop scheduled
> jobs
> not owned by them (all jobs are owned by sa).
> Team Leads - Allow them to create/drop/alter stored procedures and
> functions
> only. Otherwise, read-only access to all other objects
> Developers - Read-only access to all objects.
> For the IT Managers, I have a couple of options. 1) Give dbo permissions,
> which will give them everything but the ability to view/start/stop jobs.
> I
> won't give them sysadmin rights.
> For the Developers, it's pretty easy. db_datareader permissions,
> db_denydatawriter permissions.
> For the Team Leads, I have not come up with anything bullet-proof. If I
> give db_ddladmin rights, it allows them to modify data regardless of any
> explicit deny permissions I put on any objects.
> Does anyone have any suggestions?
> Thanks,
> David Grau
> Database Administrator
> --
> Surprise & Delight|||Thanks for your reply. However, let me add more detail now that I know more
about this request.
The IT Managers want to have SQL Logins that have expanded security beyond
their Windows logins. Is there a way to give them read/write to each
database as well as the ability to start/stop/delete scheduled jobs?
Similarly, Team Leaders want separate SQL Logins that they can use that have
the following: read-only access to the databases; create/drop/alter stored
procedures and functions. No other abilities for the Team Leaders. They
should not be able to create/alter/drop tables or any other objects.
Can all this be accomplished through database roles?
Thanks,
David Grau
--
Surprise & Delight
"Uri Dimant" wrote:

> David
> 1. Create ITManagers Group and add it to sysadmin server role.
> 2. Create TeamLead Group
> a) Don't make it a member of sysadmin server role
> b) GRANT CREATE TABLE ,CREATE Function ,GRANT CREATE Procedure TO
> TeamLead
>
> Take a look at creating Roles in the BOL as well
>
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:D265D1AC-C3D5-408B-8C77-B91696442F9F@.microsoft.com...
>
>|||David
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
Add them to sysadmin server role
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:3079CDD8-49BC-4466-9FC2-2CED09181265@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply. However, let me add more detail now that I know
> more
> about this request.
> The IT Managers want to have SQL Logins that have expanded security beyond
> their Windows logins. Is there a way to give them read/write to each
> database as well as the ability to start/stop/delete scheduled jobs?
> Similarly, Team Leaders want separate SQL Logins that they can use that
> have
> the following: read-only access to the databases; create/drop/alter
> stored
> procedures and functions. No other abilities for the Team Leaders. They
> should not be able to create/alter/drop tables or any other objects.
> Can all this be accomplished through database roles?
> Thanks,
> David Grau
> --
> Surprise & Delight
>
> "Uri Dimant" wrote:
>