Hi!
I have a user on my database that has only "select" access
(db_datareader).
Problem is, I also want him to also be able to create/update extended
properties on tables or views, but without modifying the tables'
schema.
I played around with GRANT but apparently, a member of "db_datareader"
cannot create/modify extended properties on an object if he's not the
owner of this object. I tried making this user a member of
"db_datawriter", but it didn't work.
Nothing short of making him member of "db_ddladmin" worked... but then
this is too much, the user can now alter to delete tables: i DON'T want
that!
Hello, Can I read and write the extended properties with Access?
If it€™s possible is better create a project (ADP) or a classic MDB?
When I try with this query in the query panel (in a MDB)
SELECT objtype, objname, name, value FROM fn_listextendedproperty (NULL, 'schema', 'Person', 'table', default, NULL, NULL)
I have this error message:
The size of a field is too long
If I try with an ADP, when I ask a new query in design view, I have a message for some roblems between the Access version (2003) and SQL Server version (2005) and I can€™t save the query.
In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.
Is there any way to grant this privilage without granting alter on schema also?
Plz help me to know whats wrong with following script:
EXEC sp_addextendedproperty @name = 'col_Programs_ID_Description', @Value = 'The Unique Identifier for a specific Program', @level1Type = 'Programs', @level2Type = 'ID'
I get the following error: Server: Msg 15600, Level 15, State 1, Procedure sp_addextendedproperty, Line 42 An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.
NOTE: If I dont use the last 2 parameters this procedure runs fine, but it adds the Extended Property to the Database Level while I'm trying to add it to the TABLE-->COLUMN level.
Does anyone know of a good primer on extended properties and why I would want to use them. I have you the on-line books sections but that does a clear "why". I have search for white papers and similar documents.
I understand they are good for documentation, but I was looking for something that explain Microsoft long-term vision. How I might use them in template scripts, VSTS, etc.
Under SSMS I can add simple text and carriage returns to an extended property associated with a column. SSMS preserves the <CRLF> formatting that I used. But when I try to read this same text using, for example,
"Object is invalid. Extended properties are not permitted on 'dbHSPS.dbo.Conflict', or the object does not exist."
However I can SELECT * FROM dbHSPS.dbo.Conflict and it returns results, and I know the test_me extended property exists for table "Conflict" in dbHSPS.dbo ...
I am using SqlServer 2005 and I have created small sample table with culumns that have Extended properties. FirstName have [First Name] extended propery called M_Caption. Then I created a view from the table to use in an access database. My problem is, I can't get the table column extended properties to show up in the view column extended properties.
Here is the table
/****** Object: Table [dbo].[Person] Script Date: 04/07/2008 14:42:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Person]( [Id] [int] NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'First Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'FirstName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'Last Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'LastName'
I created this view
/****** Object: View [dbo].[View_1] Script Date: 04/07/2008 14:50:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[View_1] AS SELECT FirstName, LastName FROM dbo.Person GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Person" Begin Extent = Top = 6 Left = 38 Bottom = 99 Right = 189 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,
AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine. What I haven't found anywhere on Help or MSDN online is what these extended properties mean. Could someone enlighten me? 1. What is a description of the above properties? 2. What is the benefit of using them (clearly the function works without using them). Does it help debugging? 3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?
I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
Is there any way to get more information for when IAuthorizationExtension::CheckAccess fails to grant access to a report item for the current user? Specifically, it would be useful to know:
1. URL of attempted report 2. IP address of user agent 3. Identity of current user 4. Date/Time of the failed attempt
I have created a table with .. username, password, grant username and password are nvarchar grant is a bit I want to check the availability of the username and password and then check if it is granted to access or not .. so I made a selection query for getting the grant when username ==x and password = y x=data come from another page y=data come from another page then put the selected grant or deny ( true or false) in a data set .. and then check if it is grant ( true) to complete what I want >>> else ( deny ....( false )) to return a reponse of deny username or password... but everytime the dataset is empty .. this is my code ... sqlConnection1.Open(); SqlDataAdapter da=new SqlDataAdapter("select grant_deny from users where username='"+Request.QueryString["username"]+"' and password='"+Request.QueryString["password"]+"'",sqlConnection1); da.Fill(ds,"users"); sqlConnection1.Close() if (ds.Tables[0].Rows[0]["grant_deny"].ToString()=="true") { . . . . } else { Response.write("error"); } what is wrong ..?? and if there is anyone has another solution for what I want .. I appriciate any sample code,... thanks in advance..
I would like to grant the MACHINENAMEASPNET user all acccess to my MSSQL database (i.e tables and stored procedures. I can do this through enterprise manager but id rather do it programatically so i can add it to my database creation script. What is the syntax for this?
Can anyone help me on users Access rights. The problem is I have created users called finance and sales. Sales have default database called Salesdb. Similarly finance have default database called Financedb.
The user sales created a table called 'daily_report' in salesdb and he is also belongs to the database role of db_ddladmin. The user finance doesn't have any access rights to this salesdb database and he didnot belong to any database roles in financedb itself. But I don't know, this finance user he is able access all the rights of 'daily_rep' table of salesdb database. Even the user sales has revoked all the access rights from the finance user, he still ables to modify/insert/select/delete the records of 'daily_rep' table of Salesdb database.
i am working on a project using asp.net and microsoft sql server, i am now working on my home pc. so now the problem is whenever i try to access the database through asp a error message such as :
[OleDbException (0x80040e4d): Login failed for user 'JYHENGASPNET'.] System.Data.OleDb.OleDbConnection.ProcessResults(I nt32 hr) +20 System.Data.OleDb.OleDbConnection.InitializeProvid er() +57 System.Data.OleDb.OleDbConnection.Open() +203 JYHeng.USRRegistration.Page_Load(Object sender, EventArgs e) in C:Documents and SettingsHeng Jang Yang.JYHENGFYPFYPCacheJYHENGJYHengUSRRegistra tion.aspx.vb:63 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731 will appear. my connectionstring used is : objCn.ConnectionString = "Provider=SQLOLEDB; Data Source=(local);Initial Catalog=fyp;Integrated Security=SSPI;"
i am told that i must check if my aspnet user have been granted access to the database. however i am not sure on how to check the access, not to mention granting accsss. How am i supposed to grant my aspnet user access to my sql database?
Can someone please help me ? i am really lost. thank you
I am setting up report services after I installed the report server add-ins on sharepoint 2007.
at first step, Reporting services integration, I put report server web service URL -- http://ssrsdev1/reportserver authentication mode -- windows authentication it went OK.
at second step, Grant Database Access, I put ssrsdev1 for server name (default instance) and then click OK and then I put the user name (a member of local admin group on ssrsdev1) and password, but I always got errors says: Unable to log on with the given username and password. what wrong here? what should I check? I am sure the user and password is right and I can log on to ssrsdev1 physically with admin right without any problem. thanks for any thought on this. zwp
So here's the situation: I'm creating a front-end application to an SQL Server 2000 database and I need to create a new login to the database for the app.
Is it possible (or even; is it a good idea...) to create a logon that only has access to a single table?
Actually, I know it's possible - but I don't want to have to go through the hundreds of tables and deny access to them all! So I guess the question is;
Is there a quick and easy way of granting a login permissions to a single table only?
I need an example of how to grant access to a SQL user to a DB. For the life of me I can't seem to get my syntax correct. My database name is TEST and my username is LEMME_IN and I want to grant the user "Public" access to the db with db_datareader, db_datawriter database role membership.
I am build an application that able to update insert delete an entries from my sql server, i create a new account in sql server management studio express so my app can connect to the database thru SQL Server Aurthentication. Delete & update method work well but my insert dont.
I change to the connection string to localhost ( WindowsAurthentication) and it works so i conclude that the problem lies on the sql user access. However I had already ticks everything on object explorer > security > logins > my user properties > Server Roles & User Mapping. What should i do inorder to grant insert/create database access to my sql user? Thanks.
How do I grant a user access to a particular project? I have set up some reports and want other users to look at them as a template to build their reports and keep getting projectname.rptproj.user is denied.
How can I remove access to extended (xp_) stored procedures? Is there any revoke on <stored_procedure_name> ... command? How can I generate a script of all users who have execute privileges for these procedures? Also, is there any way of restricting (instead of removing) access to those procedures?
Any help will be greatly appreciated!!! Thanks, Alla
I have around 600 databases in my server, a user need select access of all the databases. will i have to go one by one in all the dbs and create that user and give datareader role to him. or is thr any shorter way to do so????
I have a large table with email column. I need to grant select access to this email column to many users but the actual email should not be visible to those users. I thought of following options:
1.Create an indexed view with that column encrypted and then grant access to users.While searching by email, their search text will also be encrypted and then matched with view column.Problem with this is that I have to create indexed view because most searches will be on Email column and table size is pretty big,20 m records. I don't prefer indexed view in general.
2. Add another column to table with encrypted email and grant access to that column to users. Problem with this is that it will increase table size and i'll need one more index.
i want to create new sql user and grant him two tables access. we have several databases created on same server so we want to allow only two table in ABC database. user should not be able to see other databases and their tables. And user also should not be able to access any other tables in ABC database except two tables.
is there any query to deny all tables in schema for all clauses (Select, Update, Insert) then grant two tables to user with select clause?
I am trying to grant access to groups on my reports... The thing is that, whenever I try a distribution list I would get...user or group name is not recognized... (rsUnknownUserName)
1-how can I recognize if it is a group or a distribution list in advance... 2- why can't I grant access to distribution lists?
I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2] SELECT 'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]' FROM sys.views;
How do I grant a user permissions to only one table in a database.  How would it affect him using our Main App which is NAV with regards to his user's permission in NAV
GRANT SELECT ON [dbo].[TblAreaCatmap] TO [admin] prevent grant from being automaticly add to each column?
Is there a way when you issue a grant select to a table or a view to not also grant select for each column.
The problem is when you use the grant command it automaticly adds the grant command to each column. I want to grant the permission at the table level so when the table is scripted it only has a single grant command instead of a grant for the table and a grant for each column which is not needed.
The sql managemnt studion interface will allow you to do this but onlt by using the interface. If you issue the above command from a query window it also creates A GRANT FOR EVERY COLUMN. How can I stop this behavior.
I am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. This would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?
sproc: CREATE PROCEDURE [schemaname].[SetUserCulture] @UserID int , @Culture nvarchar(10) AS UPDATE dbo.SecUser SET Culture = @Culture WHERE UserID = @UserID
